very simple query not working.... (1 Viewer)

we have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...

now for the query:
i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design:
this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...

i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work...

You need to include the units lookup table in your query. Draw the proper join line if Access doesn't draw it automatically. Select the text field from the lookup table.

Amt: Maintable.[amount]&Space(1)&LookupTable.[unitsName]

A better naming scheme helps to avoid confusion regarding lookups. A "good" structure for the units lookup table would be:

tblUnitType
UnitTypeID (autonumber primary key)
UnitTypeName (text name such as gallon, ounce, etc.)

Then in your maintable, the foreign key name should be UnitTypeID rather than units. It will be immediately clear when you look at it that the field refers to the primary key field rather than the text value.

i tried that and it is still giving me a type mismatch. here's my query, if need be i can post the DB (although i'll be out of town until monday, so it'll have to wait until then )

Take a look at how tblConUnits.ConUnitsID and tblWasteTracker.ConUnits are defined. tblConUnits.ConUnitsID which sounds like it is the primary key of the table, is probably defined as an autonumber. If that is true, then tblWasteTracker.ConUnits must be defined as long integer.

You also need to go to the relationships window and define a relationship between these tables and enforce referential integrity. Do not select the cascade delete or update options.

forgot to post a reply...thanks!! that's exactly what it was...i had an autonmumber for the PK and as text in the other table...changed that to long integer and all is well...