ComboBox Question

[Gilrucht]

I have a client db for my law office. After client info is entered the db branches off into one of 4 tables depending on whether the legal matter is a state civil, statecriminal, federalcriminal or federal civil matter.the db the continues from of these 4 main tables. I have a client contact popup form that records phonecalls from a call on their case. Im tryng to create a combobox that will lookup the correct client when a call is recieved but can't figure out how to have one combobox that looks up data from 4 different tables. I can't base the combo on the clientinfo table because a client can have more than one legal matter so the combo has to be based on the 4 case tables in order to link the call record to the corecr case record. I'm thinking I need sime sort of if statement Maybe a listbox with the 4 cases and and if statement that calls a corresponding criteria criteria in the query for the combo box. Can something like this be done? Is there a way to call seperate records from more than 1 table in the same combo box?

 

[Smart]

Re : ComboBox Question

You could write select query
selecting the required fields from the four tables
base your combo box on that query

 

[viperstingray]

try to also look for SQL statements that would accomplish that. Search for cascading combo boxes and you will see what i mean. sorry i could not be more help

 

[Gilrucht]

I appreciate both suggestiond but don't think either will work. I use a lot of cascading combo boxes in other situations in this db. The problem here is that all 4 tables are on the same level so there really are no parent-child relationships you can set up for the cascading boxes.

The problem with the the sql statement is that it is more complicated that that. Its not the typical stiuation where you are pulling say customer name fro from 1 table, product name from another , etc. In my situation I am pulling the same type of data, the case identification id from eacn of 4 different tables. This should actually all be in one table but I had to break down into 4 tables because when I had I general case tabel I exceeded access's limitation on relationships to one table. That has caused this problem as well as others.

There really is no reason not to go directly from cliientinfo to specific casetables such as autoaccidents, divorce, criminalfraud, etc except for the fact the number of case tables exceeds the number of relationships Access will allow me to create to one table. Thus I created these 4 "artificial"categories(statecivil,statecriminal,federalcivil, and federalcriminal) to break down the relationships and solve this problem. Unfortunately, It has created other problems, such as this one. I have identical data in 4 different tabels at the same level that I need to pull into one combobox

 

[RuralGuy]

It seems to me that a Union Query would work in this case.

 

[Gilrucht]

Rural Guy,

You were right. That did the trick. Thanks.

 

[RuralGuy]

You're very welcome and thanks for posting back with your success.