Master Table that filters data to other tables (1 Viewer)

manc74

New member
Local time
Today, 20:09
Joined
Feb 21, 2018
Messages
2
[SOLVED] Master Table that filters data to other tables

Good morning,

Please find attached my example.

I want to enter a company address/details only once.
One company can have many different roles.

Presently, I can open up tblADDRESS_MATRIX and enter/amend records, changing the checkbox values as I go, and once saved, it populates the relevant queries with the data - which is great.

However, I want to be able to have a one-to-many relationship coming off every single one of those queries, AND enforce referential integrity, cascade update related fields, cascade delete related records - which a query cannot do. So I figured the best way would be to have those queries as tables instead.

I looked at the append query, and whilst it does work, every time you append the data, it deletes the relationship that the table had, which is no good to me.

Any ideas on the best approach to achieve what i need?

Best regards
manc
 

Attachments

  • Test.accdb
    608 KB · Views: 82
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:09
Joined
May 7, 2009
Messages
19,169
you need to separate each address into tables with one main controlling table.
each table must have the id of the controlling table.
 

Attachments

  • Test (2).zip
    53.7 KB · Views: 85

manc74

New member
Local time
Today, 20:09
Joined
Feb 21, 2018
Messages
2
Thanks arnelgp for your response.

Appreciate you taking the time to provide your working example. We can most definitely use this.

Thanks very much.

Best regards
manc
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:09
Joined
May 7, 2009
Messages
19,169
you can add as many addresses as the customer may have.
goodluck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:09
Joined
Feb 19, 2002
Messages
42,973
I took a stab at a normalized schema. The AddressTypes were created from your Y/N checkboxes but they don't actually make sense to me so you might think about what you are trying to do.

Here's a picture of the relationships window.
 

Attachments

  • Schema.JPG
    Schema.JPG
    38 KB · Views: 122
  • Test_Pat.accdb
    784 KB · Views: 79

Users who are viewing this thread

Top Bottom