Combining data if ?????

[pdbowling]

Hi all,
I have two tables from two vendors.

Vendor1
DeptCode......TruckN.....PMC.....NormC.....AbnormC
F4000............F100.......$100.....$500.......$10
IA702.............F110.......$100.....$500.......$10
MA702...........T150.......$100......$500.......$10
etc

Vendor2
DeptCode......TruckN.....PM.......Norm.......Ab
IW000............F100.......$100.....$500.......$10
IA702.............F110.......$100.....$500.......$10
MA702............T150......$100......$500.......$10
etc

Normally this wouldn't be hard but...

I need to combine the PMC with PM, NormC with Norm, and AbnormC with Ab IF......

The deptcode AND the truck number are the same.
If the truck numbers are the same and the department code is different, I need 2 seperate listings (table below). I'm kind of lost on where to begin with a query for this.

DeptCode......TruckN......PM.......Norm........Ab
F4000.............F100.......$100.....$500........$10
IW000............F100.......$100.....$500........$10
IA702.............F110.......$200.....$1000.......$20
MA702............T150.......$200.....$1000.......$20
etc
Thanks all.
PB

 

[FoFa]

How about something like:
select Deptcode, TruckN, PMC as P1, NormC as N1, AbnormC as A1 from Vendor1
UNION ALL
select Deptcode, TruckN, PM, Norm, Ab from Vendor2

Call this query Query1

Select DeptCode, TruckN, sum(P1), sum(N1), sum(A1) from Query1 Group By DeptCode, TruckN

Bascially, you will have to add anything else you need.

 

[pdbowling]

hmm

That is fabulous. Thank you. The only problem is...
I don't quite see what the query is doing. Any clues you can offer.

How is

Select DeptCode, TruckN, sum(P1), sum(N1), sum(A1) from Query1 Group By DeptCode, TruckN

only telling it to combine when they deptcode and truckn are equal.. I only see 'group by' as an organization of the data already being added and not a qualifier. What am I missing? Sorry to be green about all of this.

PB

 

[FoFa]

The UNION ALL query takes your 2 tables and puts all the data in one. (Think of it as combining the tables). You need UNION ALL to return all rows, where a UNION (without the ALL) would delete duplicate data, which you do not want. The second query uses the first query as input and adds all the like data rows together (from the group by) and would give you what your final example shows. I do not understand the "I need 2 seperate listings", so if you expand on that it may be more clear.