Project Access

[Leeroybrown]

This is the new relationships that I've set up, could you advise if I'm on the right track?



LRB

 

[VilaRestal]

It looks to me that...

For tblClientServicesProvided:

ID is its own record ID (autonumber perhaps)
it needs a field ClientID that will link to tblClients.ID

For tblContactInformation:

Its Id field is probably not the same as tblClients so shouldn't be linked by that field
Is it ClientContactInformation or general contact information for not just clients?
If the first then I suspect you want tblClientContacts (I'm renaming it ) to have its own ID field (autonumber probably) and a ClientID field that will join to tblClients.ID

Pretty much the same goes for tblExpenses

Lastly, every table contains information and records. There's no need to use those words in a table's name

On and lastly lastly, have a field with (if known) in its name is bad for two reasons: brackets are used for other things in code (like /*+-<>^! but even more so) and like them risk errors and make code hard to read if used in table or field names. Also, that kind of qualifier would go on a form but not in the field name. Presumably, all data is only entered 'if known'.

 

[spikepl]

Not quite.

Each record in a table has to have own primary key, and/ or a composite key of foreign keys.

So that you can record multiple items for a client, but the table containing these items also containes items for other clients. Eg:

tblClients
--------
ClientID (PK)
..

tblPayments
-----------
PaymentID (PK) - so each payment has own ID
ClientID -so each payment is linked to a given client
Amount
Date


before you continue, get a template and study how the relatioships are set up

 

[Leeroybrown]

It looks to me that...

For tblClientServicesProvided:

ID is its own record ID (autonumber perhaps)
it needs a field ClientID that will link to tblClients.ID

Okay, I've changed the Fields names to ClientID

For tblContactInformation:

Its Id field is probably not the same as tblClients so shouldn't be linked by that field
Is it ClientContactInformation or general contact information for not just clients?
If the first then I suspect you want tblClientContacts (I'm renaming it ) to have its own ID field (autonumber probably) and a ClientID field that will join to tblClients.ID

Pretty much the same goes for tblExpenses

I've also changed the table names as suggested (tblClientContacts and tblClientExpenses).

Lastly, every table contains information and records. There's no need to use those words in a table's name


I've removed all the references to information and records.

On and lastly lastly, have a field with (if known) in its name is bad for two reasons: brackets are used for other things in code (like /*+-<>^! but even more so) and like them risk errors and make code hard to read if used in table or field names. Also, that kind of qualifier would go on a form but not in the field name. Presumably, all data is only entered 'if known'.

Please above where I have edited the post and put replies in.

I've removed (if known) from the tables.

Please also find attached my new relationship layout and a jpeg and excel spreadsheet of the table design.

I think I may of messed the relationship up between tblEmployee and tblClients

LRB

 

[Leeroybrown]

Please find attached my current database

 

[spikepl]

A few comments:

1. Do not use lookup fields in your tables - that is not recommendend practice. You can use combo boxe on the forms for those fields
2. Customarily, one would place the primary key at the top of the table.
3. Do not use ClientID as text : a text would be a ClientName. Use the Autonumber Primary Key to link with other tables
4. If you call Autgoenerated fields as ..._Number, one could be lead to believe these fields are visible to users. Those fields are keys for linking data, and their values should be immetarial and not visible for human consumption.
5. Having a Record_NUmber in tblClientContacts is a bit of a misnomer. Call it something unique, eg ClientContactID -I tend to give the primary key the name of the item that the table holds.
6. Hyperlinks? Maybe. Depends on what you want to do with that field further down the road. Storing that data as a string may be much better and simpler. It depends.
7. Phonenumbers as Number ? Maybe. What about extensions for those who don't have a direct line
8. tblEmployees ??? You need to be able to store employees for many clients? If so you need a ClientID in there. Also, if you use Initials as PK, then no two persons with same initials can exist in that table. Use the Employee_ID as PK
9. Use SHORT names for things. AS short as possible, yet significant. tblClientPropertyLocationAddresses could be tblClientAddresses or tblClientSites, or tblSites or tblAddresses
10. You may want to have a field ShortName for clients, so that JohnWeMakeThisAndThatGizmoSince1782andBrothersLTD can be referred to eg as GizmoLtd in searches, internal lists, comboboxes etc.