By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,726 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

Table relationships

100+
P: 184
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.
Feb 8 '07 #1
Share this Question
Share on Google+
78 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 8 '07 #2

100+
P: 184
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
Feb 8 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 8 '07 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 8 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Feb 8 '07 #6

100+
P: 184
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
Feb 9 '07 #7

NeoPa
Expert Mod 15k+
P: 31,434
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 ;)
Feb 10 '07 #8

100+
P: 184
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.
Feb 12 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Create a query as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Client Treatments].ClientID,  [Treatment].Treatment, [Treatment].Cost, [Treatment].[Date]
  2. FROM  [Client Treatments] INNER JOIN [Treatment]
  3. ON   [Client Treatments].[TreatmentID] = [Treatment].[TreatmentID];
Set up a subform on the Main form based on this query. Use the subform wizard .
Feb 12 '07 #10

100+
P: 184
Thank you Mr. Mccarthy

I will give it a go and let you know if I have any problems.!!
Feb 12 '07 #11

NeoPa
Expert Mod 15k+
P: 31,434
Thank you Mr. Mccarthy
You don't want to confuse Mary with Paul ;)
Feb 12 '07 #12

100+
P: 184
Oh damn! Sorry man, did not mean to offend anyone.

If I got it wrong then please accept my appologies.
Feb 12 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
You don't want to confuse Mary with Paul ;)
Different spelling in the surname.
Feb 12 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Oh damn! Sorry man, did not mean to offend anyone.

If I got it wrong then please accept my appologies.
Don't worry there is no offence :)

It's a common mistake and I'm well used to it. NeoPa was just being friendly and pointing out the correction (and teasing me while he's at it).

Mary
Feb 12 '07 #15

NeoPa
Expert Mod 15k+
P: 31,434
Oh damn! Sorry man, did not mean to offend anyone.

If I got it wrong then please accept my appologies.
As Mary says - I was just pulling your chain.
The ;) generally indicates a jokey manner and certainly does in this case.

...Besides, I was way off base confusing McCarthy with McCartney anyway :(
Feb 12 '07 #16

100+
P: 184
No probs guys! All taken and given in very good spirit. TheScripts is a happy place to be anyway :-).

I have a problem with what you got me to try earleir, but I am going to trry and thrash it out for another couple of hours and if I still suffer I will get back to you all.
Feb 12 '07 #17

NeoPa
Expert Mod 15k+
P: 31,434
A great attitude. I wish all our members were as easy to deal with. Let us know anytime you need more help.
Feb 12 '07 #18

100+
P: 184
Morning

Right , I do have a little problem I cant get past. For the treatments form I have written a query to include the following :

ClientID
Treatment(Lookup to treatments to select)
Cost(Lookup to prices to select)
Date
Full Name ( Full Name:[ClientFirst]&" "&[ClientLast]

Now the only problem I have left is that Full Name does not present me with a list of existing customers to select from, instead it only displays the first client name from the table and I cannot even edit it.

Cant figure out where I am going wrong here. I have tried redoing it about 5 times now.
Feb 13 '07 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Full Name: (SELECT [ClientFirst] & " " & [ClientLast] FROM Clients WHERE ClientID=[Client Treatments].ClientID)
  2.  
Mary
Feb 13 '07 #20

100+
P: 184
Morning Mary

Thank you for your reply. Right I gave that a go but I only get a blank form as result with literally nothing on it .

I have tried using the code when creating a new form in design and also by editing the code after using the form wizard. Guess I am trying to do this the wrong way. Overworked numpty that I am....:-)
Feb 13 '07 #21

100+
P: 184
Actually

I am starting to wonder if this is a issue within Access 2007, because I just cannot get the username concatenated. It just would not display.
Feb 13 '07 #22

NeoPa
Expert Mod 15k+
P: 31,434
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Full Name: (SELECT [ClientFirst] & " " & [ClientLast] FROM Clients WHERE ClientID=[Client Treatments].ClientID)
  2.  
Mary
If this is a subquery (which it looks like) then the ([Client Treatments].ClientID) reference in the WHERE clause is undefined so it shouldn't work. I'm assuming this is to be assigned in Design View.
Feb 13 '07 #23

NeoPa
Expert Mod 15k+
P: 31,434
Actually

I am starting to wonder if this is a issue within Access 2007, because I just cannot get the username concatenated. It just would not display.
Try running a SQL query with :
Expand|Select|Wrap|Line Numbers
  1. SELECT [ClientFirst],[ClientLast],
  2.        [ClientFirst] & " " & [ClientLast] AS ClientFull
  3. FROM Clients
See what this gives you. It will tell you if the & (Concatenation) works.
Feb 13 '07 #24

100+
P: 184
Thanks Neopa

I will give that a go and let you know.

Thanks
Feb 14 '07 #25

100+
P: 184
Hello NeoPa

Right I gave it a go, but still no luck. When I create a form by wizard or design, I keep being prompted with a Enter Parameter Value box for both first and last name.

Getting past theat there are 3 fields on the form:

First name
Last name
Full name

All three just appears as text boxes and not displaying any text( I already have my Clients table with Client first and last names entered.

Very strange this becuase I could do this fine in Access 2003.....
Feb 14 '07 #26

NeoPa
Expert Mod 15k+
P: 31,434
You say :
Right I gave it a go, but still no luck.
You don't say what happened. That's all I want to know about for the moment. You can't build on sand - we need to get the basics sorted out first.
Feb 14 '07 #27

100+
P: 184
Hi Neopa

My appologies, I might not have explained myself very well. The last reply I put in was in fact an explanation of what happend after I entered the SQL statement you gave me.

What I meant was that I have created a new query using the SQL statement you provided and then I created a new form based on this as explained in the last post.

I hope this helps clarify? If not let me know.

(It's a shame I cant attach screenshots of what I am trying. It would make it easier)
Feb 14 '07 #28

NeoPa
Expert Mod 15k+
P: 31,434
I know its difficult but when working in databases you need to learn to take things slowly and carefully. Above all precisely.
I wanted you to test the SQL in a query and not build anything on that query. I still need to know exactly what you get when running that specific SQL in a QueryDef.
Feb 14 '07 #29

NeoPa
Expert Mod 15k+
P: 31,434
Hang on :
Looking at post #19 (where I got the field names from) the names are different from post #9!
What's going on here?
I've got to go out now so can't respond shortly but I'll check later for your response.
Feb 14 '07 #30

100+
P: 184
Neopa

Just to put your worries to rest on that one, I saw that the fields you specified werent the correct ones so I did change it to the correct ones. I will do as you asked with the query and let you know.

Thanks
Feb 14 '07 #31

100+
P: 184
Right

I have done as you asked and created the query and run it. There were no errors and the result it gave me was :

Three fields as follows :
FirstName (It listed all first names of all clients entered into my Clients table)
LastName ( It listed all lastnames of all clients entered into my Clients table)
ClientFull(It listed the concatenated first and lastnames of all clients entered in my Clients tabel)
Feb 14 '07 #32

NeoPa
Expert Mod 15k+
P: 31,434
So essentially that worked well. That clarifies that point. Thanks for the other post btw - it helps me to focus on what may be a problem, and not waste effort on inconsequentials :)
Now, with this query saved as a QueryDef you need to :
  1. Ensure that it (or a more complex query including both of these fields - FirstName & LastName - tested to return the expected results of course) is used as the source for your form.
  2. When that is done, ensure that the two controls for the First & Last Names are correctly bound to the fields from the QueryDef.
  3. Check that this form returns these two fields as expected.
  4. Update a control (or add a new one) to show the full name as Me![].ControlSource = "=[FirstName] & "" "" & [LastName]".
When you reply - please include the names of all items introduced (QueryDef; Form; All the controls; etc).
Feb 14 '07 #33

100+
P: 184
Morning Neopa

Right I have carried out your instructions, but yet again unsuccessfull. Here is what I have done:


I created a new query(TestQuery) and included Clients.FirstName,Clients.LastName,Clients.ClientF ull(ClientFull:[FirstName]&" "&[ClientLast]. I then ran this query and it returned all fields with all the relevant data, including the concatenated field with the concatenated names in it.

I then created a new form(TestForm) and based the form Record Source on TestQuery. I then made sure that all items on the form (FirstName,LastName,ClientFull) had their Control Source set to the corresponding fields that the form is based on. I then tested the form and now no values were displayed for either FirstName,LastName,ClientFull.

I then updated the control as you requested and tested it again . This time FirstName and LastName were still left blank, but ClientFull had #Name? displayed in it.




As for your 4 points on post #33 :

1. The query I created and tested returned the correct values beofre bounding it to any form.
2. The two controls for the First & Last Names are correctly bound to the
fields from the QueryDef.
3.The answer is no as no values are returned this time.
4.Before updating the control, all fields on the form are blank.After updating the control I recieve #Name? for ClientFull with FirstName and LastName still empty.


I hope this is comprehensive enough, if not then please let me know.

Thanks
Feb 15 '07 #34

100+
P: 184
I've done it!!

I have open TestForm end edited the properties for ClientFull.

I left Control Source empty.
For RowSource I entered the following : Select TestQuery.ClientFull AS ClientFull From Testquery.

On the form now I get a dropdown box with all the concatenated names listed and I am able to select which one I need.

All I have to do now is to get it to work on the form I am planning to set up.
Feb 15 '07 #35

NeoPa
Expert Mod 15k+
P: 31,434
As for your 4 points on post #33 :

1. The query I created and tested returned the correct values before binding it to any form.
2. The two controls for the First & Last Names are correctly bound to the fields from the QueryDef.
3.The answer is no as no values are returned this time.
4.Before updating the control, all fields on the form are blank.After updating the control I recieve #Name? for ClientFull with FirstName and LastName still empty.

I hope this is comprehensive enough, if not then please let me know.
Last first : Yes this is very clear thank you.
I've read your latest post and, although that might appear to work, it is likely to give you issues when working with live data (You would be accessing the table from two different queries at the same time when using the form).
Your answer to question two intrigues me.
The way I would determine if the fields were bound correctly is by seeing the results. As reported in 3 - the two fields (both of them) appear not to be correctly bound.
Can you post for me the values in :
  1. The Form's Record Source property (If, as I suspect, it is a QueryDef, then please include the SQL used).
  2. The Control source of the TextBox used for the [FirstName].
This sort of problem would be a two-minute issue if the database were available, but things are a lot more clumsy when working via posts in a forum :(.
Feb 15 '07 #36

100+
P: 184
Jip no problems.

I have now set everything back to it was before thechanges I made in post #35.

The details are as follows:

Form Record Source = TestQuery
TextBox Control Source = ClientFull

You are right, the changes I made have been causing me problems. All sorts of strange things were happening in my tables. Well like I said before, it would have been great if we could have posted screenshots of what we are trying to do because it will be soooo much easier for someone like yourself who tries to help others.
Feb 15 '07 #37

NeoPa
Expert Mod 15k+
P: 31,434
Can you post for me the values in :
  1. The Form's Record Source property (If, as I suspect, it is a QueryDef, then please include the SQL used).
  2. The Control source of the TextBox used for the [FirstName].
I still need :
1. The SQL of [TestQuery].
2. Are you sure you use ClientFull as the Control source for [FirstName]?
Feb 15 '07 #38

100+
P: 184
Sorry about that.

Right as follows :

1. SQL of TestQuery = SELECT Clients.FirstName, Clients.LasName, [FirstName] & " " & [LasName] AS ClientFull
FROM Clients;


2. The Control Source for FirstName = FirstName (This is all there is for this textbox)

Fingers crossed. Tell you what, I am dreading this massive project I have to do. It has over 50 tables and many many forms and reports and I have a deadline of only a few months. If I can hardly manage with this one which has only 3 tables and two simple forms!!!
Feb 15 '07 #39

NeoPa
Expert Mod 15k+
P: 31,434
Can you save the form and attach it to this thread.
I'll check it out.
Feb 15 '07 #40

NeoPa
Expert Mod 15k+
P: 31,434
Perhaps I should explain a bit more :
Everything you've told me leads me to believe that all should work. I expect there is something in the design of the form which we're overlooking as we don't know it's a problem. I will upload your form into a test database and create a little 'Clients' table to see what happens.
Feb 15 '07 #41

100+
P: 184
Right I am propably sounding a right numpty right now, but when replying to this for the message there is no option to attach anything. How do I go about it?
Feb 15 '07 #42

NeoPa
Expert Mod 15k+
P: 31,434
No, the attachments are not straightforward and are on the To Do list.
After posting a reply, Edit the reply within the 5 minute window and there is an option there to add an attachment.
Feb 15 '07 #43

100+
P: 184
Right

Lets try.

Here is the screenshot....

Nope. I will have to try something alse cause the maximum attachment size is extremely small.
Feb 16 '07 #44

NeoPa
Expert Mod 15k+
P: 31,434
You should be looking to attach the exported form rather than a screenshot.
Never mind, I just tried it and it doesn't have the full info I was after anyway.

Instead try this :
  1. Create new database.
  2. Using File / Get External Data / Import, Import the form into the new database.
  3. Close the new database.
  4. Zip up the new database.
  5. Attach the Zip file to a post in here.
If you want to, you can include an empty table and any relevant queries in the new database too. That shouldn't blow the size limit.
Feb 16 '07 #45

100+
P: 184
This is doing my head in now. The smallest size I can get it to is 48.9KB, still too large. I will see what else I can try......
Feb 16 '07 #46

NeoPa
Expert Mod 15k+
P: 31,434
One form, compacted and Zipped is nearly 49KB?
What have you got in there? A double-decker bus?
Feb 16 '07 #47

100+
P: 184
Heheheheh. Thanks for that Noepa. That comment cheered up my miserable day man!!!! :-)
Feb 16 '07 #48

100+
P: 184
Right, Think I have done it now and included a bit more than just the form.

Here goes....

I backed it up and then zipped it.
Attached Files
File Type: zip Thescripts2_2007-02-16.zip (24.6 KB, 86 views)
Feb 16 '07 #49

NeoPa
Expert Mod 15k+
P: 31,434
I'm glad I was at least able to cheer you up some even if I haven't found your problem yet.
I'll have to look at this when I get home later.
Feb 16 '07 #50

78 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.