Reference To Table

[stepanstas]

Hello.

I need help referencing 2 tables. Here is what I am trying to achieve.

Lets say I have one large table with data such as: NY, NJ, PA, CA, WA, IL
Then I have another table that I want to reference, so it would have: NY - East Coast, NJ - East Coast, PA - East Cost, CA - West Cost, WA - West Cost

Notice I do not have IL listed in the reference table. Lets say I only want to point out East and West coast states, any other states that pop up, just say other.

So here is what I am trying to achieve.

Table
NY
NJ
PA
CA
WA
IL

Query
East Coast | NY
East Coast | NY
East Coast | NY
West Coast | CA
West Coast | WA
Other Region | IL

Any help is greatly appreciated.

 

[Pat Hartman]

Since a state only belongs to one region, add region to the state table.

 

[HiTechCoach]

Looks like you need a look up table fo the states that includes a field for region (east coast, west coast, etc). The you can use this table in your query to get the desire info.

 

[stepanstas]

The thing is, I do not want to list all the states. Is there some wildcard I can use in either my table or my query to represent any states not listed.

What if I am only interested in specifing the region for certain states, i do not want to list the region for other states.

Thanks for you help thusfar.

 

[HiTechCoach]

The thing is, I do not want to list all the states. Is there some wildcard I can use in either my table or my query to represent any states not listed.

What if I am only interested in specifing the region for certain states, i do not want to list the region for other states.

Thanks for you help thusfar.

You could list only the states in the lookup table that you want to have a region.

In your query, use a join where it returbs all customers and only include the regions for states that match. For that states that do not match, then region will be Null.

You can use this int he control source of a text box:

=IIF(IsNull([region]),"Other Region ",[region])

 

[stepanstas]

Okay,

I'm not getting the exact result I was looking for.

Can you take a look at the attachement?

I am only getting 1 result as apposed to seeing all.

Thanks.

 

[HiTechCoach]

Sound like the join between the two tables is not correct. Did you change the default join type as noted in my previous post?

To change the join property, right click on the line that joins the two state fields and select "Join Properties". You will need to select Option two or three. My guess is it will be option #2.

The SQL will look something like:

... FROM Customers LEFT JOIN Regions ON Customers.State=Regions.State

 

[boblarson]

Delete the link between the two ID fields. You are trying to link between two primary key fields and you should just be linking between the states

 

[boblarson]

By the way, you would not link between two primary key fields. You would link between a primary key field in one table and the FOREIGN key of another.

 

[stepanstas]

Great, that worked.

Thanks a lot everyone, especially HighTechCoach, the join properties is what i forgot about.

boblarson, I figured there was something wrong with the link between the 2 id's but it happened auto when i added the tables, so just left it.

Thanks again.

 

[boblarson]

boblarson, I figured there was something wrong with the link between the 2 id's but it happened auto when i added the tables, so just left it.

That's what happens when it finds the same name of a field in each table. So, I find it is best to not name a field ID but to use what it actually is - for example:

RegionID in the Regions table

and

CustomerID in the Customers table

and therefore you would naturally see that RegionID would not be linked to CustomerID. If, then you had a table where you linked the two (like CustomerRegions table):

CustomerRegionID - Autonumber (PK)
CustomerID - Long Integer (FK)
RegionID - Long Integer (FK)

etc.

 

[HiTechCoach]

Great, that worked.

Thanks a lot everyone, especially HighTechCoach, the join properties is what i forgot about.

boblarson, I figured there was something wrong with the link between the 2 id's but it happened auto when i added the tables, so just left it.

Thanks again.

You're welcome!

Glad we could assist.

It created the join automatically because the field names matched. Access just made an assumption. It is not always correct as in this case. I have found that it is a good idea to make sure the every primary key field has a unique name across all tables. It will prevent this auto join from happening in the future.

Edit:
Yeah, what Bob said ..... Great explanation!