Missing records when they're there

M

Me

Hi All,

I have a database to keep track of clients and their purchases.

Tables:
tblClient with ClientID as the PK = client info like name, street address,
so forth.

tblClientNumbers with ClientNoID as PK, ClientID as FK linking to tblClients
= records for different client numbers we keep track of such as Social Sec
#, Employer Identification Number (if different than ssn), Industry
Classification, etc. Each separate number, like ssn is a separate record
here, EIN is a separate record, etc. I set it up with this table rather
than adding ssn as a control on the tblClient table, ein as another control,
etc because it seemed appropriate, but possibly I've made a mistake for
reporting purposes.

tblOrders with OrderID as PK, ClientID as FK with dates and product codes
(another table called tblProducts) of orders, etc.

It all seems to work okay except when doing reports and I need the client
numbers to be included. I want to list a client's purchases along with the
appropriate client numbers. Yet, if I do a query as a basis for such a
report requesting the client name, etc from the tblClient table and the ssn
AND ein from the tblClientNumbers table, and the client only has a ssn and
not an ein, then it doesn't include this client in that report.

I hope this is clear. Did I design wrong? Are the relationships wrong (one
to many from tblClient to tblClientNumbers). What aren't I getting?

TIA
me
 
B

Bob Quintal

Hi All,

I have a database to keep track of clients and their purchases.

Tables:
tblClient with ClientID as the PK = client info like name, street
address, so forth.

tblClientNumbers with ClientNoID as PK, ClientID as FK linking to
tblClients = records for different client numbers we keep track of
such as Social Sec #, Employer Identification Number (if different
than ssn), Industry Classification, etc. Each separate number,
like ssn is a separate record here, EIN is a separate record, etc.
I set it up with this table rather than adding ssn as a control
on the tblClient table, ein as another control, etc because it
seemed appropriate, but possibly I've made a mistake for reporting
purposes.

tblOrders with OrderID as PK, ClientID as FK with dates and
product codes (another table called tblProducts) of orders, etc.

It all seems to work okay except when doing reports and I need the
client numbers to be included. I want to list a client's
purchases along with the appropriate client numbers. Yet, if I do
a query as a basis for such a report requesting the client name,
etc from the tblClient table and the ssn AND ein from the
tblClientNumbers table, and the client only has a ssn and not an
ein, then it doesn't include this client in that report.

I hope this is clear. Did I design wrong? Are the relationships
wrong (one to many from tblClient to tblClientNumbers). What
aren't I getting?

TIA
me
You can make clientnumbers subreports or you can remove the
clientnumbers from the query and get the data using dlookup
functions.
 
K

KARL DEWEY

This will pull the two records necessary for both EIN & SSN --
SELECT tblClients.ClientID, tblClientNumbers.SSN, tblClientNumbers_1.EIN

FROM (tblClients LEFT JOIN tblClientNumbers ON tblClients.ClientID =
tblClientNumbers.ClientNoID) LEFT JOIN tblClientNumbers AS tblClientNumbers_1
ON tblClients.ClientID = tblClientNumbers.ClientNoID;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top