Query for Values

[srpatel]

Hello,

I am hoping for someone to help me out with a query I need to run on my MDB.


I have one master table that collects all the information called ALLBS. There are other relevant tables that the ALLBS relates to get information from.

Currently when I export data from the ALLBS table, to excel it carrys forward the numerical values that represent the record from another table. I think an example would explain this best

There are quite a few fields that are in the ALLBS so I will simply use the ones which are giving me a problem

ALLBS:
BSID PK
EAssetType number
AssetLocationType
ParapetType number
LocationID number
LineID number



Related tables:

tblAssetLocType
AssetLocID PK
AssetLocType Text

tblLine
LineID PK
Line Text

tblLocations
LocationID PK
LineID Number
LocationType text
LCS text
LCSDesc text
AssetLocationTypeID num

AssetLocationTypeID relating to tblAssetLocType>AssetLocType.

tblParapetType
ParapetID PK
ParapetType Text

In my ALLBS table this is how the data gets stored:

BSID[tab]EllipseAssetType[tab]AssetLoctype[tab]Direction[tab]ParapetType[tab]LocationID[tab]Line
1[tab][tab]FootBridge[tab][tab][tab][tab][tab]-[tab][tab][tab][tab]NA[tab][tab][tab]3[tab][tab][tab]00023456[tab][tab][tab]2

The values 3 under parapet type represents "STEEL" from tblParapetType and value 2 under Line represents "CENTRAL" from tblLine.

[attn]Attention! 1[/attn]
I need these numerical values replaced when i run a query so i can export the data. How can i achieve this?

Your help would be greatly appreciated.

Thanks
Shreekant
Have an awesome day!

 

[JerryKlmns]

Create a query that joins all PKs to FKs and get the description you need and not the PK no.

 

[srpatel]

Hi Jerry,

Abit new to access, could you give me an example please?

thanks

 

[JerryKlmns]

SELECT A.BSID
A.EAssetType
L.Line
FROM ALLBS As A INNER JOIN tblLine As L On
A.LineID = L.LineID;

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886