Combobox display order

[Duncan]

I have a table with a combobox in it. The records displayed in this combobox are in the same correct order as displayed in the source table. The problem is that when I select multiple values in the combobox they appear by default in alphabetical order in the table.
How do I get the selected data in the combobox to appear in the same order on the table as selected and not in alphabetical order? I think it may have something to do with the criteria section of the query that generates the combobox from the source table!

[jzwp11]

First, you should not have a combo box at the table level (even though Access has that capability). It will cause you problem, you might want to take a look at this site for more details.

[Duncan]

Thanks for the help. I'm new to access and have read what you suggested. So should I have the comboboxs in the forms with the original table as the recordsource with the elected data duming into another table. I have done this and it works, however this way seems not to allow multiple select comboboxs, Just a single select. I want the user to be able to make multiple selections reorded in the one cell

[jzwp11]

I see you are using Access 2007, is the field in the table a multivalue field? If so, I have tried to avoid using those, so I'm not sure how they would work.

I generally use a multi-select list box when I want users to have the option of selecting more than one option.

In order to help you out better, we need to understand what your table structure looks like. Could you provide that?

[Duncan]

G-Day, Yes I‘m using 2007. At the moment the fields in the tables are multivalue Comboboxs with the Row Source from the original tables I imported from Excel which works well until you come to Querying and working with Relationships (I just generated Forms from these tables). I stated this way when starting off with Access and it seemed to be the easiest way as to give the end user multi-select and got rid of Null values when queried. However others have mentioned Normalization in relational databases and as you have mentioned it appears not the best thing at a table level as I’m finding out the hard way. I have attached a Zip file of the 1, the root Excel table 2, my manipulation table of the root table 3, the associated Form. I left out the client tables for privacy. Many thanks for the help

[jzwp11]

I would definitely get rid of the table level lookup fields; see this site for a more detailed explanation. I would break out the lists that are currently in the lookups to their own separate tables. I am thinking that the multiselect items should actually be related records in a table since I am guessing that they form the many side of a one-to-many relationship. You will have to give us some background as to what your application is meant to do and how the data is related, before we can determine if your table structure is normalized.

[Duncan]

Thanks. There appears to be many pit falls with Combos. The data is all related through the Clients MRN (Medical Record Number). There is a list of 24 identified ‘Clinical Domains of Nursing Practice’ (Self Care, Mobility, Transfers etc – see attached). Each client can be related to any number of the Clinical Domains. Within each Clinical Domain is Issue, Cause, Clinical Indicators, Actions Considerations etc (Field Headings) . So if a client requires a wheelchair to mobilise then the Mobility Domain would be selected and the associated Issue, Cause, Clinical Indicators, Actions Considerations etc selected therein. I’m thinking the roar data is present the wrong way in Excel. Any working examples of one Clinical Domain (Self Care) would be great then I can duplicate it from there - hopefully!
Thanks

[jzwp11]

There maybe some data models on-line for what you want to do but I have am not aware of any myself.

Each client can be related to any number of the Clinical Domains. Within each Clinical Domain is Issue, Cause, Clinical Indicators, Actions Considerations etc (Field Headings)

This describes a one to many relationship because a client can have many clinical domains. Furthermore, a clinical domain can apply to many clients which is another one-to-many relationship. When you have two one-to-many relationships between the same two tables you need a junction table to represent that many-to-many relationship

tblClients
-pkClientID primary key, autonumber
-txtFName
-txtLName


tblClinicalDomains
-pkClinicalDomainID primary key, autonumber
-txtClinicalDomainName

tblClientClinicalDomain
-pkClientClinicalDomainID primary key, autonumber
-fkClientID foreign key to tblClients
-fkClinicalDomainID foreign key to tblClinincalDomains

You would then have a table that holds these attributes: Issue, Cause, Clinical Indicators, Actions Considerations

tblAttributes (you probably have a better name)
-pkAttributeID primary key, autonumber
-txtAttributeName

Since you have many of these attributes per client/clinical domain combination and an attribute can apply to many client/clinical domain combinations you again have a many-to-many relationship.

tblClientClinicalAttribute
-pkClientClinicalAttributeID primary key, autonumber
-fkClientClinicalDomainID foreign key to tblClientClinicalDomains
-fkAttributeID foreign key to tblAttributes

[Duncan]

Thanks you so much for your time and help. I will give it a go later today.

[Duncan]

All works well and created a form for same etc. I'm still with the problem of being unable to give the end user the ability to select multiple values (say for Appendix Field) within a Form without having multiple value comboboxs in the receiving table (i,e tblAttributes). Is there a function in the Combo Box Controls on the Form ribbon or Property Sheet to give the user the ability to select more than one value? Have I missed something while creating the Combo Boxs on the Form?

[jzwp11]

If a user can have many values for the appendix field that implies that you have a one-to-many relationship which would require the information to be stored as records in a related table. Now in terms of forms, you can use a multi-select list box where the user can select the items they want and then you would use Visual Basic for Application (VBA) code to append them to the table. But your tables have to be set up properly first.

[Duncan]

Now I’m getting into an area of database design that is completely foreign to me that is VBA coding. I appreciate the help and understand if your help ends here re need coding. I’ve being struggling/pondering with this one aspect since I started developing the database many months ago knowing at some point I would need to tackle it. I’ve created everything else but this.

I’m in the process of searching the forum for code but it’s hard to relate same to my needs when unfamiliar with code language. Attached is a abridged version of the database with the tables and the relationships you suggested. Table Excel CD headings is the source table of the Clinical Domain headings and table Excel Self Care is the source table for the attributes associated with the clinical domain heading Self Care.

The example ‘Test Form’ has one list box;
Form Record source = Attributes (as this is where the data is going)
Listbx Control Source = Appendix (as this is the field the data is going to)
Listbx Row Source = Excel Self Care - Appendix (as this is where the data is coming from)
This works great for single select data - many thanks.
If possible where to from here just for the Appendix attribute as a working example of multiple select?

[jzwp11]

The headers in the one table should actually be tables and the list of items under each header should be records in the tables. Please see the attached.

Now you have to figure out the relationships between the tables.

For a clinical domain can their be many issues? Can an issue apply to many clinical domains?

[Duncan]

Thanks for the example, about to work out the relationships.

Yes - a Cinical Domain can have many issues, causes etc but only from the selection within that specific Clinical Domain (Self Care, Mobility etc). and

No - one issue can't relate to many Clincial Domains. The 24 Clinical Domains each have a unique set of Issues, Causes, Clinical Indicators, Actions/Considerations and Appendix etc. This would make 144 tables if all separated.

[jzwp11]

This would make 144 tables if all separated.

No, all issues/causes would go in 1 table, you can add 1 field to identify to which clinical domain they belong.

Similarly, all clinical indicators, actions/considerations and appendicies each go in their separate tables as I illustrated in the database I attached previously.

Then with respect to the clinical indicators, does each issue/cause have specific clinical indicators that it relates to? In other words does an issue/cause have many clinical indicators? Can a clinical indicator relate to more than one issue/cause?