Re: Help with query

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....
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 :(

--
Message posted via http://www.accessmonster.com
.

Relevant Pages

  • Re: Help with query
    ... 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. ... FROM tblSalesOrder SO INNER JOIN (tblDespatch D INNER JOIN (tblTransaction T ...
    (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: Business objects, subset of collection
    ... The application is for scheduling of payments of invoices and ... for the selected vendor ... subsets of the vendors / invoices: ... I have a Setfilter(DateTime filterDate) methods defined in the ...
    (comp.object)
  • Re: A/R form creation?
    ... it is something like this month {i will work in month steps,but if a company has dept the amount will carry over to the nexxt month} the A company has purchased lets say 780USD, but in total i get paid for the amount of 500USD, the dept is 280USD and if in this month isn't paid it will start the new month with -280USD at their balance.... ... The absolute minimum is a new table for incoming payments, linked to Customers, and a union query to add amounts due and subtract amounts paid to calculate the outstanding balance. ... It would also help if you added an extra yes/no field to both tables to mark settled invoices and totally used-up payments so you exclude them from the union query, or it will introduce performance issues over time as the number of sales / payments increases. ... how you handle the settlement is a whole different ballgame! ...
    (microsoft.public.access.forms)
Quantcast