Big "How Do I?" With This Database Relationship/Table

[Guest_imported]

I'm trying to create a database that keeps track of clients.
I have ***First Name, Last Name, Address, City State*** on one table called [Client]

In another table I have ***Purchase Description, Amount, Date of Purchase, Grand Total*** This table is called [Client Purchases]

The problem I am running into is the actual relationships. I tried creating First Name and Last Name on the [Client Purchases] table and I tried every combination of relationships. For example I set both first and last name as primary keys, in this case I was only able to do a one-to-one. I want to be able to have a one-to-many relationship. Another time I set the First and Last Name on the [Client] table as primary keys and had no primary keys on my [Client Purchases] in this case the join was "indeterminate"

What I am getting at is that I need some way of joining the two tables because with all databases that keep track of customers there could be multiple customers with the same first name and multiple customers with the same last name. I need to find some way so that my customer lets call him "John Doe" and another customer "Jane Doe" are differentiated between although they have the same last name etc... Thanks so much in advance.


Lewie

 

[Bastien]

simple solution:

on table "clients"
add a field called clientID (primary key, autonumber, or some unique way to identify the "client&quot

on "purchases" table
add field clientID
that is filled in with the clientId from the "clients" table when a purchase is made....

link the two in the relationships screen and bob's your uncle...

hth Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[GHolden]

You need a unique key in the client table. The easiest way to achieve this is to add an autonumber field to the table. The autonumber will automatically increment each time you add a client.
For this example call this field ClientCode.

Then in the Client Purchases table you would also have the ClientCode field.

Each time you add a client purchase you would add the appropriate ClientCode to the purchase.

So ClientCode is unique in the Client table, but there could be many in the client purchase table.

So you create a one to many relationship

 

[Guest_imported]

So down the road, will I be able to search by client name? Like if I wanted to search for a particular client let's say "Jane Doe" ??

 

[GHolden]

You would search the client table by client name, you would then also have that clients clientcode. You could then retrieve all client purchases by filtering the client purchase table by that clientcode.