Related numbers to names in a table or query

[keeling1]

Relating numbers to names in a table or query

I think this problem is easily fixed, but I can't figure out how to do it.

Very briefly, my database is used to help people repair the machines in a factory. The main purpose is to print reports that will tell the technicians which machines need fixing and what's wrong with them. There are hundreds of machines, so they're divided into categories. I have a pair of combo-boxes (the second dependent on the first) set up. The second, dependent one captures the machine type (which was selected in the first combo-box), the machine name, and an ID number for the machine. The bound column is the one with the machine ID number. So the table it's bound to saves only the ID number, not the machine name. (I have it set up like this only because I followed an example I found online. I've tried to set it up without using the ID number at all, but I can't get the dependent combo-box to work any other way.)

Here's the problem: in the report I need the machine name to show up, not the maching ID number. The ID numbers are just randomly assigned, and the technicians don't know which machines they refer to. So when the report is generated, it needs to say what the name of the machine is, not the ID number. Do I need to set up a table that identifies the ID number with the name? Can I do this in the query?

Any help would be appreciated.

 

[pbaldy]

I assume you have a table with the machine ID's and names. You would base your report on a query that joined that table with the main table on the ID field, allowing you to return the name to the report.

 

[keeling1]

Okay, in the query, I joined the ID field on the main table with the ID field on the tblMachine table, which has columns for the ID number and the Machine name. I thought that what I'd need to do then is to add a field in the query for Machine Name from the tblMachine table. But that's giving me a "Type mismatch in expression" error when I open the report, and all the control sources in the report have error messages.

As you can see, I don't really know what I'm doing here. But your help is proving invaluable.

 

[pbaldy]

Fair warning; I've had a plate of pasta and a half a bottle of wine now, so anything I say should be carefully examined.

First thing is, does the query properly return the data it should? Once we get that sorted out, we can make sure the report reflects the query. If you're getting a type mismatch from the query, make sure the ID field has the same data type in both tables (if it's an autonumber in one, it should be a long integer in the other).