Union Query

[qwerty99]

I'm not sure if this is the right place for this, so if it's not I apologize in advance.

I'm a beginner in Access and building a database for work, and I came across union queries through my extensive Google searches, and it is something that would work perfectly in my DB...except for one key part.

Is there anyway to create a Union Query that is linked to a combobox, and will filter the data depending on what is chosen by the combobox?

So if the business name ABC Inc is chosen in the combobox, it will show all the query information from all queries relative to ABC Inc, and if I choose a different name it will only show the info for that name.

I don't even know if this can be done as I haven't found it online anywhere, but any help that could be offered would be great!

 

[pbaldy]

Sure, the trick is each SELECT clause needs it's own criteria:

SELECT...
FROM...
WHERE...
UNION
SELECT...
FROM...
WHERE...

Get the syntax by using the query builder on a single table and add the criteria, then you'll see how the SQL needs to look.

 

[qwerty99]

Thanks for the quick reply. Could you elaborate how to get the combobox to work with the SQL? Would the 'WHERE' clause need to be set up/linked to the business name in some way, since it will change based on what the user selects? I understand the SELECT, FROM, AND UNION clauses (the easy part, I know) but am unsure how to use WHERE in this instance.

This is the first time I have ever worked with SQL, and most of my programming background is just a little bit of Excel VBA, so your patience is appreciated.

Thank you!

 

[pbaldy]

Did you miss this?

Get the syntax by using the query builder on a single table and add the criteria, then you'll see how the SQL needs to look.

You can use the Build feature in the criteria to get the correct syntax to point to the combo, or use the full version from here:

http://www.mvps.org/access/forms/frm0031.htm

 

[Pat Hartman]

If you are having to search multiple tables at once, you almost certainly have a design flaw. Do you want help trying to resolve the underlying issue?

 

[qwerty99]

pbaldy: Thanks, I got it to work!

Pat Hartman: If you can think of a better way to do what I'm trying to do that would be fantastic, though what I have now does work for what I need. The reason I have to search multiple tables at once is because each business name can have any number of possible coverages, and each one of those can again be more than one per business name. An example:

I have the business ABC Inc which has the coverages NTL, BFL, Glass Breakage, and Topper all applied to it. There are 3 NTL coverages for it, 1 BFL, 1 Glass Breakage, and 2 Topper coverages.

The business 123 LLC has one each of the coverages NTL, Topper, and PD.

Since there can be multiple coverages per name (such as 3 NTL or 1 or none), I made each its own table and linked it to the business name. From what I was learning from my google searches I felt this was the way to accomplish a DB that would appropriately show and link everything I needed to do.

I need a way to show on a form all the coverages and coverage type each business name has, and the Union query does just that.

Do you have any suggestions on some better way to do this, or have I just managed to make things more confusing?

Thanks again!

 

[Pat Hartman]

You should only have to search the table that contains the customer name. The related tables should be linked by CustomerID. Once you find a customer, you can open a form to show the customer and a subform that shows the coverages.