Re: Help with query

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 <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.


graeme34 via AccessMonster.com wrote:

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....
Vincent Johns wrote:

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 <vjohns@xxxxxxxxxxxxxxxxxx>
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 :(


.

Relevant Pages

  • Re: Help with query
    ... expression in the summary subtracting payments from the invoice total.... ... I currently have the query for summing the total amount of Invoices each ... FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T ... The problem here is a Transaction can be for more than one invoice.. ...
    (microsoft.public.access.queries)
  • Re: Need help with a Batch Query
    ... LEASES (ties the Tenant and Unit together) ... Transaction ... PAYMENTS ... FROM (qryCurrent INNER JOIN LEASES ON qryCurrent.LedgerID = LEASES.LedgerID) ...
    (microsoft.public.access.queries)
  • Re: Cant get query to work...
    ... I think I would start out with a query to sum payments against a specific ... INNER JOIN Invoices ON Orders.OrderID = Invoices.OrderID) ...
    (microsoft.public.access.queries)
  • Re: Student class payments
    ... By adding in fields specific to one type of transaction, ... The total of all amounts received on behalf of that student, ... Yes, partial payments could be ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Help with query
    ... Ive managed to write the query to summ the payments.... ... FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T ...
    (microsoft.public.access.queries)
Loading