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]

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

[bloukopkoggelmander]

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.

[NeoPa]

Try this ...

Full Name: (SELECT [ClientFirst] & " " & [ClientLast] FROM Clients WHERE ClientID=[Client Treatments].ClientID)
 

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.

[NeoPa]

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 :

SELECT [ClientFirst],[ClientLast],
       [ClientFirst] & " " & [ClientLast] AS ClientFull
FROM Clients

See what this gives you. It will tell you if the & (Concatenation) works.

[bloukopkoggelmander]

Thanks Neopa

I will give that a go and let you know.

Thanks

[bloukopkoggelmander]

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

[NeoPa]

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.

[bloukopkoggelmander]

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)

[NeoPa]

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.

[NeoPa]

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.