Linked Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BChat - 21 Jan 2008 13:57 GMT

Using Access 2007. I have a DB with 2 tables, 1st field in each table is
"Code", a numeric field. I enter data in table 1, which consists of 3
fields - Code - Date - Amount. Table 2 has Code - Supplier fields. I have a
report, based on a query, that shows the Date - Supplier - Amount fields.
The 2 Code fields are joined by a Relationship in the query.

I would like to set up a rule, if that is what it is called, so that I
cannot enter a Code number in Table 1 unless that Code number exists in
Table 2. It seems to me I did this many years ago in a database far away,
but cannot remember the process I used.

Thanks,
BChat

Reply to this Message

Allen Browne - 21 Jan 2008 14:58 GMT

Not sure that structure really makes sense, but if you have some reason to
go that way, just open the Relationships window (on the Database Tools tab
of the ribbon), and create a relation between your 2 tables based on the
Code field.

Be sure to get the relation the right way around (Table1 is the primary
table?), and to check the box for Referential integrity.

More info in this old article:
Referential Integrity can't be that easy!
at:
http://allenbrowne.com/xbase-07.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Using Access 2007. I have a DB with 2 tables, 1st field in each table is
> "Code", a numeric field. I enter data in table 1, which consists of 3
[quoted text clipped - 10 lines]
> Thanks,
> BChat

Reply to this Message

BChat - 21 Jan 2008 15:34 GMT

Allen,

Thanks, that did what I wanted. I had the wrong table as primary. It has
been a long time since I have done any work w/Access and I am trying to get
back into it. As I learn, I'm sure my structures will improve - I
hope.......;-)
Thanks again,
BChat

Not sure that structure really makes sense, but if you have some reason to
go that way, just open the Relationships window (on the Database Tools tab
of the ribbon), and create a relation between your 2 tables based on the
Code field.

Be sure to get the relation the right way around (Table1 is the primary
table?), and to check the box for Referential integrity.

More info in this old article:
Referential Integrity can't be that easy!
at:
http://allenbrowne.com/xbase-07.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Using Access 2007. I have a DB with 2 tables, 1st field in each table is
> "Code", a numeric field. I enter data in table 1, which consists of 3
[quoted text clipped - 10 lines]
> Thanks,
> BChat

Reply to this Message