Help with query

[graeme34 via AccessMonster.com]

Hi could somebody help me out adapting the following query.....

I currently have a statement report that has a subreport of all linked to the
main report by account name....
The subreport has a detail line for each Invoice sent through the month...as
the enterprise rules are payment 30 days from end of month.... there can be
three months of invoices to the customer in the report....also in the detail
section are control boxes showing any payments received for those invoices
with the can shrink property set to yes...since bringing the payment details
into the report my footer section has hit a snag...I orginally planned a
sub/sub report in the footer showing the total invoiced amount for each month
with the control source query as such:

SELECT DISTINCT qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm")
AS MonthName, Sum(qryStatementLine.Debit) AS SumOfDebit, DatePart("m",
[InvoiceDate]) AS MonthNumber
FROM qryStatementLine
GROUP BY qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm"),
DatePart("m",[InvoiceDate])
ORDER BY qryStatementLine.AccountIndex, DatePart("m",[InvoiceDate]) DESC;

Which as you can see Selects and groups the account index and sums all debits
in each month.

This worked fine(ish)
Ideally I would have liked the report to also show the month if the customer
hadnt ordered anything. ie.

March £543.04
February £ 0.00
January £182.45

Whereas currently it is only showing March and January....I not too sure this
is a query problem as there is nothing to select....I'm thinking more along
the line of conditions built into the report format....but not sure how to
got about this...thats one problem.

My main problem is when I have tried to bring payment details into the query,
if a customer has made more than one payment in that month then I am getting
(n) number of records for each month sum, here is the adapted query:

SELECT DISTINCT qryMonthSalesCust.AccountIndex, qryMonthSalesCust.MonthName,
qryMonthSalesCust.SumOfDebit, qryMonthSalesCust.MonthNumber, tblTransaction.
TransCredit
FROM tblTransaction INNER JOIN ((tblSalesOrder INNER JOIN qryMonthSalesCust
ON tblSalesOrder.AccountIndex = qryMonthSalesCust.AccountIndex) INNER JOIN
(tblDespatch INNER JOIN tblSalesInvoice ON tblDespatch.DespatchNumber =
tblSalesInvoice.[Despatch Number]) ON tblSalesOrder.SalesOrderNumber =
tblDespatch.SalesOrderNumber) ON tblTransaction.TransactionNumber =
tblSalesInvoice.TransactionNumber
ORDER BY qryMonthSalesCust.AccountIndex, qryMonthSalesCust.MonthNumber DESC;

Where TransCredit is the amount of any payments made..
But the results are nothing like I had planned...although I can see why it
showing them...
just dont know how to rectify it

 

[Vincent Johns]

This would be easier to answer if you didn't omit some important
details... such as the definitions (SQL will do) of the Queries you
refer to, and sample records from the Tables. Missing Queries,
apparently, are [qryMonthSalesCust] and maybe [qryStatementLine];
missing Tables include [tblDespatch], [tblSalesInvoice],
[tblSalesOrder], and [tblTransaction]. Since none of these Tables
includes any fields identifying a date, I think you can't expect to be
able to display meaningful totals grouped by date (regardless of how
fancy your Queries might be -- if the information is missing, you can't
just conjure it up).

In addition to the sample records I mentioned in each of the Tables used
by your Queries, I suggest that you also show an example datasheet that
shows the results you'd like your Query to display, based on those
sample records. (You'll probably have to generate this manually, since
the Query that you would need to use to generate it automatically is the
one you're seeking help on.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi could somebody help me out adapting the following query.....

I currently have a statement report that has a subreport of all linked to the
main report by account name....
The subreport has a detail line for each Invoice sent through the month...as
the enterprise rules are payment 30 days from end of month.... there can be
three months of invoices to the customer in the report....also in the detail
section are control boxes showing any payments received for those invoices
with the can shrink property set to yes...since bringing the payment details
into the report my footer section has hit a snag...I orginally planned a
sub/sub report in the footer showing the total invoiced amount for each month
with the control source query as such:

SELECT DISTINCT qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm")
AS MonthName, Sum(qryStatementLine.Debit) AS SumOfDebit, DatePart("m",
[InvoiceDate]) AS MonthNumber
FROM qryStatementLine
GROUP BY qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm"),
DatePart("m",[InvoiceDate])
ORDER BY qryStatementLine.AccountIndex, DatePart("m",[InvoiceDate]) DESC;

Which as you can see Selects and groups the account index and sums all debits
in each month.

This worked fine(ish)
Ideally I would have liked the report to also show the month if the customer
hadnt ordered anything. ie.

March £543.04
February £ 0.00
January £182.45

Whereas currently it is only showing March and January....I not too sure this
is a query problem as there is nothing to select....I'm thinking more along
the line of conditions built into the report format....but not sure how to
got about this...thats one problem.

My main problem is when I have tried to bring payment details into the query,
if a customer has made more than one payment in that month then I am getting
(n) number of records for each month sum, here is the adapted query:

SELECT DISTINCT qryMonthSalesCust.AccountIndex, qryMonthSalesCust.MonthName,
qryMonthSalesCust.SumOfDebit, qryMonthSalesCust.MonthNumber, tblTransaction.
TransCredit
FROM tblTransaction INNER JOIN ((tblSalesOrder INNER JOIN qryMonthSalesCust
ON tblSalesOrder.AccountIndex = qryMonthSalesCust.AccountIndex) INNER JOIN
(tblDespatch INNER JOIN tblSalesInvoice ON tblDespatch.DespatchNumber =
tblSalesInvoice.[Despatch Number]) ON tblSalesOrder.SalesOrderNumber =
tblDespatch.SalesOrderNumber) ON tblTransaction.TransactionNumber =
tblSalesInvoice.TransactionNumber
ORDER BY qryMonthSalesCust.AccountIndex, qryMonthSalesCust.MonthNumber DESC;

Where TransCredit is the amount of any payments made..
But the results are nothing like I had planned...although I can see why it
showing them...
just dont know how to rectify it

 

[graeme34 via AccessMonster.com]

Hi Vincent,
Thanks for your reply....
Missing tables/queries here goes....
qryMonthSalesCust was the original query posted =

SELECT DISTINCT qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm")
AS MonthName, Sum(qryStatementLine.Debit) AS SumOfDebit, DatePart("m",
[InvoiceDate]) AS MonthNumber
FROM qryStatementLine
GROUP BY qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm"),
DatePart("m",[InvoiceDate])
ORDER BY qryStatementLine.AccountIndex, DatePart("m",[InvoiceDate]) DESC;

qryStatementLine =

SELECT DISTINCT A.AccountIndex, A.AccountName, SO.CustomerOrderNumber, SI.
InvoiceDate, SI.SalesInvoiceNumber, SI.TotalNett, V.VATValue, CCur([TotalNett]
*(([VatValue]/100)+1)) AS Debit, T.TypeOfPosting, IIf([TypeOfPosting]
="Customer Payment",[TransCredit],0) AS IFFCredit, T.DateOfPosting, [Debit]-
[IFFCredit] AS ItemBalance, SI.SalesInvoicePaid
FROM tblVAT V INNER JOIN ((tblAccount A INNER JOIN tblSalesOrder SO ON A.
AccountIndex = SO.AccountIndex) INNER JOIN ((tblDespatch D INNER JOIN
tblSalesOrderLine SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER
JOIN (tblTransaction T RIGHT JOIN tblSalesInvoice SI ON T.TransactionNumber =
SI.TransactionNumber) ON D.DespatchNumber = SI.[Despatch Number]) ON (SO.
SalesOrderNumber = SOL.SalesOrderNumber) AND (SO.SalesOrderNumber = D.
SalesOrderNumber)) ON V.VATRate = SOL.VATRate
ORDER BY A.AccountIndex, SI.SalesInvoiceNumber;

tblDespatch = DespatchNumber (PK), SalesOrderNumber (FK for tblSalesOrder),
DateOfDespatch, (there are three other fields althought these are only
'flags' for coding)

tblSalesInvoice = SalesInvoiceNumber (PK), DespatchNumber (FK for Despatch),
InvoiceDate, TotalNett, CarriageAmount, TotalIncVAT (I know I shouldnt store
calculated fields, but storing it really does make the query building easier),
TransactionNumber (FK for tblTransaction, no referential integrity as a
transaction may not be for an Invoice), SalesInvoicePaid (yes/no).

tblSalesOrder = SalesOrderNumber(PK), AccountIndex (FK), DateOfOrder,
CustomerOrderNumber, DeliveryMode

tblTransaction = TransactionNumber (PK), NominalIndex (FK for Nominal tbl),
DateOfPosting, TypeOfPosting, TransCredit, TransDebit, TransactionDetails

The data I would like to view is:

A total for all Invoices for each month for each customer, qryMonthSalesCust
is currently doing this, what I would like is to bring in to the query is a
total of all payments by customer for each month, payments will be
TransCredit and criteria for filtering for only customer payments from the
tblTransaction TypeOfPosting will be 'Customer Payment'.
Then I would like a total for the month, Total Invoices - SumofCredits for
that month.

As I'm typing this I'm guessing Im going to need a new query for the total of
payments, then join the two queries together and use the above sum in an
expression, rather than including the 'SELECT's' in the first
qryMonthSalesCust.

Also is there a way I can bring into the query all the MonthName regardless
of if they have been Invoiced or made a payment for that month. This is only
for 'cosmetic' purposes for my report, as stated in my first post....I would
like to use the query as a sub/sub report for the footer section displaying
monthly oustanding amouunts for the last three months, then anything over
that I would like 'summed' and shown as one total such as overdue.

This would be easier to answer if you didn't omit some important
details... such as the definitions (SQL will do) of the Queries you
refer to, and sample records from the Tables. Missing Queries,
apparently, are [qryMonthSalesCust] and maybe [qryStatementLine];
missing Tables include [tblDespatch], [tblSalesInvoice],
[tblSalesOrder], and [tblTransaction]. Since none of these Tables
includes any fields identifying a date, I think you can't expect to be
able to display meaningful totals grouped by date (regardless of how
fancy your Queries might be -- if the information is missing, you can't
just conjure it up).

In addition to the sample records I mentioned in each of the Tables used
by your Queries, I suggest that you also show an example datasheet that
shows the results you'd like your Query to display, based on those
sample records. (You'll probably have to generate this manually, since
the Query that you would need to use to generate it automatically is the
one you're seeking help on.)

Hi could somebody help me out adapting the following query.....

[quoted text clipped - 52 lines]

showing them...
just dont know how to rectify it

 

[graeme34 via AccessMonster.com]

Just a quick update....
Ive managed to write the query to summ the payments (sort of)....

SELECT SO.AccountIndex, T.TypeOfPosting, Sum(T.TransCredit) AS
SumOfTransCredit, Format([DateOfPosting],"mmmm/yy") AS MonthPaid
FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T
INNER JOIN tblSalesInvoice SI ON T.TransactionNumber = SI.TransactionNumber)
ON D.DespatchNumber = SI.[Despatch Number]) ON SO.SalesOrderNumber = D.
SalesOrderNumber
GROUP BY SO.AccountIndex, T.TypeOfPosting, Format([DateOfPosting],"mmmm/yy");

Why I am saying sort of is because a payment can have many invoices,
therefore if a customer pays (n) invoices on one payment, then the query is
grouping together the (n)occurances of that payment....leading to (n) times
the original payment when summing...

Hi Vincent,
Thanks for your reply....
Missing tables/queries here goes....
qryMonthSalesCust was the original query posted =

SELECT DISTINCT qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm")
AS MonthName, Sum(qryStatementLine.Debit) AS SumOfDebit, DatePart("m",
[InvoiceDate]) AS MonthNumber
FROM qryStatementLine
GROUP BY qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm"),
DatePart("m",[InvoiceDate])
ORDER BY qryStatementLine.AccountIndex, DatePart("m",[InvoiceDate]) DESC;

qryStatementLine =

SELECT DISTINCT A.AccountIndex, A.AccountName, SO.CustomerOrderNumber, SI.
InvoiceDate, SI.SalesInvoiceNumber, SI.TotalNett, V.VATValue, CCur([TotalNett]
*(([VatValue]/100)+1)) AS Debit, T.TypeOfPosting, IIf([TypeOfPosting]
="Customer Payment",[TransCredit],0) AS IFFCredit, T.DateOfPosting, [Debit]-
[IFFCredit] AS ItemBalance, SI.SalesInvoicePaid
FROM tblVAT V INNER JOIN ((tblAccount A INNER JOIN tblSalesOrder SO ON A.
AccountIndex = SO.AccountIndex) INNER JOIN ((tblDespatch D INNER JOIN
tblSalesOrderLine SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER
JOIN (tblTransaction T RIGHT JOIN tblSalesInvoice SI ON T.TransactionNumber =
SI.TransactionNumber) ON D.DespatchNumber = SI.[Despatch Number]) ON (SO.
SalesOrderNumber = SOL.SalesOrderNumber) AND (SO.SalesOrderNumber = D.
SalesOrderNumber)) ON V.VATRate = SOL.VATRate
ORDER BY A.AccountIndex, SI.SalesInvoiceNumber;

tblDespatch = DespatchNumber (PK), SalesOrderNumber (FK for tblSalesOrder),
DateOfDespatch, (there are three other fields althought these are only
'flags' for coding)

tblSalesInvoice = SalesInvoiceNumber (PK), DespatchNumber (FK for Despatch),
InvoiceDate, TotalNett, CarriageAmount, TotalIncVAT (I know I shouldnt store
calculated fields, but storing it really does make the query building easier),
TransactionNumber (FK for tblTransaction, no referential integrity as a
transaction may not be for an Invoice), SalesInvoicePaid (yes/no).

tblSalesOrder = SalesOrderNumber(PK), AccountIndex (FK), DateOfOrder,
CustomerOrderNumber, DeliveryMode

tblTransaction = TransactionNumber (PK), NominalIndex (FK for Nominal tbl),
DateOfPosting, TypeOfPosting, TransCredit, TransDebit, TransactionDetails

The data I would like to view is:

A total for all Invoices for each month for each customer, qryMonthSalesCust
is currently doing this, what I would like is to bring in to the query is a
total of all payments by customer for each month, payments will be
TransCredit and criteria for filtering for only customer payments from the
tblTransaction TypeOfPosting will be 'Customer Payment'.
Then I would like a total for the month, Total Invoices - SumofCredits for
that month.

As I'm typing this I'm guessing Im going to need a new query for the total of
payments, then join the two queries together and use the above sum in an
expression, rather than including the 'SELECT's' in the first
qryMonthSalesCust.

Also is there a way I can bring into the query all the MonthName regardless
of if they have been Invoiced or made a payment for that month. This is only
for 'cosmetic' purposes for my report, as stated in my first post....I would
like to use the query as a sub/sub report for the footer section displaying
monthly oustanding amouunts for the last three months, then anything over
that I would like 'summed' and shown as one total such as overdue.

This would be easier to answer if you didn't omit some important
details... such as the definitions (SQL will do) of the Queries you

[quoted text clipped - 22 lines]

 

[Vincent Johns]

OK, I've incorporated your new Queries into my version of your database,
as well as three additional Tables ([tblAccount], [tblSalesOrderLine],
and [tblVAT]) that you hadn't mentioned. But it would also help if you
could post sample records from all of your Tables, maybe a couple of
records from each -- enough to illustrate what is happening in your
Queries. Given enough time, perhaps I could puzzle out all the details,
but my answer will mean more to you if the data look realistic. (A
short field name doesn't always convey an accurate idea of what exactly
is supposed to be inside that field, so example values can help
considerably.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Just a quick update....
Ive managed to write the query to summ the payments (sort of)....

SELECT SO.AccountIndex, T.TypeOfPosting, Sum(T.TransCredit) AS
SumOfTransCredit, Format([DateOfPosting],"mmmm/yy") AS MonthPaid
FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T
INNER JOIN tblSalesInvoice SI ON T.TransactionNumber = SI.TransactionNumber)
ON D.DespatchNumber = SI.[Despatch Number]) ON SO.SalesOrderNumber = D.
SalesOrderNumber
GROUP BY SO.AccountIndex, T.TypeOfPosting, Format([DateOfPosting],"mmmm/yy");

Why I am saying sort of is because a payment can have many invoices,
therefore if a customer pays (n) invoices on one payment, then the query is
grouping together the (n)occurances of that payment....leading to (n) times
the original payment when summing...

Hi Vincent,
Thanks for your reply....
Missing tables/queries here goes....
qryMonthSalesCust was the original query posted =

SELECT DISTINCT qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm")
AS MonthName, Sum(qryStatementLine.Debit) AS SumOfDebit, DatePart("m",
[InvoiceDate]) AS MonthNumber

FROM qryStatementLine

GROUP BY qryStatementLine.AccountIndex, Format([InvoiceDate],"mmmm"),
DatePart("m",[InvoiceDate])
ORDER BY qryStatementLine.AccountIndex, DatePart("m",[InvoiceDate]) DESC;

qryStatementLine =

SELECT DISTINCT A.AccountIndex, A.AccountName, SO.CustomerOrderNumber, SI.
InvoiceDate, SI.SalesInvoiceNumber, SI.TotalNett, V.VATValue, CCur([TotalNett]
*(([VatValue]/100)+1)) AS Debit, T.TypeOfPosting, IIf([TypeOfPosting]
="Customer Payment",[TransCredit],0) AS IFFCredit, T.DateOfPosting, [Debit]-
[IFFCredit] AS ItemBalance, SI.SalesInvoicePaid

FROM tblVAT V INNER JOIN ((tblAccount A INNER JOIN tblSalesOrder SO ON A.

AccountIndex = SO.AccountIndex) INNER JOIN ((tblDespatch D INNER JOIN
tblSalesOrderLine SOL ON D.SalesOrderNumber = SOL.SalesOrderNumber) INNER
JOIN (tblTransaction T RIGHT JOIN tblSalesInvoice SI ON T.TransactionNumber =
SI.TransactionNumber) ON D.DespatchNumber = SI.[Despatch Number]) ON (SO.
SalesOrderNumber = SOL.SalesOrderNumber) AND (SO.SalesOrderNumber = D.
SalesOrderNumber)) ON V.VATRate = SOL.VATRate
ORDER BY A.AccountIndex, SI.SalesInvoiceNumber;

tblDespatch = DespatchNumber (PK), SalesOrderNumber (FK for tblSalesOrder),
DateOfDespatch, (there are three other fields althought these are only
'flags' for coding)

tblSalesInvoice = SalesInvoiceNumber (PK), DespatchNumber (FK for Despatch),
InvoiceDate, TotalNett, CarriageAmount, TotalIncVAT (I know I shouldnt store
calculated fields, but storing it really does make the query building easier),
TransactionNumber (FK for tblTransaction, no referential integrity as a
transaction may not be for an Invoice), SalesInvoicePaid (yes/no).

tblSalesOrder = SalesOrderNumber(PK), AccountIndex (FK), DateOfOrder,
CustomerOrderNumber, DeliveryMode

tblTransaction = TransactionNumber (PK), NominalIndex (FK for Nominal tbl),
DateOfPosting, TypeOfPosting, TransCredit, TransDebit, TransactionDetails

The data I would like to view is:

A total for all Invoices for each month for each customer, qryMonthSalesCust
is currently doing this, what I would like is to bring in to the query is a
total of all payments by customer for each month, payments will be
TransCredit and criteria for filtering for only customer payments from the
tblTransaction TypeOfPosting will be 'Customer Payment'.
Then I would like a total for the month, Total Invoices - SumofCredits for
that month.

As I'm typing this I'm guessing Im going to need a new query for the total of
payments, then join the two queries together and use the above sum in an
expression, rather than including the 'SELECT's' in the first
qryMonthSalesCust.

Also is there a way I can bring into the query all the MonthName regardless
of if they have been Invoiced or made a payment for that month. This is only
for 'cosmetic' purposes for my report, as stated in my first post....I would
like to use the query as a sub/sub report for the footer section displaying
monthly oustanding amouunts for the last three months, then anything over
that I would like 'summed' and shown as one total such as overdue.

This would be easier to answer if you didn't omit some important
details... such as the definitions (SQL will do) of the Queries you

[quoted text clipped - 22 lines]

showing them...
just dont know how to rectify it

 

[graeme34 via AccessMonster.com]

hi Vincent

Like I said I think I need two seperate queries, one showing all the total
sum of all invoices for each Customer(Account) and another showing all the
payments made by the customer. Then join them by Account Index and use an
expression in the summary subtracting payments from the invoice total....
Structure of tables as follows:
tblAccount:
AccountIndex (PK), Customer (yes/no) Supplier (yes/no), AccountName, Address
Fields...(1,2,3 etc), Town, TelNum, Faxnum,PostCode, CurrentOwed (If supplier)
, CurrentBalance(if Customer) DateAccountOpened

tblSalesOrderLine
SalesOrderNumber(PK), ProductCode(PK), QuantityOrdered, Price, VATRate (FK)...
..plus a few yes/no flags for coding

tblVAT
VATRate (PK), VATValue

Where VATRate is a code for the amount of Value Added Tax i.e 17.5%

I currently have the query for summing the total amount of Invoices each
month for each Customer(Account).....I now need the query for summing the
total of payments, part of Transaction table i.e the TypeOfPosting field can
either be Customer Payment or Pay Supplier
here is what I have so far...
SELECT SO.AccountIndex, T.TypeOfPosting, Sum(T.TransCredit) AS
SumOfTransCredit, Format([DateOfPosting],"mmmm/yy") AS MonthPaid
FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T
INNER JOIN tblSalesInvoice SI ON T.TransactionNumber = SI.TransactionNumber)
ON D.DespatchNumber = SI.[Despatch Number]) ON SO.SalesOrderNumber = D.
SalesOrderNumber
GROUP BY SO.AccountIndex, T.TypeOfPosting, Format([DateOfPosting],"mmmm/yy");

The problem here is a Transaction (payment) can be for more than one invoice..
..I.e all Invoices are paid in one Transaction....or a number of transaction
this is causing the query to bring a Transaction for each Invoice...i.e
payment £300.00 for 5 invoices...5 records of £300.00 in the query when
summing getting £1500.00, Ive tried different Join type s but to no avail.....


Hope this is enough information....

OK, I've incorporated your new Queries into my version of your database,
as well as three additional Tables ([tblAccount], [tblSalesOrderLine],
and [tblVAT]) that you hadn't mentioned. But it would also help if you
could post sample records from all of your Tables, maybe a couple of
records from each -- enough to illustrate what is happening in your
Queries. Given enough time, perhaps I could puzzle out all the details,
but my answer will mean more to you if the data look realistic. (A
short field name doesn't always convey an accurate idea of what exactly
is supposed to be inside that field, so example values can help
considerably.)

Just a quick update....
Ive managed to write the query to summ the payments (sort of)....

[quoted text clipped - 90 lines]

 

[Vincent Johns]

I'm sorry to keep putting you off (though, as I said, example records
would have helped). However, lacking those, I'll look at your
descriptions and try to come up with some suggestions by tomorrow.

One suggestion would be to consider if you ever need to split a
transaction, such as for two partial payments on one invoice or for one
payment covering two invoices.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

hi Vincent

Like I said I think I need two seperate queries, one showing all the total
sum of all invoices for each Customer(Account) and another showing all the
payments made by the customer. Then join them by Account Index and use an
expression in the summary subtracting payments from the invoice total....
Structure of tables as follows:
tblAccount:
AccountIndex (PK), Customer (yes/no) Supplier (yes/no), AccountName, Address
Fields...(1,2,3 etc), Town, TelNum, Faxnum,PostCode, CurrentOwed (If supplier)
, CurrentBalance(if Customer) DateAccountOpened

tblSalesOrderLine
SalesOrderNumber(PK), ProductCode(PK), QuantityOrdered, Price, VATRate (FK)...
.plus a few yes/no flags for coding

tblVAT
VATRate (PK), VATValue

Where VATRate is a code for the amount of Value Added Tax i.e 17.5%

I currently have the query for summing the total amount of Invoices each
month for each Customer(Account).....I now need the query for summing the
total of payments, part of Transaction table i.e the TypeOfPosting field can
either be Customer Payment or Pay Supplier
here is what I have so far...
SELECT SO.AccountIndex, T.TypeOfPosting, Sum(T.TransCredit) AS
SumOfTransCredit, Format([DateOfPosting],"mmmm/yy") AS MonthPaid
FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T
INNER JOIN tblSalesInvoice SI ON T.TransactionNumber = SI.TransactionNumber)
ON D.DespatchNumber = SI.[Despatch Number]) ON SO.SalesOrderNumber = D.
SalesOrderNumber
GROUP BY SO.AccountIndex, T.TypeOfPosting, Format([DateOfPosting],"mmmm/yy");

The problem here is a Transaction (payment) can be for more than one invoice..
.I.e all Invoices are paid in one Transaction....or a number of transaction
this is causing the query to bring a Transaction for each Invoice...i.e
payment �.00 for 5 invoices...5 records of �.00 in the query when
summing getting �0.00, Ive tried different Join type s but to no avail.....


Hope this is enough information....

OK, I've incorporated your new Queries into my version of your database,
as well as three additional Tables ([tblAccount], [tblSalesOrderLine],
and [tblVAT]) that you hadn't mentioned. But it would also help if you
could post sample records from all of your Tables, maybe a couple of
records from each -- enough to illustrate what is happening in your
Queries. Given enough time, perhaps I could puzzle out all the details,
but my answer will mean more to you if the data look realistic. (A
short field name doesn't always convey an accurate idea of what exactly
is supposed to be inside that field, so example values can help
considerably.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Just a quick update....
Ive managed to write the query to summ the payments (sort of)....

[quoted text clipped - 90 lines]

showing them...
just dont know how to rectify it