Compare previous year count for same previous sum amount

[smithn293]

I have data in 2 tables like this:

Table2010
ShipmentID (unique, PK)
ShipMonth
ShipYear
Weight

Table2011
ShipmentID (unique, PK)
ShipMonth
ShipYear
Weight


Each record represents a shipment. I am trying to do the following:

For the same total weight in a given calendar month, I want to compare the number of shipments (number of records) in 2011 that resulted for the same total weight for the previous year's month.

For example:

Table2010
ShipID DateMonth DateYear Weight (lb)
100 01 2010 200
101 01 2010 200
102 01 2010 200

Table2011
ShipID DateMonth DateYear Weight (lb)
100 01 2011 300
101 01 2011 350

The resulting record should look like:

DateMonth PrevYearSummedWeight ShipCount2010 ShipCount 2011
01 600 3 2


I've tried a ton of ways to include a where clause subquery to compare summed amounts, but nothing is working.

Any help is greatly appreciated!

Thanks.

 

[james_lankford]

this might help get you started, but I'm sure you'll have to change some stuff

make a query

SELECT 
  Table2010.ShipMonth, 
  count(*) AS NumberItems, 
  sum(Table2010.Weight) AS TotalWeight
FROM 
  Table2010
GROUP BY 
  Table2010.ShipMonth;

save as select_from_2010

make another query

SELECT 
  Table2011.ShipMonth, 
  count(*) AS NumberItems, 
  sum(Table2011.Weight) AS TotalWeight
FROM 
  Table2011
GROUP BY 
  Table2011.ShipMonth;

save as select_from_2011


make another query

SELECT 
  select_from_2010.ShipMonth, 
  select_from_2010.TotalWeight, 
  select_from_2010.NumberItems, 
  select_from_2011.NumberItems
FROM 
  select_from_2010 
    INNER JOIN 
      select_from_2011 
        ON 
          select_from_2010.ShipMonth = select_from_2011.ShipMonth
WHERE 
(
  (
    select_from_2010.TotalWeight <= [select_from_2011].[TotalWeight]
  )
);

save as compare_2010_2011