One table holds the value the other holds the text... 1

[ToshTrent]

Hi I have several tables in my Access Database.

One table holds the Status information,

1. Pending
2. Setup
3. Awaiting Information

The second form holds the other information

PromotionID
PromotionName
PromotionDetails
PromotionStatus

on the second form (tblPromotions) It holds the data like,

PromotionID: (Autonumber)
PromotionName: (Text)
PromotionDetails: (Text)
PromotionStatus: (Text) - Either 1, 2 or 3 is entered in this field.

I then would like the query that i have setup to show the text value of that field, so instead of showing 1, I would like Pending to show, 2 = Active and so on.

Is there away to do this?

[red]Many Thanks[/red]
Matt

 

[TonyJollans]

Hi ToshTrent,

Yes, you can do it. The SQL for it will be ..

SELECT tblPromotions.PromotionID, 
       tblPromotions.PromotionName, 
       tblPromotions.PromotionDetails,
       tblStatus.StatusText,
FROM   tblPromotions 
  LEFT JOIN tblStatus 
  ON tblPromotions.PromotionStatus = tblStatus.StatusID;

You can build it quite easily in the Query Builder. Come back if you need help with this.

Enjoy,
Tony

 

[ToshTrent]

Thats done the trick, thank you ever so much!

So can't this option be done in the design screen aposed to the SQL screen?



[red]
Thankyou[/red]
Matt

 

[TonyJollans]

Hi ToshTrent,

Yes it can be done in the design screen; it's hard to describe in words alone, but I'll try.

Open up a new Query in Design view - the "Show Table" dialog should be visible.
Double click on the first Table (tblPromotions).
Double click on the second Table (tblStatus).
Close the Show Table Dialog.
Click and Drag PromotionStatus from tblPromotions.
Drop it on StatusID in tblStatus.
This will give you a line between the two fields.
Right click on this line - you should get a menu with two options (Join properties and Delete).
(If your mouse position is not quite right you'll get a bigger more general menu - reposition and try again).
Select Join Properties - exactly what you see depends on your version of Access.
Select option 2, Include all records from 'tblPromotions' and only those records from 'tblStatus' where the joined fields are equal
Press OK.
Choose the fields you want in your Query result as normal.

Enjoy,
Tony

 

[ToshTrent]

Thank you very much Tony! you've been a great help.

Kindest Regards.
Tosh