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.

[NeoPa]

There's probably a small problem somewhere that you're overlooking.
Once that's found I expect it will all fit nicely into place for you :)

[MMcCarthy]

Right I have tried somthing different now.

I have changed the tables as follows :

Clients (Nothing changed)
Treatments (TreatmentID, Treatment)
Cost(CostID,Cos t)
Client Treatments(Clie ntID,TreatmentI D,CostID,Treatm entDate)

The idea for the last table is to be used for my form where I add treatments to existing customers. Only problem is that when I base my form on this table, the dropdown fileds only shows the ID's which is not user friendly. I cannot find a way for it to show things like Treatment,Cost.

This is propably no the right way of doing it......

In the table design go to these fields and go down to the properties. The column widths should be set to 0cm;4cm. I am just using 4 as an example width here. The point is that setting the first column (the ID column) to 0 will hide it and you will now be able to see the second column.

Mary

[NeoPa]

Hi there Neopa

No worries, I have been thrashing away at this problem. Close to sitting in a corner somewhere and cry the rest of my living years away, but still having a go though. Is access just not particularly user friendly or is it just me....

I've now looked at your db.
I can't find a problem though. Can you describe to me briefly and clearly what you are getting (in which form) that you would not expect or do not want.
Maybe test out the copy of the db you posted too, rather than only the original. That way we know we're both looking at the same thing.

[NeoPa]

BTW I'm going to be away for most of the weekend (In that hideous place called - Cut Off From The Web). Just so you know if you don't manage to reply before Friday evening (GMT).

[bloukopkoggelmander]

Hi there Neopa

No worries man. I wont have much time to respond either, Time to give some attention to the family. It might be best if we pick this back up again next week then.

Have a nice weekend.

[NeoPa]

;)
Catch up next week then.

[bloukopkoggelmander]

Hi there Neopa

Applogies for my late reply , but I have had all sorts of problems over the last couple of days.

Right I am including a slightly ammended database, which I have changed slightly by adding a extra table and some forms.

Form one, NL New Client Form works as it should as I am able to add new Clients and it updates the table accordingly.

Form two, NL New Client Treatments works as it should as I am able to add new Treatments and it updates the table accordingly

Form Three, NL Costs works as it should as I am able to add new Costs and it updates the table accordingly.

The problem I am having here is with form Four, NL ClientTreatment s. All I want to see on this form is a dropdown list with the Client's First and last name concatenated, A dropdown list with a choise of treatments, a dropdown list with a list of costs to select and then a Date selection.

When making my selections I then want to save the contents of this form to table NL NewClientTreatm ents which only lists the id fields for username,treatm ent,cost and a date field. The problem is that I cannot get the form to present the user friendly information like a name and a treatment and then just save everything as ID'S.

Hope this makes sense.

Thanks

[NeoPa]

Now you're going off at a tangent (don't worry about the posting delay btw :))
Going off on a different track while half-way through a problem is guaranteed to confuse one of us if not both. Trust me this is complicated enough dealing with just one thing at a time without branching out before an issue is resolved.
Until I get answers to my last set of questions (post #63) I don't even know where we are on the original point.

[bloukopkoggelmander]

Hi Neopa

Sorry man, did not mean to confuse you. The database I attached is still the original one I sent you. All I did was add a table which contains, ClientID, TreatmentID,Cos tID,ClientTreat mentID,Treaymen tDate and the form to go with it. I thought that this could make it easier.

As all the other tables would contain the data to be looked up, this table would combine it all to show which client has wich treatment at which cost on which date.

Should I rather go back and use the old one instead? Cause I really did not mean to confuse you man. Only asking for some help and trying to figure it out as well.

[NeoPa]

I'm sure you were just trying to be as helpful as possible and we may well get to that stage later.
However, it's quite difficult to work on a problem remotely and one of the (if not the) most important thing to remember is to keep synchronised. In this case, for me to catch up with you I still need answers to post #63 as I loaded up your database copy and couldn't find anything that didn't work.
Now if we go off elsewhere, looking for problems in the database when the problem actually turns out to be with your PC or your settings for instance, then we could get all confused. In a way, you need to let me drive, which can be disconcerting, but without that control of the flow it is too confusing for me, as I have to rely on (almost) all of my information second-hand.
Does this make sense?