Query to return value not autonumber

[Zydeceltico]

All - I have been putting off asking this question for a long time as I feel really ignorant needing to be reminded - - but hey - - -

So I have a tblWeldAssemblyInspection that has a bunch of fields one of which is "OilCanning." "OilCanning" is a yes/no field.

On this same tbl I have a field called "Workstation." Field "Workstation" is entered on a form via a cbo (cboWorkstations) which gets its data from a second table (tblWorkstations). The fields in tblWorkstation are 1) WS_PK (autonumber) and Workstation (the literal name of any given workstation).

I have a query that returns all records from tblWeldAssemblyInspection where "OilCanning" = "yes." Works great. But how do I get my query to return the name of the workstation and not the autonumbered WS_PK?

Thank you,

Tim

 

[plog]

Via a JOIN. Bring in the table with the name in it, link it appropriately to tblWeldAssemblyInspection and bring down the field you need.

 

[CJ_London]

sounds like you are using lookup fields in your tables - they often cause this sort of confusion. Better to not use them so you can see what you are actually storing. Either way you will still need to follow plog's solution

 

[Zydeceltico]

sounds like you are using lookup fields in your tables - they often cause this sort of confusion. Better to not use them so you can see what you are actually storing. Either way you will still need to follow plog's solution

Thanks. Yeah - I'm reworking the tables and forms now to use Lookup tables from the cbos in the forms - changing column count and column width.

One thing I really don't get though is how I create a JOIN later when I want to query the main table for a condition and have the data value rather than the ID return in the datasheet.

I'm playing around with it atm. I've attached a simple db. There are three tables. tblProdCombos, tblProducts, and tblColor. There is one form (frmProdCombos) and one query (qryProdCombos).

tblProdCombos has three fields: ProdComboID (autonumber), Product_FK, Color_FK. There are no lookups of any kind defined in tblProdCombos.

tblProdCombos is populated using frmProdCombos where the user picks a product and a color using two combo boxes who have their Row Sources set to either tblProducts or tblColor.

Values stored in tblProdCombos are the ID values from each table not the actual product name or color.

qryProdCombos returns a datasheet with the ID values not the product names or selected colors.

I have not defined any relationships or joins either.

How can I have qryProdCombos return product names and colors instead of ID values?

In this test db I have not changed any default column counts or widths.

Thanks in advance!

Tim

 

[plog]

In qryProdCombos bring in tblColor
-click on tblColor and drag it over

Link the tables appropriately
- click on Color_FK and drag it onto ColorID

Bring ColorName into the query
- double click on ColorName

Run you rquery and ColorName now shows up.

I suggest you read up on some basic tutorials (https://support.office.com/en-us/ar...-queries-a9739a09-d3ff-4f36-8ac3-5760249fb65c)

 

[Zydeceltico]

I suggest you read up on some basic tutorials (https://support.office.com/en-us/ar...-queries-a9739a09-d3ff-4f36-8ac3-5760249fb65c)

Thanks Plog - that's exactly what I was looking for and the advice also.

Tim