Multi-value lookup field

[Lesley]

Even though what you see and work with appears to be a single field, the values are actually stored independently and managed in hidden, system tables. The Access database engine handles this for you, automatically separating the data and bringing it back together again to surface the values in one field.

Is there a way to edit this hidden table to prevent alphabeticalisation within the multi-value field?

This is the row source control in the multi-value field in the DMP Table:

SELECT [DMP Parasites found].[Parasite found], [DMP Parasites found].[ID] FROM [DMP Parasites found] ORDER BY [ID];

That would appear to sort the result by the ID (number) from the Parasites found table in the parasites found field of the DMP Table, but it doesn't.

[June7]

That SQL in the RowSource only sorts the combobox list, not the data saved in field.

Option offered in earlier post: Or save the number

1. Delete the link between tables in Relationship builder

2. Change field in DMP Table to a number type

3. Set ID field in DMP Parasites found as primary key then recreate link in Relationships

4. Change the Lookup properties
RowSource: SELECT ID, [Parasite found] FROM [DMP Parasites found] ORDER BY [ID];
ColumnWidths: 0";2.0"

[Lesley]

No there is no patients table. We don't have patients we have specimens the DMP Table is the specimen table and contains most of the information regarding the specimens. There is not always a patient name particularly if it's an animal specimen rather than human.

Yes, there could be more than one specimen from a single patient and over a large period of time. That is for the submitting hospital to deal with. Each specimen is a different job to us. and treated as a different database entry. They do not need to relate back to the patient to us, but to a lab number.

I'll have a try of that tomorrow, I've been at this for hours and am flaked.

Many thanks for your patient help, a fresh morning may help me take a fresh approach.

[June7]

Then I wonder why you bother with name fields.

[Lesley]

To comply with UK accreditation each specimen must have 3 identifiers - usually First name, Last name and Date of Birth. We are always sent those together with the hospital's unique number and submitting lab's specimen number. These are used when reporting back a specimen result to the submitting hospital. For work within our lab and for specimen tracking our own unique DMP number is used.

It sounds long winded but no patient want to get someone else's result. Someone else's goods can be exchanged by a shop but medical issues are rather more critical.