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
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