Combobox, match one database with another

[DarkSmile]

Hi,

I have a form based on a database 'Klanten - Food' => this database stores all information about our clients (Name, ID, Standard, City,...),
I have another database 'Auditoren' => This stores the name of the auditoren and for wich standard they are qualified.

I have a from based on the client database:

I would like now to be able (I tried with combo35 but no success) to say => Match standard (from client) with the standard in auditoren, and if it has an X the auditor is qualified and needs to come in a dropdown list.
Result would be => Test klant has standard IFS Broker

In my Database auditoren => Test is qualified for ACS-003 Déb , IFS Broker , IFS Global Markets & IFS L
Test should be the only one showing up in the dropdown

How can I achieve this?

 

[Micron]

You will forever have difficulty in getting at data from tables that are designed like spreadsheets. This is a very common mistake that Excel users make. Your qualifications should be in rows/records in a related table, not spread across fields. IMO, you will now have to make a UNION query to get the values in rows, then use that query in another query. The 2nd query would use the criteria you're looking for. If it's ACS-003 Deb then that would be the criteria to use. If you want all qualifications then use no criteria. If your tables were designed properly you would almost never need a UNION query. Perhaps you will have to link to the other db table since it is in a different database.

Also, you should not have special characters (save for underscore _ ) or spaces in any object name (fields are objects). I suggest you research db normalization with the thought of rebuilding your db or you will always have trouble.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

 

[DarkSmile]

You will forever have difficulty in getting at data from tables that are designed like spreadsheets. This is a very common mistake that Excel users make. Your qualifications should be in rows/records in a related table, not spread across fields. IMO, you will now have to make a UNION query to get the values in rows, then use that query in another query. The 2nd query would use the criteria you're looking for. If it's ACS-003 Deb then that would be the criteria to use. If you want all qualifications then use no criteria. If your tables were designed properly you would almost never need a UNION query. Perhaps you will have to link to the other db table since it is in a different database.

Also, you should not have special characters (save for underscore _ ) or spaces in any object name (fields are objects). I suggest you research db normalization with the thought of rebuilding your db or you will always have trouble.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

Thank you, if changed the way my data is stored, so now i have 3 databases.

1. Auditor + ID
2. Normen + ID
3. Qualified => this one is linked with the 2 others

Result: I can see per auditor & norm who is qualified for what

Now I need to match the normen from my test client with the database qualified , I assume I need to change something on how I store this data too?

Are you able to help a bit more?

 

[Micron]

Actually you have 3 tables in one database, not 3 databases. From what I can interpret, you now have 2 main tables and a junction table (Qualified) for a many to many relationship (many auditors can have many qualifications) so that part looks good. Probably the best way to proceed with your question is to have a main form (for auditors) with a subform for normen (quality system standards?). You could research how to design for subforms so that you get the linking right.

If you're in the mood to improve the design as much as possible, you should review the following and fix a few things. When you read the links note that ID is a poor name for a table field. If you improve the design, consider reposting the relationships. One other thing not noted in any of the links is that I'd advise basing forms on queries and not tables if you think that eventually there will be a lot of records. Access will load a large batch of records (not the entire table of records when there are very many, but certainly all fields) which may eventually slow things down.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields