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]

I'm afraid that the file reported as 'Unrecognised Database Format' when I tried to open it with Acc2003 at home :(

[bloukopkoggelmander]

Ah that's cause I am using 2007. I will see if I can save it in an earlier version and post it again.

Thanks

[bloukopkoggelmander]

Try this one.....

[sohel]

hi,
to join two table in oracle you must have a primary foreign key relationship between the two table. to join 'n' number of tables you must have 'n-1' join condition. so to join 2 tables u need one join condition. the two table you specify is very simple.

Table 1(Clients) :

ClientID
Firtsname
Lastname
HouseNumber
Streetname
Town
Postcode
HomePhone
MobileNumber
Email

Tabel 2(Treatments) :

TreatmentID
Treatment
Cost
Date

create table table1(
ClientID number(6),
Firtsname varchar2(12),
Lastname varchar2(12),
HouseNumber varchar2(12),
Streetname varchar2(12),
Town varchar2(12),
Postcode number(6),
HomePhone number(6),
MobileNumber number(10),
Email varchar2(16));

first decide a column which will uniquely identify your table1. make it primary key.


1 alter table table1
2* add constraint client_id_pk primary key(clientid)
SQL> /

Table altered.

create table table2(
Treatment_ID varchar2(15),
Treatment varchar2(30),
Cost number(10,2),
req_date date);


1 alter table table2
2* add clientid number(6)
SQL> /

Table altered.

SQL> alter table table2
2 add constraints table2_client_id_fk foreign key(clientid)
3 references table1(clientid);

Table altered.

now put your data first in table2 i.e the table contain foreign key.then enterd the value in table1. in any case u will not find any clientid in table1 which does not entered in table1. for furthe references go to
www.oravcle.com
by

[NeoPa]

Try this one.....

I'll look at that when I can (at home).
In the mean-time I'll try to delete your ealier attachment as unrequired and taking up space.

[NeoPa]

hi,
Various Oracle related stuff

Did you mean to post that in this thread?
I don't believe this question is related to Oracle.
I really don't mean to put you off from posting, but maybe you were trying to add it to a different thread?
Would you like me to delete it for you? Just let me know.

[bloukopkoggelmander]

Hi there Sohel,

Sorry buddy, I am not using Oracle.

[bloukopkoggelmander]

Right I have tried somthing different now.

I have changed the tables as follows :

Clients (Nothing changed)
Treatments (TreatmentID, Treatment)
Cost(CostID,Cost)
Client Treatments(ClientID,TreatmentID,CostID,TreatmentDa te)

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

[NeoPa]

I'm sorry, I seem to have let this one slip off my radar and haven't got around to looking at it yet. I'm out most of this evening so I can't promise anything but will try this evening or tomorrow. If you hear nothing by then bump it up again as I don't want to leave this one unresolved after all the work already gone into it.

[bloukopkoggelmander]

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

[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,Cost)
Client Treatments(ClientID,TreatmentID,CostID,TreatmentDa te)

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 ClientTreatments. 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 NewClientTreatments which only lists the id fields for username,treatment,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,CostID,ClientTreatmentID,TreaymentDate 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?

[bloukopkoggelmander]

Yip no problem. I will hold myself back and let you get on . I will go back to the post in question and get you all the details you asked for. Wont be long....

[bloukopkoggelmander]

Right I had a look at that datbase again. Must say the bit I emailed you does not really have much on it. But from that, the problems are as follows:

When using TestFrom to select Client , Treatment,Cost and Date, upon saving a new entry, the selected date is not entered into the Treatments table. It is just blank in the table.

The second thing is that I cannot see anywhere in the tables how a treatment, cost or date is related to a specific client( One client can have multiple treatments) after making a entry on the TestForm.

This would of course be a problem when it comes to creating reports.

The form to add a new client works fine, no problem with that one.

[NeoPa]

The database was actually posted to explain how you couldn't get the FullName made up from the FirstName & LastName fields to work.
When I opened up the database I found that they did all work (as far as I could see but there was more than one form in it so I wasn't sure exactly what you wanted me to look at).
Am I to understand now that this is no longer a problem. It's what I've been focusing on the whole way through (As I mentioned before I never like to build on sand so I can't move on from one point unless it's resolved otherwise confusion surely follows). Help me to understand what the current status is and how we got there.

[bloukopkoggelmander]

Hi Neopa

You are right, it is working now!! Dont know what I did to make it work, but when I tested it before I sent it through to you, I could not get it working. Well, I am not going to mess around with it just in case it pops again. So yes I think that issue is solved then.

But I tried to use this function, but could not get any treatment,cost or date bound to this concatenated name. I dont know how to link this to it's corresponding ClientID.

Do you prefer me close this thread now that the main issue seems to be solved and start a new one for the other issue relating to this as above?

[NeoPa]

That's up to you at this stage.
Normally I would say yes, but I'm happy to continue guiding you onwards in here if that's preferable for you.
You already know how demanding I am about clear communication so I'll leave it up to you ;)

[bloukopkoggelmander]

Neopa

I do agree with your principles on posting, so what I will do is close this post as the main issue has been solved ie. Concatination of first and last name.

What I will do though is post a new thread, but do what I tried in the latter part of this post( before I got told off :-) ) and post you the database I have updated and am now at a point where I am stuck.

Does that sound ok? That way we start a new thread with a new issue.....

[bloukopkoggelmander]

Hi NeoPa

Her is the link to the new thread :

Filling table from form using only ID'S

[NeoPa]

Neopa

I do agree with your principles on posting, so what I will do is close this post as the main issue has been solved ie. Concatination of first and last name.

What I will do though is post a new thread, but do what I tried in the latter part of this post( before I got told off :-) ) and post you the database I have updated and am now at a point where I am stuck.

Does that sound ok? That way we start a new thread with a new issue.....

That sounds eminently sensible my friend.
As a favour (so I can catch up with it before I would otherwise get up that far), can you post a link to the new thread here when you've set it up.

[NeoPa]

Hi NeoPa

Here is the link to the new thread :

Filling table from form using only ID'S

OK, so I didn't refresh my browser (again) before posting.
Seems you read my mind anyway :D