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_i d_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,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......

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