Totals Issue

[SamDev]

I have created an invoice - that is a form - part of the form that records
all the transactions is a subform and the total of all transactions is
calcualated using the amounts of each transactions. I have another form that
records payments made to each invoice. The payments go against the total of
the invoice. I have then created a query that displays outstanding
payments/receivables. Problem is when there is more than one transaction on
a single invoice, the query (and subsequent reports) will display, for
invoices that are completely paid but with more than one tranaction line,
2nd, 3rd line etc transactions as unpaid, therefore, in corresponding
reports it looks like there is more outstanding than there actually exists -
it also uses these figures in my totals that I have created for total
outstanding.

In my multi-table query, I have included invoice #, amount, amount paid,
customer name, billing date and have created an expression that calculates
outstanding (amount - amount paid) with a criteria for the the outstanding
expression as <>0. It works great except for invoices that have more than
one transaction.

Any help would be appreciated.

Much thanks.

 

[Steve Schapel]

SamDev,

Could you post back with the SQL view of the query? Thanks.

 

[SamDev]

Steve:

This is the SQL view of the query - much thanks! The "Load Transaction
Table" is where the each "purchase" gets stored.

SELECT [Customer Table].[Customer Name], [Invoice Transaction
Table].[Billing Date], [Invoice Transaction Table].[Invoice Number], [Load
Transaction Table].[Amount Paid], [Load Transaction Table].Destination,
[Invoice Transaction Table].[Customer PO], [Load Transaction Table].[Payment
Date], [amount]-[amount paid] AS OS, [Customer Table].[Phone Number],
[Invoice Transaction Table].[Load Number], [Load Transaction Table].Amount,
Sum([Load Transaction Table].Amount) AS SumOfAmount
FROM ([Customer Table] INNER JOIN [Invoice Transaction Table] ON [Customer
Table].[Customer ID] = [Invoice Transaction Table].[Client ID]) INNER JOIN
[Load Transaction Table] ON [Invoice Transaction Table].[Invoice Number] =
[Load Transaction Table].Invoice
GROUP BY [Customer Table].[Customer Name], [Invoice Transaction
Table].[Billing Date], [Invoice Transaction Table].[Invoice Number], [Load
Transaction Table].[Amount Paid], [Load Transaction Table].Destination,
[Invoice Transaction Table].[Customer PO], [Load Transaction Table].[Payment
Date], [amount]-[amount paid], [Customer Table].[Phone Number], [Invoice
Transaction Table].[Load Number], [Load Transaction Table].Amount
HAVING ((([amount]-[amount paid])<>0));

 

[Steve Schapel]

Sam,

You have got Group By applying to fields that it shouldn't be applying
to. In particular, you are Grouping By [Load Transaction
Table].[Amount] and at the same time trying to Sum this field (which in
fact appears to be the main purpose of the Totals Query). Therefore,
just remove the column from the quwery design grisd where you have got
Group By for the Amount field. There are probably other fields in the
query as well, that it is either unnecessary or incorrect to be
including in the Group By. See if you can tidy it up, and post back if
you need further help.

 

[SamDev]

Thanks - I think I have it working now --- needed "fresh eyes" on it - much
thanks!

Sam,

You have got Group By applying to fields that it shouldn't be applying to.
In particular, you are Grouping By [Load Transaction Table].[Amount] and
at the same time trying to Sum this field (which in fact appears to be the
main purpose of the Totals Query). Therefore, just remove the column from
the quwery design grisd where you have got Group By for the Amount field.
There are probably other fields in the query as well, that it is either
unnecessary or incorrect to be including in the Group By. See if you can
tidy it up, and post back if you need further help.

--
Steve Schapel, Microsoft Access MVP

Steve:

This is the SQL view of the query - much thanks! The "Load Transaction
Table" is where the each "purchase" gets stored.

SELECT [Customer Table].[Customer Name], [Invoice Transaction
Table].[Billing Date], [Invoice Transaction Table].[Invoice Number],
[Load Transaction Table].[Amount Paid], [Load Transaction
Table].Destination, [Invoice Transaction Table].[Customer PO], [Load
Transaction Table].[Payment Date], [amount]-[amount paid] AS OS,
[Customer Table].[Phone Number], [Invoice Transaction Table].[Load
Number], [Load Transaction Table].Amount, Sum([Load Transaction
Table].Amount) AS SumOfAmount
FROM ([Customer Table] INNER JOIN [Invoice Transaction Table] ON
[Customer Table].[Customer ID] = [Invoice Transaction Table].[Client ID])
INNER JOIN [Load Transaction Table] ON [Invoice Transaction
Table].[Invoice Number] = [Load Transaction Table].Invoice
GROUP BY [Customer Table].[Customer Name], [Invoice Transaction
Table].[Billing Date], [Invoice Transaction Table].[Invoice Number],
[Load Transaction Table].[Amount Paid], [Load Transaction
Table].Destination, [Invoice Transaction Table].[Customer PO], [Load
Transaction Table].[Payment Date], [amount]-[amount paid], [Customer
Table].[Phone Number], [Invoice Transaction Table].[Load Number], [Load
Transaction Table].Amount
HAVING ((([amount]-[amount paid])<>0));