Hi All
I have a table called Office that is shared between four other tables. The four other tables have unique details in them and cannot be shared.I have four relationships my problem is that every time the office table need to be updated I have to do this four times.
There must be an easier way to do this.

Thanks


Brendan Bartley

__________________
Brendan Bartley

What do you mean by:

"I have a table called Office that is shared between four other tables"?

I have the office called office 1 2 3 and 4 and these are linked Separately to four other tables
Thanks

Brendan Bartley

Sorry, just to be clear:

You have a table, called Office, which has four fields in it, called:

Office1
Office2
Office3
Office4

You then have a situation when you want to change the value in these four fields for a specific record?

No
I have four tables called office1 office2 etc

Brendan Bartley

Sorry, now I am totally lost. A telephone at this point would be handy! But it did just push me over 100 posts, so not all bad!

Right, so:
You have 4 tables (Office1, Office2, Office3, Office4)

Question:
and you have a table called Office, too?

Question:
After a change to the Office table, you want to make an update to the 4 tables?
Are the 4 tables copies of the Office table?

I sense there is a solution to the problem (and, don't take this the wrong way, I think it is related to your design), I just don't understand your design or the problem very well.

No sorry
I have only four tables called Office1 Office2 etc no there is no table called office. All the details in these tables are the same. I want to know instead of having these four tables I want to have just one is this possible.
Thanks

Brendan Bartley

Yeah, just have one table and use it like a reference table... assuming they all have the same contents!

When you use the table in a query, simply add them into your query... Query design will give each new table an alias (Office_1, Office_2, etc usually), you might want to give them more suitable ones, you should access the table property in your query design to do this, or in SQL view, use something like:

FROM Office as [Office1]
================================

So if you had a tblData with two fields (FieldA and FieldB) which both referenced the OfficeID of the table Office, in order to return the OfficeData field from the Office table, you would end up with SQL like:

SELECT Office1.OfficeData, Office2.OfficeData
FROM Office AS Office1 INNER JOIN (tblData INNER JOIN Office AS Office2 ON tblData.FIELDA = Office2.OfficeID) ON Office1.OfficeID = tblData.FIELDB