Table relationships

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

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

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

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)

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.

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

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 ;)

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.

Create a query as follows:

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

Thank you Mr. Mccarthy

I will give it a go and let you know if I have any problems.!!

Thank you Mr. Mccarthy

You don't want to confuse Mary with Paul ;)

Oh damn! Sorry man, did not mean to offend anyone.

If I got it wrong then please accept my appologies.

You don't want to confuse Mary with Paul ;)

Different spelling in the surname.

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

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 :(

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.

A great attitude. I wish all our members were as easy to deal with. Let us know anytime you need more help.

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.

Try this ...

Mary

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....:-)

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 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.

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 :
See what this gives you. It will tell you if the & (Concatenation) works.

Thanks Neopa

I will give that a go and let you know.

Thanks

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.....

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.

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)

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.

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.

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

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)

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).

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

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.

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 :(.

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.

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]?

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!!!

Can you save the form and attach it to this thread.
I'll check it out.

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.

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?

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.

Right

Lets try.

Here is the screenshot....

Nope. I will have to try something alse cause the maximum attachment size is extremely small.

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.

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......

One form, compacted and Zipped is nearly 49KB?
What have you got in there? A double-decker bus?

Heheheheh. Thanks for that Noepa. That comment cheered up my miserable day man!!!! :-)

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.

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.