Table Linking and Queries??

[Scott Viney]

Afternoon All,

I have created a table for states of a country. To me this table is like a
store for information. I wont be updated by user interaction.
StateID StateName StateTelephone Code
1 Cundinamarca 1
2 Cordoba 914

I have one table for clients and in this table I have StateID, I also have
one table for Offices which I have added StateID as well.

In my relationships table how do I link them. Do I add one States table and
then link to both the clients and offices table with referential integrity?
Or do I just ignore referential integrity?

Which leads onto the next question I have a query that brings all the tables
to create my report. How do I have to link them in the query? Or should it
be automatic because of the relationships I have already setup?

In my report I want to show the telephone codes which relate to the state
for the office and the client which could be different. But I keep getting
errors on the report.

I thought this was the best way of doing this as I wouldnt duplicate the
data for the phone code in multiple tables, but in the end I cant make it
work.

What am I doing wrong?
Scott V

 

[Vincent Johns]

Afternoon All,

I have created a table for states of a country. To me this table is like a
store for information. I wont be updated by user interaction.
StateID StateName StateTelephone Code
1 Cundinamarca 1
2 Cordoba 914

I have one table for clients and in this table I have StateID, I also have
one table for Offices which I have added StateID as well.

In my relationships table how do I link them. Do I add one States table and
then link to both the clients and offices table with referential integrity?
Or do I just ignore referential integrity?

It should be no problem. (I assume that the [StateID] values are
unique!) ;-)

You can invoke the same Table twice (or more) in Query Design View or in
the Relationships window; Access assigns a unique name to each extra
use, so you might have 2 references, to [State] and to [State_1]. One
you'd link to [Clients] and one to [Offices].

Which leads onto the next question I have a query that brings all the tables
to create my report. How do I have to link them in the query? Or should it
be automatic because of the relationships I have already setup?

In Query Design View, you can override any relationships that Access
assigns automatically due to definitions in the Relationships window.

In my report I want to show the telephone codes which relate to the state
for the office and the client which could be different. But I keep getting
errors on the report.

Does your underlying Query for the Report, the Report's data source,
work properly? If not, try to debug the Query first.

I thought this was the best way of doing this as I wouldnt duplicate the
data for the phone code in multiple tables, but in the end I cant make it
work.

What am I doing wrong?
Scott V

Yes, it's good to avoid duplicating data (usually). What you describe
here doesn't seem to call for any duplication. You might want to post
your SQL, and someone here might be able to suggest changes.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.