Important help with a field

[noahtomlin]

I am an archivist with a synagogue who is creating a database of historical documents.



One thing my client wants is a list of what congregants are listed in a particular document, and the ability to narrow a search down to documents that list X congregant.

I was able to create this using a standard field (having to make compromise to use a radio box system), but the lookup only shows surnames, not first names.




Attempts to modify this in Design View have not succeeded. Properties of the lookup currently look like this:




I am slowly realizing that I'm in way over my head, but the database is too far into development to change course. The main priority here is to change the lookup in the field so both surname and first name (stored as LastName and FirstName in the table) are visible.

Thank you for helping a panicking but determined newbie through this!

[June7]

This is not a 'standard' field - it is a multi-value field. I NEVER use multi-value fields. But if you must, should not store name but person ID. Your table does not have a unique identifier primary key defined. Add an autonumber field called PersonID. Then combobox properties:

RowSource: SELECT PersonID, LastName & "," & FirstName AS Fullname FROM people_1 ORDER BY LastName, FirstName;
ColumnCount: 2
ColumnWidths: 0";1"
BoundColumn: 1
ControlSource: a long integer number field to save PersonID as foreign key

[noahtomlin]

It works now for adding stuff with the radio boxes. However, the filter field is now completely empty. Is that a side effect of adding a new value to the multi-value field, making the old entries obsolete?

Additionally, attempting to make a custom lookup for LastName, FirstName gives me this error:

[June7]

Yes, if you are switching to autonumber primary key as I suggested.

You would have to do what it says - delete relationship - delete the field and create a new field of long integer type so it can receive autonumber value as foreign key. But you will lose data so if you have a well-used db loaded with data, probably don't want to do this.

I NEVER use multi-value field and I NEVER build Lookup field in table - especially with Lookup wizard. Once these things are created they are difficult to get rid of.

What radio boxes?

[davegri]

You have a many-to-many relationship between documents and congregants which you've approached with a multivalue field.
Here's a way to convert that to a conventional M-T-M setup. Try it on a copy of your DB.
https://www.accessforums.net/showthread.php?t=77252

[orange]

Noah,

Getting your tables and relationships designed and tested to ensure they meet your requirements is critical to a well-functioning database. Too often people (newbies included) jump to physical Access database expecting the software to do something magical.
Note to Noah and others --it doesn't work like that. Analyze your requirement, make a model with pencil and paper, test your approach with some sample data and scenarios.
See the Database Planning and Design link in my signature for articles that may be helpful to you and your project. You may find the short BA-Experts links helpful with analysis (and humorous).

I agree with June - advise against multivalued fields and lookups at table field level. Opt for proven lookup tables approach.
Good luck.