Make Table Query

[funkmonsteruk]

I'm building a make table query which uses 4 fields <Date> <Name> <Credit> <Debit>. During the make table process i need to summ all of the <credit>'s for each <Name> sum all the <debit>'s for each <name> and subtract sum of <debit>'s from sum of <credit>'s.

This should leave me with a final table which gives me <Name> <date> and <Balance>. How do i achieve this? I'm guessing i'll need to use an expression to do this, but have no idea what to do....Please help

 

[gnt]

Hello-
You're going to have to first run a query to create your original table, I'll call it tblCredit. Then something like this should work...

SELECT * INTO tblBalance
FROM (SELECT Name, Sum(CreditTtl-DebitTtl) as Balance
FROM (SELECT Name, Sum(Credit) as CreditTtl, Sum(Debit) as DebitTtl
FROM tblCredit
GROUP BY Name)
GROUP BY Name);

...where tblBalance is your new table with 2 columns, Name and Balance.

I'm assuming that there is more than one date per person in the original table?? If so, you can't include the date in the tblBalance because it's grouped by Name.

You could always run a drop table query to get rid of your original table.

Let me know if that's close to what you're looking for.
-gnt