Table relationships

[bloukopkoggelmander]

Hi all

I have been working on this project now for 4 months and is still no further than the first step. This is driving me to suicide.

As a testing database I have set up the following:

Table 1(Clients) :

ClientID
Firtsname
Lastname
HouseNumber
Streetname
Town
Postcode
HomePhone
MobileNumber
Email

Tabel 2(Treatments) :

TreatmentID
Treatment
Cost
Date

Now I am not sure how to relate these tables . I also want to set up a tabbed form and on the first page only enter new client details but then on the second enter any treatments for existing clients. Now how do you link a specific treatment to a client?

After 4 months I should know how to do this, but I think I have gotten myself into such a muddle I dont know left from right and plus we have just upgraded to Access 2007.

Thanks for any advice.

[MMcCarthy]

Hi all

I have been working on this project now for 4 months and is still no further than the first step. This is driving me to suicide.

As a testing database I have set up the following:

Table 1(Clients) :

ClientID
Firtsname
Lastname
HouseNumber
Streetname
Town
Postcode
HomePhone
MobileNumber
Email

Tabel 2(Treatments) :

TreatmentID
Treatment
Cost
Date

Now I am not sure how to relate these tables . I also want to set up a tabbed form and on the first page only enter new client details but then on the second enter any treatments for existing clients. Now how do you link a specific treatment to a client?

After 4 months I should know how to do this, but I think I have gotten myself into such a muddle I dont know left from right and plus we have just upgraded to Access 2007.

Thanks for any advice.

Your treatment table needs to have a foreign key to the Client table as follows:

Tabel 2(Treatments) :

TreatmentID
ClientID (Foreign key referencing the Primary key of the clients table)
Treatment
Cost
Date

It would be worth you while checking out this tutorial:

Normalisation and Table structures

[bloukopkoggelmander]

Hi there

Thank you for reply. Yes sorry, I forgot to mention that I included the Client ID as a foreign key in the Treatments table. I also thought at one point of using a linking table instead, but I suppose that will be wrong. Also the Treatments and Prices will be lookups as they would rarely change.

But I will have a look at that link you included in the reply.

Thanks a lot

[MMcCarthy]

Hi there

Thank you for reply. Yes sorry, I forgot to mention that I included the Client ID as a foreign key in the Treatments table. I also thought at one point of using a linking table instead, but I suppose that will be wrong. Also the Treatments and Prices will be lookups as they would rarely change.

But I will have a look at that link you included in the reply.

Thanks a lot

If you have the ClientID in. Set the indexed property to Yes (Duplicates OK). Then open the relationships window and double click on the relations and tick all the referential integrity and cascading options.

On the tabbed form. Set the record source of the main form to clients table and put field controls on first page tab. On second page tab put subform based on treatments table and use master/child properties to link the ClientID fields.

Mary

[nico5038]

I get the impression that your clients can have multiple treatments.
In such a case you create a relationtable holding both the ClientID and the TreatmentID.
Additionally you can aad the TreatmentDate and when prices can change, the actual price can be added too. The other option would be to give the treatments also a startdate and when a new price is added, add a row with a new ID, or extract the price with the TreatmentDate from the treatment table when it holds a start (and/or end) date.

Nic;o)

[MMcCarthy]

I get the impression that your clients can have multiple treatments.
In such a case you create a relationtable holding both the ClientID and the TreatmentID.
Additionally you can aad the TreatmentDate and when prices can change, the actual price can be added too. The other option would be to give the treatments also a startdate and when a new price is added, add a row with a new ID, or extract the price with the TreatmentDate from the treatment table when it holds a start (and/or end) date.

Nic;o)

Good catch Nico. I'm slipping .. old age you know.

This would mean you would now have three tables one to hold clients, one to hold treatments and one to hold the join relationship as Nico outlined.

Table3
ClientID
TreatmentID


You would then include the join table with the treatments table in a query for the subform.

[bloukopkoggelmander]

Bril!

Thank you sooo much guys. I will have a go at your suggestions this morning and see how I get on. I will let you know if I have any more problems.

Thanks a lot

[NeoPa]

Hi there

Thank you for reply. Yes sorry, I forgot to mention that I included the Client ID as a foreign key in the Treatments table. I also thought at one point of using a linking table instead, but I suppose that will be wrong. Also the Treatments and Prices will be lookups as they would rarely change.

But I will have a look at that link you included in the reply.

Thanks a lot

Well spotted Nico.
I can see from the OP's second post that he was thinking along the right lines before, but sometimes, when so many issues that you're not sure of all run together it can be so confusing you can't even recognise when you've found the right answer. I sympathise.
That is where TSDN works so well, as we don't have the pressure of all the problems to worry about. A little experience helps too of course ;)

[bloukopkoggelmander]

Hi guys, me again!

Sorry to bother you guys again, but I am slightly stuck on another issue. I have now set up the tables as follows :

Clients

ClientID
ClientFirstName
ClientLastName
HomeNumber
StreetName
Town
County
Psotcode
HomeTel
MobileTel
Email


Treatments
TreatmentID
Treatnment(This uses a lookup column of treatments I have entered)
Cost(This is a lookup column of costs I have entered myself)
Date


Client Treatments (This is the linking table for Clients and Treatments)

ClientID
TreatmentID

Now I have managed to set up a form to enter new Clients and this tested fine with all filed in the Client table being populated.

The problem I have is to create a form for new treatments . I want the following fields on the form : Username(This to be concatenated to include First and Lastname), Treatment,Cost, Date. I have tried various different Queries to accomplish this but just cannot find a way to relate a treatment to a client so that the relevant tables are populated with relevant data.I have also added the following to the query to display the username : Full Name:[ClientFirst]&" "&[ClientLast], but still no joy. Just cannot see where I am going wrong.

[MMcCarthy]

Create a query as follows:

SELECT [Client Treatments].ClientID,  [Treatment].Treatment, [Treatment].Cost, [Treatment].[Date]
FROM  [Client Treatments] INNER JOIN [Treatment]
ON   [Client Treatments].[TreatmentID] = [Treatment].[TreatmentID];

Set up a subform on the Main form based on this query. Use the subform wizard .