Need help with table design and relationships

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Im making a client management database and need a little help. The goal of
this database is to be able to pull up a specific client and display the
client information, below that you will see all the related projects for the
specific client. You can then open the projects individually to edit them
and stuff.

As far as tables I was thinking

tblClients - contains client information, name address etc

partly confused here.
tblProjects - holds project information.
can I use one table to hold all project information for every client or does
each client need its own table? Im assuming using relationships I can have
all the projects in one table and link them via some sort of ID, but this is
where I get lost.

Can anyone offer any help on this matter?

Thanks
 
Hi Andrew

No, you certainly shouldn't have a separate table for each client's
projects. You simply need to create a one-to-many relationship between the
Clients table and the Projects table.

Your Clients table needs to have a primary key - I suggest an Autonumber
field - called, say, ClientID.

Your Projects table needs a corresponding "foreign key" field (PrjClient) of
the same type. (If ClientID is an Autonumber, then for "same type" read
"numeric long".)

Now use the Relationships window to create a one-to-many relationship
betyween ClientID and PrjClient. The PrjClient field will contain the
*value* of the ClientID field from the record for the client that "owns" the
project.

You can then create a form/subform bound to the two tables, and as you
display a client in the main form, the related projects will automatically
be displayed in the subform.
 
One table for clients, and one for projects.

Client table has a primary key field (ClientID)

Projects table has a Primary Key field (ProjectID) AND a foreign key field
(ClientID)

A relationship is formed beween the two tables by joining the two ClientID
fields together.

That's the basics...

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
i get the idea - ill give it a shot but I may need some help still. ill let
you know, thanks
 
How do I setup the foreign key field and link it to the primary field of the
clients table?
 
Make sure that each table has a field called ClientID in it. Then open the
relationships window add the two relevant tables (Client and Projects).
Drag the ClientID field from the Client table over to the ClientID field in
the Projects table. You will get a dialog box called Edit
Relationships...check the Enforce Referntial Integrity checkbox.

You might want to read up on some of this in Access help as well.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
Back
Top