MS Access Query Issues With Sum() Function and Group By SQL

[G2]

Hi,

I'm not great at building SQL from scratch so I've been constructing
and then piece-meal editing the sql using the query designer.
Basically what I am trying to accomplish is linking an imported table
to the rest of the tables that already exist within the database. The
end result is that I need an aggregate sum of [Collected Premium] by
Year, then, month, then producer, then Customer, then product.

For example:
2009, Jul, Producer A, Customer A, Product STD, Premium Collected
2009, Jul, Producer A, Customer A, Product LTD, Premium Collected

The above would be simple since there are unique rows. However, things
can never be easy and the imported file often looks like this because
the premiums were received at different times throughout the month:
2009, Jul, Producer A, Customer A, Product STD, Premium Collected1
2009, Jul, Producer A, Customer A, Product STD, Premium Collected2
2009, Jul, Producer A, Customer A, Product LTD, Premium Collected1
2009, Jul, Producer A, Customer A, Product LTD, Premium Collected2

So, I figured I need to aggregate each combination as described above
to get back to:
2009, Jul, Producer A, Customer A, Product STD, Aggregate STD Premium
Collected
2009, Jul, Producer A, Customer A, Product LTD, Aggreagte LTD Premium
Collected

The following is my SQL statement:
rawCollectedFile is the imported file
tblCombinedFile is the table where the output will be dumped

SELECT rawCollectedFile_2009Q1.DisYr AS [Year],
rawCollectedFile_2009Q1.Period AS [Month], tblProducer.ProducerEIN,
tblProducer.ProducerName, rawCollectedFile_2009Q1.newDivSerial AS
CustDivSerial, tblCust.CustName, tblProductConversions.Product,
tblTier.TierPercentage, tblLineOfBusiness.Split AS [Broker Split],
tblLineOfBusiness.custSplit, tblLineOfBusiness.LOB_EffDate, Sum
(rawCollectedFile_2009Q1.[Collected Premium]) AS [SumOfCollected
Premium]

INTO tblCombinedFile

FROM tblTier INNER JOIN ((tblPlanYearTier INNER JOIN (tblProducer
INNER JOIN tblProducerPlanYearTier ON tblProducer.ProducerEIN =
tblProducerPlanYearTier.ProducerEIN) ON tblPlanYearTier.PlanYearID =
tblProducerPlanYearTier.PlanYearId) INNER JOIN
(((rawCollectedFile_2009Q1 INNER JOIN tblProductConversions ON
rawCollectedFile_2009Q1.Product =
tblProductConversions.CollectedFileProduct) INNER JOIN tblCust ON
rawCollectedFile_2009Q1.newDivSerial = tblCust.CustDivSerial) INNER
JOIN tblLineOfBusiness ON tblCust.CustDivSerial =
tblLineOfBusiness.CustDivSerial) ON
tblProducerPlanYearTier.ProducerPlanYearTierID =
tblLineOfBusiness.ProducerPlanYearTierID) ON tblTier.TierID =
tblPlanYearTier.TierID

GROUP BY rawCollectedFile_2009Q1.DisYr,
rawCollectedFile_2009Q1.Period, tblProducer.ProducerEIN,
tblProducer.ProducerName, rawCollectedFile_2009Q1.newDivSerial,
tblCust.CustName, tblProductConversions.Product,
tblTier.TierPercentage, tblLineOfBusiness.Split,
tblLineOfBusiness.custSplit, tblLineOfBusiness.LOB_EffDate;

I get a nice output with all the right columns and everything matched
corrected, except the aggregate premium is whack. I'm bet it's
something simple in the joins or grouping that is screwing it up. Any
help would be greatly appreciated!

Thanks!
thegetch1

 

[G2]

I should also note that what the aggregate premium column seems to be
returning appears to be correct, but at the year level, not at the
individual product level. For instance:

2009, Aug, Producer A, Customer A, STD, Premium = $300
2009, Aug, Producer A, Customer A, LTD, Premium = $400

and then...
2009, Sep, Producer A, Customer A, STD, Premium = $300
2009, Sep, Producer A, Customer A, LTD, Premium = $400

Notice how the premium amounts are the same, which leads me to believe
the order of the groupings is off somehow, but can't put my finger on
it.

Thanks!