Pulling from several tables

[Guest]

I currently have a table called tblCustomers that lists customerID,
customerName, Address, etc. then 4 more tables that list different
exceptions and details of the exception for each customer. My tables are:
tblGeneral: ID, customerID, Description, AffectedDays, etc
tblEligibility: ID, customerID, Description, addedEligibilty, etc
tblProcessing: ID, customerID, whereInProcess, EstimatedCompletion, etc
tblQueue: ID, customerID, whereRouted, Description, etc

I would like to have a query that will show the exceptions that a school
has. They may not be listed in all four tables. How can i set up the query
to show what I want.

 

[Jeff Boyce]

Create a new query. Add the Customer table (?your customers are schools?).

Add each of your 4 "exception" tables.

Drag a join from tblCustomer!CustomerID to tblGeneral!CustomerID. Modify
the properties of this connection (e.g., right-click to highlight the
joining line and select Properties), to include ALL records from tblCustomer
and ANY that match from tblGeneral.

Repeat on the other three.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you that is exactly what I wanted!

Create a new query. Add the Customer table (?your customers are schools?).

Add each of your 4 "exception" tables.

Drag a join from tblCustomer!CustomerID to tblGeneral!CustomerID. Modify
the properties of this connection (e.g., right-click to highlight the
joining line and select Properties), to include ALL records from tblCustomer
and ANY that match from tblGeneral.

Repeat on the other three.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I currently have a table called tblCustomers that lists customerID,
customerName, Address, etc. then 4 more tables that list different
exceptions and details of the exception for each customer. My tables are:
tblGeneral: ID, customerID, Description, AffectedDays, etc
tblEligibility: ID, customerID, Description, addedEligibilty, etc
tblProcessing: ID, customerID, whereInProcess, EstimatedCompletion, etc
tblQueue: ID, customerID, whereRouted, Description, etc

I would like to have a query that will show the exceptions that a school
has. They may not be listed in all four tables. How can i set up the query
to show what I want.

I'm not absolutely certain, since I am not sure which tables might
contain what; but here's two things to try.

1. Create a Query joining tblCustomers to the four other tables, using
a Left Outer Join for each join - select the join type line and choose
the option that says "show all records in tblCustomers and matching
records in <the other table>".

This might not work, because there is (presumably) no relationship
between, for example, tblEligibility and tblQueue.

2. Probably better: don't try to do this in a Query at all. Instead
create a Form based on tblCustomers with four subforms based on the
other tables. Use CustomerID as the master/child linking field; this
will show you the exceptions for each customer, if any, and allow you
to edit them as well (which the query probably won't).

John W. Vinson[MVP]