Multi-value lookup field

[Lesley]

Hi there, I wonder if anyone can advise me.



I'm having a big struggle with adding a multi-value lookup field to a table. I need to provide a long pick list of items (from a source table) but these items must not be output (in forms or reports) in alphabetical order, they need to remain in the order that they are in the source table.

Access wants to alphabeticalise the output even if a I add a number field to the source table and sort by that.

Is there any way I can persuade access to just give the selected items back in the same order as in the source table ?

Many thanks if you can help

[ItsMe]

Store the Alpha in one field and the numbers in another field. Concatenate the two together for forms and reports.

[June7]

Are you using report Grouping & Sorting features?

[Lesley]

Thanks for that idea but I only added the number field to try to force access to stop alphabetising the multi-lookup field. I don't want the number field in any output.

I am when I prepare reports but this is a multi lookup field in a table. I want the items chosen for that field not to be alphabetised by access but given in the order in the source table.

[June7]

The number field value does not have to be displayed in report, just use it to control sort order. I am not sure concatenating is needed but even that would not have to be displayed.

[ItsMe]

I am totally confused. The order displayed for lookup fields should be the order that the RowSource is typed. If it is not based on a value list and it is based on a table, create a query and add a sort order in the query. If it is based on a query object, change the sort order there.

[Lesley]

The number field value does not have to be displayed in report, just use it to control sort order. I am not sure concatenating is needed but even that would not have to be displayed.

Yes, that's what I hoped the number field in the source table would do but the multi-value field (in the target table) takes no notice of the sorting by the number field, even though I asked it to in the multi-field wizard, and alphabetises anyway.

[June7]

Oh rats, if you are using a multi-value field, that does complicate. I NEVER use multi-value fields.

[Lesley]

D'oh.

I can do it without using mult-choice by using a series dropdown combo lists with a single selection option each. The user either selects them or not to leave blank but as there are about 40 items it would be cleaner to have one multi choice box rther than 40 single choice boxes.

I am totally confused. The order displayed for lookup fields should be the order that the RowSource is typed. If it is not based on a value list and it is based on a table, create a query and add a sort order in the query. If it is based on a query object, change the sort order there.

Yes, I thought that too but discovered that Access choose to sort the selected values from the Row source table by alphabetical order. I have no idea how to prevent it.

[June7]

Would still be a combobox with multiple items listed as choices. The difference is that only one value can be selected and saved. Each selection would be in a new record. Will probably need a related table.

Re: multi-value field, review http://office.microsoft.com/en-us/ac...001233722.aspx

If you want to provide db for analysis, follow instructions at bottom of my post.

[ItsMe]

.... Access choose to sort the selected values from the Row source table by alphabetical order. I have no idea how to prevent it.

Interesting. I could not find any properties to offset the effect either. If the sort order in the rowsource does not do it then I suspect it will not be possible.

[Lesley]

I've attached the relevant part of the database.

Theparasites found field in the DMP Tabe is the multi-value field. It was created using the Lookup Wizard in the table design view.

Values are taken from the Parasites found table and sorted in Ascending order in the wizard using the ID field which is a number field. The Allow Multiple field Values is checked in the wizard.

When a number of items are selected in the resulting field in the DMP table they are stored alphabetically rather than in the required order as given by the ascending sorted number field.

I hope this is enough information and many thanks fir your kind help.

Lesley.


Multi Field test.zip

[June7]

Shouldn't there be a patient ID field? Names are poor unique ID.

That is the nature of multi-value field. Did you read the referenced link? Do you understand what a multi-value field actually is?

Order data is saved, multi-value or otherwise, should be irrelevant. Control order with queries and/or form/report properties.

If you want to sort the items by the number, expand the multi-value field in query and join with the source table to retrieve the number field. Or save the number and expand and join to retrieve the text.

SELECT [DMP Table].[Patient Surname], [DMP Table].[Patients Forname], [DMP Table].[Parasites found].Value, [DMP Parasites found].ID
FROM [DMP Parasites found] RIGHT JOIN [DMP Table] ON [DMP Parasites found].[Parasite found] = [DMP Table].[Parasites found].Value
ORDER BY [DMP Table].[Patient Surname], [DMP Table].[Patients Forname], [DMP Parasites found].ID;

[Lesley]

This is only part of a much larger database containing a number of other tables.

In the DMP Table the DMP Specimen Number is the unique primary key.

Shouldn't there be a patient ID field? Names are poor unique ID.

SELECT [DMP Table].[Patient Surname], [DMP Table].[Patients Forname], [DMP Table].[Parasites found].Value, [DMP Parasites found].ID
FROM [DMP Parasites found] RIGHT JOIN [DMP Table] ON [DMP Parasites found].[Parasite found] = [DMP Table].[Parasites found].Value
ORDER BY [DMP Table].[Patient Surname], [DMP Table].[Patients Forname], [DMP Parasites found].ID;

I don't think I need to involve the patient name fields in a multi-value field which just needs to contain the parasite names found in a specimen. I only want it to select parasite names from the information in the DMP Parasites found table. It does this well but alphabetises it instead of leaving it in the order in the source table.

Unless I've miss-understood your meaning.

The alphabetising is taking place within the multi-value field. and is not what I need. More than one organism may be found in one specimen, some organisms are of greater clinical importance and need to be listed first on a report.

[June7]

My earlier comments still apply.

Then use the Specimen Number instead of patient names.

Is there a Patients table? Could there be multiple specimens for each patient? Should save PatientID in DMP Table?