Show name instead of ID (1 Viewer)

[Ironis]

Hi

I have the following query:

Select tblReservation.date, tblReservation.programcode_ID, Sum(tblReservation.number_pers) AS [Total pp Program]
FROM tblReservation
GROUP BY tbrReservation.date, tblReservation.programcode_ID

It works perfectly. It shows the date of the reservation, the programID and the total # of persons who are in all reservations.

Now there is one problem, I want it to show not the programcode_ID, but the program_name form tblPrograms

How can I fix this? I tried several things but it didn't work

I'm using Access 2000

Ironis

 

[Rob.Mills]

I've solved this challenge using a couple solutions. The one I prefer is using a combobox. Reason being, if you're using this on a continuous form with many records it's faster. The other is a DLookUp.

In the combobox set the recordsource to have the first column be the ID, the 2nd column contain the name. Then make sure it's set to display two columns.

Set the column widths to -- 0";1" or whatever you want the width to be on the 2nd.

Then set the controlsource to the ID.

I think that's all the details you need.

 

[Pat Hartman]

Open the query in QBE view and add tblPrograms. If you have previously defined a relationship between these two tables, Access will automatically draw a join line. If the join line does not automatically appear, you will have to make one. Click on the programcode_ID in one table and drag it to the cooresponding column of the other table. Then select Program_name from tblPrograms.

Joining two tables is as simple as that. Now whenever you run the query, you will also see the program name column.