Project Access

[Leeroybrown]

Hey everyone,

I'm currently trying to build an database using Access 2010.

I've created a number of tables but I'm getting a little confused and would be extremely grateful of you assistance.

The tables I have built are as follows:

1, Client Information

Fields

Client Name
Company Registration Number
Type of Industry
Main Contact
Secondary Contact
Day to Day Contact
Registered Company Address
Parent Company
Company Logo
Website Address
Date of First Contact
Contact made by
Date Proposal Issued
Proposal Document
Date Contract
Date Contract Signed
Copy of Signed Contract
Start Date of Service Provision
Renewal Date of Service Provision
Payment Information
Length of Contract
Number of Contracted Visits
Appointed Client Manager ID

Table 2 - Payment Information

Fields

Client ID
Type of Payment
Amount to be Paid
Frequency of Payment
First Payment Made
Date of First Payment
Main Contact for Payments
Finance Director (if known)

Table 3 - Client Expenses Information

Fields

Client ID
Claim Number
Reason for Expense
Date of Claim
Amount
Notes

Table 4 - Contact Information

Fields

Client ID
First Name
Last Name
Nick Name
Job Title / Position
Location / Department
Land Line Contact Number
Mobile Phone Number
Email
Football Team Supported
Hobbies
Spouse / Partner
Birthday
Gender
Notes

Table 5 - Client Services Provided Record

Fields

Client ID
Representative
Type of Contact
Date of Contact
Details of Client Contact
Service Provided
Details of Future Contact

Table 6 - Client Property / Location Addresses

Fields

Client ID
Type of Address
Location / Department
Number / Property Name
Street
City / Town
County
Post Code
Country

Table 7 - Employee Information

Fields

Employee ID
Client Manager ID
First Name
Last Name
Initials
Employee Number
Position
Landline Contact Number
Mobile Phone Number
Email Address


Now I've been reading through Microsoft Access 2010 all in one for dummies but I'm getting confused with a number of factors:

1, My clients can have a primary address (head office) but they can also have secondary addresses such as construction sites that may be there for a limited period of time.

Is it best to have a separate table for the addresses as I have created?

2, Each Client may have a significant amount of Employees

Is it best to have a separate table for contact information

3, Primary Keys

I understand that each table requires a primary key, Can the Client ID be the Primary key and will it need a junction table to create the Client ID?

4, Relationships

I have tried my best to figure this out and just get soo confused, I would like to link up all the tables to the main client ID but for some reason I'm struggling with this.

Can you advise of the best way to relate them to each other.

Please Help.

LRB

 

[spikepl]

Each chunk of info in the db should appear only once. Keys are the means to link relevant information.

Eg.

Clients
-------
ClientID (PrimaryKey)
Permanent client data ...

SiteAdresses
------------
SiteAdressID (PrimaryKey)
ClientID (this is the foreign key, relating the given address to the specific client, so for one client you can have as many addresses as you want
FromDate (eg. to show when the address is valid)
ToDate (when the address becomes obsolete)
Other address data ...

If multiple clients can share the same siteaddress, then you'd need a many-to-many relation instead of the one-to-many outlined above. And so:


SiteAdresses (all site addresses for all clients)
------------
SiteAdressID (PrimaryKey)
Other address data ...

ClientAdresses (this is the junction table, for each link between a client and an address)
------------------------
SiteAddressID (Foreign key, pointing at the site address)
ClientID (Foreign key, pointing at the client)
FromDate
ToDate

 

[stopher]

1, My clients can have a primary address (head office) but they can also have secondary addresses such as construction sites that may be there for a limited period of time.

Is it best to have a separate table for the addresses as I have created?

I would hold all addresses in a separate table. The benefit is that if you want to list all addresses for a specific client at a given time, then this is easy from an Address table. But if you start have new fields for each new address, this is going to be difficult to manage. Your Address table can have a key to identify what type of address it is so you can still list primary addresses only if needed.

2, Each Client may have a significant amount of Employees

Is it best to have a separate table for contact information

Yes.

3, Primary Keys

I understand that each table requires a primary key, Can the Client ID be the Primary key and will it need a junction table to create the Client ID?

Yes, each table should have a primary key. Client ID would be the primary key for the client table. Your other tables will have their own primary key. So you might have ContactID as the primary key in the contact table.

The primary key is the mechanism for uniquely identifying each record in each table. So Autonumber is often used to generate values for primary keys. The user does not need to be aware of the value of the primary key.

4, Relationships

I have tried my best to figure this out and just get soo confused, I would like to link up all the tables to the main client ID but for some reason I'm struggling with this.

Can you advise of the best way to relate them to each other.

Take a stab and then post your database.

 

[Leeroybrown]

Table 3 - Client Expenses Information

Fields

Client ID
Claim Number
Reason for Expense
Date of Claim
Amount
Notes

I've just been having a think and based on the Client Expenses Table, I'm thinking that the Claim Number would be the primary key as this would be an auto number

But how do I then make the relationship back to the client?
Would the relationship be the client id and does this not matter that it's not the primary key?

LRB

 

[stopher]

Quite rightly your expenses table includes ClientID. This is the foreign key and is what is use to link back to your client table.

And yes, claim number will be an ideal Primary Key for the expenses table.

 

[Leeroybrown]

I have now set up the relationship for Client and Client Expenses

My post count is too low so I cannot post an img of the relationship.

I have done the following

Client Information Client Expenses Information

Client ID (primary key) to Client ID (one to many)
Claim Number (primary key)




I hope that is the correct way, if it is then I will go on to connect other tables and build the relationships


LRB

 

[VilaRestal]

Feel free to post a few times to get up to ten if need be. It's to stop spammers so nobody will mind if it's for a good cause.

One thing I would advise before you go too far down the route of relating all the tables is not to use / in table or field names (wouldn't advise spaces either) but / is a mathematical operator and it can make queries and code confusing to read and a bit more prone to logical errors.

In fact I would generally recommend a naming convention for fields and tables were there's never any spaces or non-alphanumeric characters and there's a prefix to each name that tells what type of field or table it is.

It pays for itself in the long run when queries and code starts to get complicated and you're trying to debug it.

 

[VilaRestal]

Thanks for the thanks but that doesn't count as a post

 

[Leeroybrown]

Thanks for the thanks but that doesn't count as a post

Okay thanks again,

Could you advise what the field names would be?

I will remove the spaces and put an _ in, from what I've read this is acceptable.

LRB

 

[Leeroybrown]

I'll also remove the / from the tables

 

[VilaRestal]

Also, and this is perhaps more personal preference, for any table that has a single numeric primary key, I would always call it just ID (not ClientID for example).

Some might say that means if you have a query with two tables then you have to distinguish between the two ID fields but I think that is no more time consuming than having to type a longer field name:

tblClients.ID as opposed to clientID

The advantage is if you have a table Clients with primary key ClientID joined to a table SiteAddresses with a foreign key primary key, a query with the two tables joined is going to have to distinguish between the fields:

Clients.ClientID and SiteAddresses.ClientID

vs

Clients.ID and SiteAddresses.ClientID


To my mind Clients.ClientID is a waste of ink - we know it's a client, the table name gives that away

 

[Leeroybrown]

Also, and this is perhaps more personal preference, for any table that has a single numeric primary key, I would always call it just ID (not ClientID for example).

Some might say that means if you have a query with two tables then you have to distinguish between the two ID fields but I think that is no more time consuming than having to type a longer field name:

tblClients.ID as opposed to clientID

The advantage is if you have a table Clients with primary key ClientID joined to a table SiteAddresses with a foreign key primary key, a query with the two tables joined is going to have to distinguish between the fields:

Clients.ClientID and SiteAddresses.ClientID

vs

Clients.ID and SiteAddresses.ClientID


To my mind Clients.ClientID is a waste of ink - we know it's a client, the table name gives that away

Okay, that gives me something to think about , thanks

 

[VilaRestal]

Could you advise what the field names would be?

How detailed the convention depends on how complex the database will be.

I would make Table 1:

tblClients

Fields:

ID
keyAppointedClientManager (perhaps a shorter name would suffice)
strName
strCompanyReg
strIndustryType
strParentCompany (perhaps make this a key linked to another table)
oleLogo
strWebsite
dtFirstContact
strFirstContactMadeBy (perhaps make this a key linked to another table)
dtProposal
strProposalDoc (perhaps it's meant to be an ole rather than just doc's name)
dtContract
dtContractSigned
oleCopyOfContract
dtServiceStarted
dtServiceRenewed
strPaymentInformation
intContractLength
intContractedVisits

 

[Leeroybrown]

How detailed the convention depends on how complex the database will be.

I would make Table 1:

tblClients

Fields:

ID
keyAppointedClientManager (perhaps a shorter name would suffice)
strName
strCompanyReg
strIndustryType
strParentCompany (perhaps make this a key linked to another table)
oleLogo
strWebsite
dtFirstContact
strFirstContactMadeBy (perhaps make this a key linked to another table)
dtProposal
strProposalDoc (perhaps it's meant to be an ole rather than just doc's name)
dtContract
dtContractSigned
oleCopyOfContract
dtServiceStarted
dtServiceRenewed
strPaymentInformation
intContractLength
intContractedVisits

Fantastic,



the field for Payment information can I link this to the payment table?

strFirstContactMadeBy (perhaps make this a key linked to another table)

when you say this ia key linked to another table how do you get another key apart from the primary key?

 

[spikepl]

I would suggest that you download one of the free templates from Microsoft and see how it ticks. http://office.microsoft.com/en-us/templates/CT010152732.aspx?av=ZAC

 

[Leeroybrown]

Thanks, I'll give it a go.

O look 10 posts

LRB

 

[VilaRestal]

strFirstContactMadeBy (perhaps make this a key linked to another table)

when you say this ia key linked to another table how do you get another key apart from the primary key?

You might have a table of staff in your company and this field would contain the ID of the staff member who made the contact (and the tables would be linked tblClients.keyFirstContactBy to tblStaff.ID

The key prefix just shows it's a field that links to another table (that's how I use it )

 

[Leeroybrown]

With regards to the table names can I have spaces in the table name for example:

tbl Client Expenses

or

Does it need to be

tblClientExpenses

or can it be

tbl_Client_Expenses

LRB

 

[spikepl]

Syntactically, you can have spaces. At your own peril. It WILL bite you in your derriere, sooner or later. When you need to refer to such tables you'll be condemned to using [].

Skip spaces and underscores.

 

[spikepl]

While we are at it: Beelzebub (i.e. MS) invented a temptation called Mulitvalued fields. Again, use at own risk. It's a wonderful shortcut to hell.