MS Access 2010 Question

[ITNoobie]

I am creating a database in MS Access 2010, I have 6 tables. My question do all 6 tables need to have either a direct relationship (primary key to primary key) or Primary Key to Foreign Key with each other?

[urtrivedi]

having relationship is good thing to do in database, but it is not mandatory at all. You will not see much pk to pk relation (1 to 1). Generally you will find (pk-fk) kind of relation much in database.

[ITNoobie]

Ok, so basically all 6 tables in my database should have a primary key to foreign key relationship to each other?

[urtrivedi]

NO its not cumpulsory, but if you wish you can create relationship to avoid errneous records

[boblarson]

Ok, so basically all 6 tables in my database should have a primary key to foreign key relationship to each other?

I would say that, NORMALLY you would have tables which are linked together in some way. It is RARE to not be able to link them together. But also, if you do have them linked, if the links seem to form a big circle of sorts then you probably have something wrong with the design.

So, if you would like to list out the tables you have and their fields I'm sure that we could give you more detailed assistance, instead of the generalizing which may, or may not, fit your situation.

[ITNoobie]

Sorry for the delay beena bit busy with school ad work. So here are my tables and the fields that are in those tables. the pk as you probably already know is the primary key. The duplicate names in the fields of the tables are so i can link pk to fk.

(Client info)Table 1
PK - Client ID
Company Name
Contact First Name
Contact Last Name
Address
City
State
Zip
Phone Number
Fax Number
E-mail

(Client invoice totals)Table 2
PK-Client invoice id
Client Id
Invoice amount
Study total
Supply total
PRI invoice id
study name id

(Payment itemization)Table 3
PK - Client invoice id
client id
quantity
supply description
CPI
Payment
balance
PRI invoice id

(PRI invoice number) Table 4
PK - PRI invoice ID
Client id
Client invoice id

(Study Details) Table 5
PK - Study name ID
study name
client id
PO Number
Job number
Market
Study Date
PRI invoice Id

(Study Type) Table 6
PK- client ID
study type

[boblarson]

Okay, the first thing is that you should not have Table 2 - the Invoice Totals. That stores calculated data which is not a properly normalized structure. So, you would build that by pulling the Invoice Details and summing them.

From your post here's how I see the table structure:

(Client info)Table 1
PK - ClientID
CompanyName
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail

If the client can have more than one contact, you might want to revise that first table to be like this:

(ClientInfo) Table 1
PK – ClientID
CompanyName
CompanyAddress
CompanyCity
CompanyState
CompanyZip
CompanyPhone
CompanyFax
CompanyWebsite

(Contacts) Table 1a
PK - ContactID (Autonumber)
ClientID – Long Integer (FK)
ContactFirstName
ContactLastName
Address
City
State
Zip
Phone Number
Fax Number
E-mail
UseCompanyAddress (Yes/No)


(Client invoice Header)Table 2
PK-ClientInvoiceID
ClientId
InvoiceDate
StudyNameID

(Invoice Details) Table 3
PK – InvoiceDetailsID (Autonumber)
ClientInvoiceID – Long Integer (FK)
Quantity
SupplyID – Long Integer (FK)

(Supplies) Table 4
PK -- SupplyID (Autonumber)
SupplyDescription


(Payment itemization)Table 5
PK – PaymentID (Autonumber)
ClientInvoiceID – Long Integer (FK)
CPI
Payment

What is PRI invoice number???  I don’t think you need this table
(PRI invoice number) Table x
PK - PRI invoice ID
Client id
Client invoice id

(Studies) Table 6
PK – StudyID (Autonumber)
StudyName
ClientID – Long Integer (FK)
PONumber
Jobnumber
MarketID – Long Integer (FK)
StudyDate

(Study Type) Table 7
PK- StudyTypeID
StudyType

(Market) Table 8
PK - MarketID – (Autonumber)
MarketName

I don't know what CPI standa for so I left it where it was and I took out the PRI Invoice table and so you might explain what that was.