Merging 2 tables with different column names

[pdbowling]

I have 2 tables
One with these columns

DeptCode, TruckN, PMC, RepNormC, RepABNormC, SFDPC

First, I need to merge RepNormC and SFDPC leaving only RepNormC. They are both currency fields and I need to add them together.

The other table has columns

PO_NO, FLEET_NO, PM_AMOUNT, NORMAL_AMOUNT, ABNORMAL_AMOUNT

The tables are related like this.
Dept_Code = PO_NO
TruckN = FLEET_NO
PMC = PM_AMOUNT
RepNormC = NORMAL_AMOUNT
REPABNormC = ABNORMAL_AMOUNT

I need to bring the tables together into one table and add the currencies (fields 3, 4, & 5) together if TruckN = FLEET_NO.

To me, this is a highly complicated query and I can't imagine where to begin. I'm still working on understanding joins.... If anyone could give me a suggestion that'd be GREAT!..... Thanks everyone.
PB

 

[neileg]

If you want to permanently merge to a table, then

Create a query like this in the QBE Grid based on your first table
PO_NO: [DeptCode], FLEET_NO:[TruckN], PM_AMOUNT:[PMC], NORMAL_AMOUNT: Nz([RepNormC])+Nz([SFPDC]), ABNORMAL_AMOUNT: [RepABNormC]

Then make this an append query and add these records to your second table.

Run a totals query on the resulting merged table that is based on this:
FLEET_NO,TotalAmount: Nz([PM_AMOUNT])+Nz([NORMAL_AMOUNT])+Nz([ ABNORMAL_AMOUNT ])
with a total in the Total Amount field

 

[FoFa]

Keep it simple. Create a new table with the columns you wish. Than write multiple queries to put the data into that table. Than you can do what you want with it.

 

[The_Doc_Man]

Or, if the tables for some reason really NEED to stay separate, look up the Help topic UNION Queries

This is yet another way to "skin the cat."