posted
So... I know I haven't posted here since sometime in the 1920s, but when in distress - Hatrack shall find an answer. Please pardon my lechery!
I've been working hard to try and construct a custom database for my private home-library, and granted my limited computing skill, I'm doing it in Access 2010, not discounting the option of trying LibreOffice Base 3, though.
So here's the thing: In my table of Authors I wish to have both a "first name" and a "last name" field, which then in another table (which will serve me for a subform in "Books") I want it to appear logically, as "[last name], [first name]". Is there any way to construct a field in one table which incorporates two other fields from another table in a given syntax (in a many-to-one relationship, as I see it)?
I will not actually sit down and study SQL or VBA for this purpose, as it's too much of a hassle for me at the moment. If it can be done by simple configuration of the relationships or a macro, then I'd be more than fine with that.
Your help, assistance, or suggestions will be greatly appreciated.
And Happy Passover to all from the Land of Bilk and Money!
1) In your Authors table, make sure you have an ID or Identity field that is simply an incrementing integer. This is easily done automatically in Access.
2) If you want to permit one book to have multiple authors, there are two ways to accomplish this. By far the easiest -- but less flexible -- is to add one field to the Books table for each author a book might have: PrimaryAuthor, Author2, Author3, and so on. Personally, I'd just do PrimaryAuthor and CoAuthor. In this field you will place the identifying unique ID from the Author table that matches the author you want to associate with the Book in question.
3) You never, never, NEVER create a field that concatenates the first name and last name of the author -- at least not in a table. You will create queries and form views that do this. In the case you're describing -- in which you are selecting an author from a dropdown in a form that you're using to populate Books -- you will use a List or Dropdown object for the Author and CoAuthor fields on the form. This list will use as its datasource the Author table, and will retain as its VALUES the ID number of the authors; the LABEL of the list items will be displayed as [LastName] & ", " & [FirstName], which is how you'd concatenate those values in Access.
Posts: 37449 | Registered: May 1999
| IP: Logged |
quote:Originally posted by TomDavidson: ...you will use a List or Dropdown object for the Author and CoAuthor fields on the form. This list will use as its datasource the Author table, and will retain as its VALUES the ID number of the authors; the LABEL of the list items will be displayed as [LastName] & ", " & [FirstName], which is how you'd concatenate those values in Access.
I don't recall there being a separate Values and Label property for List or Combo Boxes in Access.
I think that if you were to use a Combo Box, you would need to have the Control Source set to whatever field you're using to store the Authors ID value. And then the Row Source would be something like (Select [ID], [LastName] & ", " & [FirstName] from tblAuthors). The Bound Column would be set to 1 (indicating the ID value as the field to store). Column Count would be 2. And then Column Widths would be something like 0"; 2" (in order to hide the ID field and display the name field).
Posts: 161 | Registered: Aug 2010
| IP: Logged |
Printer-friendly view of this topic
![[Smile]](smile.gif)
