Looking Up Info From Another Table

[wazawak]

I am attempting to show the status of an order in my report (confirmed, work in progress, waiting on finance, etc). These "status" entries are read off tblOrdersDetails, where they are listed by their StatusID- a serial number from 1 to 7. This serial number is linked to the actual status (confirmed, work in progress, waiting on finance, etc) on a separate table.

My attempts to show this status on a report with combo boxes and list boxes have resulted in the StatusID serial number being displayed. How can I display the text status (confirmed yadda yadda) instead?

 

[pbaldy]

Typically the report would be based on a query that joined the 2 tables on the ID field, enabling you to include the text field on the report.

 

[wazawak]

I was hoping I wouldn't have to muck about with queries... I don't have much experience working with queries, so I am not sure how I would begin. Could you walk me through a simple example or link me to a resource where I could find this out? I have made a few blind attempts to build a query that would link these but I run into numerous errors and end up unable to save it.

 

[pbaldy]

You will not get far without queries, so you may as well get your hands dirty now. Run the query wizard and choose your 2 tables when prompted. Depending on your setup, you may already see a line between the StatusID fields in the 2 tables. If not, click and drag between them to create one. Double click on the fields you want the report to show, and they should appear below in the grid. Run the query and see what you get.

 

[wazawak]

I have created a query, but am unsure what to do with it. So far, it seems little has changed. I imagine the next step would be to link something in the report to the query, but as I said before, I'm still a nublet when it comes to this.

Attached (SS1) is a screenshot of the design view of the query I created. Does this look appropriate? I linked the "Status" (name) field in the tblStatus (which has both the AutoNumber serial and the name field) to the "Status" field in tblOrdersDetails (which is referencing the autonumber.)

The other attachment (SS2) is a screenshot of the report. As you can see, the "Status" column is still no more than a list of the serial IDs. It is that field that I am trying to get to display something intelligible.

 

[pbaldy]

The query looks correct offhand. You'd want that report textbox bound to the status_status field, not the status field. Is it?

 

[wazawak]

The query looks correct offhand. You'd want that report textbox bound to the status_status field, not the status field. Is it?

Do you mean a status_status field in the query?

If you mean a status_status field in the status table, that field is is always set, ie 1 -> Awaiting Customer Approval, 2-> Awaiting Finance approval, 7-> WIP, etc.

 

[pbaldy]

Sorry, I missed that you'd responded. In your query, you have an aliased field called "status_status" that looks to be the text value from that table. It's that field that you want the textbox bound to.

 

[wazawak]

Thanks very much for all your help! Unfortunately, priorities have shifted at work and this has been moved to the back burner for now. I will get back on this as soon as I can.

 

[Fr33dan]

Actually you don't need to mess with queries anyway. The DLookUp function is designed to do just this without the need for a query. Try setting the Control Source property of your textbox to

=DLookUp("[Status]","Status","[ID] = " & [Status])