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.

[bloukopkoggelmander]

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

[bloukopkoggelmander]

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)

[NeoPa]

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

[bloukopkoggelmander]

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.FirstNa me,Clients.Last Name,Clients.Cl ientFull(Client Full:[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,Last Name,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,LastN ame,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

[bloukopkoggelmander]

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

[NeoPa]

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

[bloukopkoggelmander]

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.

[NeoPa]

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

[bloukopkoggelmander]

Sorry about that.

Right as follows :

1. SQL of TestQuery = SELECT Clients.FirstNa me, 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!!!

[NeoPa]

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