multiple tables

[davehappen]

Hi all, I'm not a novice as such but have forgotten most of what i used to know (EDCL:Advanced)
I have 5 tables all linked by a membership number, 3 of the tables carry some of the same information, is it possible to enter this info into one table and have it carry across to the other tables.
TIA


Dave

[Welshgasman]

You do not 'carry across' data. You put it in one place and refer to it.?
So information should not be duplicated.?

Even in Excel you would not duplicate the data, but use a V or H Lookup

[ssanfu]

Out of curiosity,
If 3 of the tables are identical in structure, why have 3 identical tables?

[CarlettoFed]

Maybe if you attach a file it is easier to understand and therefore give you some help.

[davehappen]

sorry for the long delay, been away from the computer,
welshgasman, so i should have one table with all the information on it???
ssanfu, the tables are not identical but carry some of the same data
carlettofed, heres the file https://1drv.ms/u/s!Ar_qzWsihagxhb9t...n8rdw?e=ogUTSe
TIA

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications.

[davegri]

Here's how I would start:

dhap-davegri-v01.zip

[Welshgasman]

[QUOTE=davehappen;471020
welshgasman, so i should have one table with all the information on it???
TIA[/QUOTE]

Not what I was saying?
You should not duplicate data in different tables for the most part.
An exception might be an invoice where price is copied over to show the data at that instance of time, as if the price goes up, then referring to it later, would give an incorrect value.
In your case I would have a Member table and everywhere that the member needs to be linked, I would put the MemberID ?

[ssanfu]

I'm with davegri on the table designs/relationships, so I'll back out.

[davehappen]

Many thanks everybody that took time to help, davegri thanks for the design, 100% better than i could come up with. sorry about not getting back sooner but i'm not getting any notifications, any way one last thing, i understand PK on the relationships table but not FK????

[davegri]

Many thanks everybody that took time to help, davegri thanks for the design, 100% better than i could come up with. sorry about not getting back sooner but i'm not getting any notifications, any way one last thing, i understand PK on the relationships table but not FK????

FK suffix is Foreign Key.

Lets take an example.
The PK fields are all autonumbers, assigned automatically as each record is created.
Lets say that Elmer Smith is a Member with Member_PK = 5.
Looking at the Next of kin table, Elmer could have one or more records there. All of Elmer's NOK records need to know who they belong to (Elmer). That is accomplished by the Member_FK values in the NOK table being 5, to match Elmer's PK of 5.

If you need help setting up your forms let me know.
I would suggest a main form with the membershiip table as the recordsource.

Then a tab control on the main form with 2 tabs, each containing a subform; one for NOK and one for Service Details.
This setup will automatically assign the proper FK to both the NOK and ServiceDetails records.
Combo boxes on the subforms will allow selection of service branch and relation type.

[davehappen]

Hi davegri, if you could that would be great, maybe I'm getting a bit out of my depth here but i just can't grasp how this works, plus i'll be passing this on to someone who has less experience than myself (disaster looming).

[davegri]

OK, here's the DB file:

dhap-davegri-v02.zip

Edit: As an afterthought, I added error handlers to all the VBA procedures. V3 is functionally exactly the same as V2, just with error handling.

dhap-davegri-v03.zip

[davehappen]

davegri, that is brilliant, many many thanks, i can work with that no problem.
Again thanks
Dave

[davehappen]

davegri, that is brilliant, many many thanks, i can work with that no problem.<br>Again thanks<br>Dave