Need Join Help?

[katyshuler]

I must be having a brain fart, but I can't figure out how to do this. First off, I'm not very good at Access, but very good at SQL.

Table: DATABASE
Fields: Type1, Type2, Type3, Type4, Type5 (only Type1 is required, others are mostly blank)
...Joined to...
Table: WORKDESCRIPTION
Fields: CMCS_Code

Each record in WORKDESCRIPTION has a cost.

I'm creating an invoice (using a query which joins the two above tables) and want to generate a cost for each value in Types 1-5, but I can only get it to pull the first cost. I'm assuming I'm going to need to run a CAST AS statement, but for the life of me I can't figure out how to write it. Any help would be greatly appreciated.

[pbaldy]

If you're saying the WORKDESCRIPTION table has to join to each of the Type fields (not a normalized design by the way), you'd join 2-5 to aliased copies of the table. You might prefer creating a UNION query to "normalize" the first table, then your join is a simple one.

[katyshuler]

A UNION query is only valid if the table has all the same fields. Let me explain it differently.

ORDER table has a spot for 5 items.
ITEMS table has all items listed and their price.

I need ORDER.ITEMID to link to ITEMS.ID to pull the price of each item, so I can display it on a report (invoice).

[pbaldy]

I meant to UNION the order table to normalize it, then a regular query joining that to the items table. Or leave it like it is and use aliases of the items table.