Access Query

[Donna]

I have four tables in a database. Wages, Insurance, 401(k) Contributions and
HSA Contributions. The EE ID is common to all tables but not every table has
every EE ID. I need to make a query showing all EE ID numbers even if they
don't have an entry in one of the tables. Can you help? Thanks.

 

[Jeff Boyce]

Donna

Open a new query in design view.

Add the four tables.

Drag the EEID from the table that DOES have all EEIDs to the corresponding
field in table2 ... do it again for table3 ... and again for table4.

Now right-click on the join line and change the type of join so that you get
ALL of your table1 records and ANY of your table2 with corresponding values.

And again, from t1 to t3...

.... and from t1 to t4

Add the fields from the respective tables for what you want to see in the
query's output.

Run it.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Armen Stein]

Drag the EEID from the table that DOES have all EEIDs to the corresponding
field in table2 ... do it again for table3 ... and again for table4.

Jeff is right on target if one of your tables has ALL EEIDs. Do you
have some kind of employee master table? That would be the one.

But if for some reason you don't (which would be sketchy database
design), you'll need to do something else. To get a list of all
unique EEIDs used in *any* of several tables, you can use a UNION
query. It's essentially a way to join tables *vertically* instead of
*horizontally*. You can read up about it online or in Access help.

Once you have a Union query with all those EEIDs, you can use *that*
query as the main table in the query joins that Jeff was referring to.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com