Access query with table reference

[ChipT]

I have two tables. One with Customers down the side, and billcodes across the top. The table is populated by billcode quantities.

The second has table has two columns: Billcode and Rate. The table maps billcodes to their rates.

I want to construct a query that will multiply each billcode quantity by its rate to get revenue. How might this be constructed in Access?

Visually, here are the two tables:

Table 1:
| BC1 | BC2 |
CustA | 5 | 10 |
CustB | 3 | 8 |

Table 2:
Billcode | Rate |
BC1 | $1 |
BC2 | $2 |

Desired result:
| BC1 | BC2 |
CustA | 5 * $1 | 10 * $2 |
CustB | 3 * $1 | 8 * $2 |

 

[OBP]

ChipT, welcome to the Forum.
What you have described is what you do.
Create a Query for table 1 with the Customers and Bill codes.
Add table 2.
Add a new column, in the heading put the following
resultBC1: tablename1.[BC1] * tablename2.[BC1]
Add a new column, in the heading put the following
resultBC2: tablename1.[BC2] * tablename2.[BC2]

where tablename is the actual names of the 2 tables.

This Table structure is OK for data entry but far from ideal design wise. If you have to add a BC3 later you have to add fields to tables, queries, forms & reports.