Re: SQL help

Traditionally, an application like this, doesn't UPDATE into a summary
file. First the summary file is cleared and then INSERTed with the new
data. Like:

Now, before you start playing around with your INSERT, you should work out
your select. You said you wanted all the fields listed, no problem.
However, you wanted it GROUPed by transaction type. That could be a
problem. For example if you have multiple product types per paytype
you're going to have an issue.

One basic thing to remember is that all columns selected in a GROUP BY
have to:
A - Be in the GROUP BY statement, or,
B - Have some sort of summary function performed on them like: MAX, MIN,
SUM, etc

I am going to make a real broad assumption and assume that your sort is by
the order of the fields you have presented with a break for every PmtType.

First, see if this SELECT works.
select
PayDate,
Store,
Mfg,
PdType,
PmtType,
SUM(AmtPaid) as AmountPaid

From SalesDetail

Where PayDate = '2007/12/31'
Group by Store, Mfg, PdType, PmtType
Order by Store, Mfg, PdType, PmtType

If that works for you, and my assumption was not off, then it's a simple
matter to do

DELETE FROM SalesSum

INSERT INTO SalesSum (
select
PayDate,
Store,
Mfg,
PdType,
PmtType,
SUM(AmtPaid) as AmountPaid

From SalesDetail

Where PayDate = '2007/12/31'
Group by Store, Mfg, PdType, PmtType
Order by Store, Mfg, PdType, PmtType
)

Rob Berendt