Aged analysis in a query, grouping sums.

[Egglar]

i know what i want to do, i just dont know how

basically i am making a statment report for an accounts database. I want to do an aged analysis on the statment. for instance, it totals up what the customer owes current, 30, 60, 90 and "over" days.

If the invoice date is in the same month as the statment date (statment date will be the current date) then the invoice total goes into group "Current"

If the invoice date is in the month before the statment date then the invoice total goes in group "OneMonth"

If the invoice date is 2 months before the statment date then the invoice total goes in group "TwoMonths"

If the invoice date is 3 months before the statment date then the invoice total goes in group "ThreeMonths"

Any thing greater than 4 months goes in the "OverDue" group.

Is there a way to do this in expression builder in the the query builder? so the query will output 4 totals, current, onemonth, twomonths, theemonths and overdue

Any help would be greatly appriceted.

 

[JeremyNYC]

Close. Actually, what you'll want to do is have tables like this:

tblPart
-------
PartID (Autonumber, Primary Key)
PartDescription
UnitPrice

tblInvoicePartDetail
-------
InvoiceNO (Foreign Key to tblInvoice)
PartID (Foreign Key to tblPart)
UnitPrice (non normalized, b/c prices may change)
Quantity

This will allow you to store data about what your parts are in one table and info about what quantities of what parts at what prices are associated with what invoices. There may well be more fields in either of these tables, but one of the principles of normalization is that you don't have repeating fields, you have repeating rows, instead. Otherwise, the first time someone wants to order a 17th part, you're hosed. And it just makes calculations MUCH more difficult.

You'll want to do the same with the other set of details (was it labor?).

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Egglar]

im in the process today of re doing most of the database.
but ive had to modify your table structure to fit the needs of the business.

basicaly, there is no parts database, parts have to be typed in manualy, descriptions, totals and qtys. because the way the business works (its only small) job sheets are handed in and totaled up then the typest types them. therefore there can be no unit price multiplied by quantity etc. what ive done is create tblParts, and in there an InvoiceNo, Partqty, PartDescription and PartTotal field. InvoiceNo relates to InvoiceNo in tblInvoice. This allows manual input of parts (using a sub form) but its not limited to 16 parts as above. I did the exact same thing with labour and put sub reports on the main invoice report, all seems in order.

now i need to redesign the querys, which i need some help on. is it best to have one query for one report, or in contrast; have one query per calculation and then use multiple querys to one report?

and using Totals, i can group up groups of data in a single query, but the problem comes when trying to sum up these groups. i wokred out how to sum up one goup of data per query, but the problem comes when wanting to sum up more than one group of data per query, or should i do one group per query for this reason?

sorry for the length of this entire thread, i suspect ur probably getting a bit annoyed with my endless questions.

 

[Egglar]

ive now created 4 new queries to calculate the invoice. these are;

qryInvoicePartsCalc - groups all the part totals together in tblParts by InvoiceNo and adds them.

qryInvoiceLabourCalc - groups all the labour totals together in tblLabour by InvoiceNo and adds them.

qryInvoiceSubTotalCalc - Adds PartsTotal, LabourTotal and some other figures from tblInvoice

qryInvoiceVATCalc - Multiplies InvoiceSubTotalCalc by 0.175 to get the current VAT rate in the UK

qryInvoiceTotalCalc - adds InvoiceSubTotalCalc and InvoiceVATCalc together to give the overall total.

all these ive tested and work perfectly. the problem i have now is when i try to bind the outputs of the querys to txt boxes on the form i get an error. so i decided to use the report wizard to see if i could do it in there. i added fields i required from all the querys listed above and hit the next button, i got the message;

"You have chosen fields from record sources which the wizard cant connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query"

surley access can show more than one query on a report...that would be just silly?!?!

 

[JeremyNYC]

Egglar,

First off, no problem with the length of the thread. This is how learning happens.

Second, I don't use the wizards, so I'm not too familiar with the notion of putting more than one table or query behind a form or report. The standard way to do this is to write a query that gathers all the fields you need and then base the form or report on that single query. Your query can be based on as many tables and queries as you'd like.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Egglar]

today ive been making all new queries to proide the base data, then make queries which took this data from the base queries and then bind the reports to the queries.

i have run into a problem with a simple bit of calculation. since you told me the better way of doing the Parts and Labour was to have seperate tables, im having trouble calculating the totals for Parts and Labour.

SELECT Sum(tblParts.PartTotal) AS SumOfPartTotal, tblParts.InvoiceNo
FROM tblParts
GROUP BY tblParts.InvoiceNo, tblParts.InvoiceNo;

this is the sql i have used to take all the parts with the same invoiceno (the forgien key) and add them together to give a total of the parts, i used the same with for the labour but changed the field names of course. i put in some test data and run the query, this worked fine and this was the output i got:

qryPartsCalc
InvoiceNo SumOfPartTotal
1 £4.00
2 £1.00


qryLabourCalc
InvoiceNo SumOfLabourTotal
1 £10.00
2 £6.00

which is correct according to the test data i put in. so i created a new query which would add the SumOfPartTotal and the SumOfLabourTotal together per invice, the expected output i wanted was

qrySubTotal
InvoiceNo SubTotal
1 £14.00
2 £7.00

however, i didnt get this, i got this:

InvoiceNo SubTotal
1 £18.00
2 £18.00

the sql im using is
SELECT Sum([qryLabourCalc]![SumOfLabourCost]+[qryPartsCalc]![SumOfPartTotal]) AS SubTotal, tblInvoice.InvoiceNo
FROM qryPartsCalc, qryLabourCalc, tblInvoice
GROUP BY tblInvoice.InvoiceNo;

i just cant understand why it cant simply add the figures together, i cant work out where its getting £18.00 from either.

from what i can work out (and ive been trying to fix this for the last 5 hrs) is that the group by, sum method used in the first two queries arnt allowing the last query to add the two values..

is this right? and im guessing my above sql is flawed?

 

[Egglar]

i just made a small test qry, it seems that the data being taken from the two queries that calculate the totals from grouping the invoice numbers isnt right. I have two test records in my database, when i run each individual qry (qryPartsCalc and qryLabourCalc) the output is as expected. But then when i made this test query, it seems that its taking 4 records from each of the other qrys.

----------
qryPartsCalc
----------
PartsTotal InvoiceNo
£4.00 1
£1.00 2

----------
qryLabourCalc
----------
LabourTotal InvoiceNo
£3.00 1
£1.00 2

The results from the two above queries are correct.

-----------
qryTest
-----------
InvoiceNo SubTotal PartsTotal LabourTotal
1 £2.00 £1.00 £1.00
1 £4.00 £1.00 £3.00
1 £5.00 £4.00 £1.00
1 £7.00 £4.00 £3.00
2 £2.00 £1.00 £1.00
2 £4.00 £1.00 £3.00
2 £5.00 £4.00 £1.00
2 £7.00 £4.00 £3.00

i simply added the fields from qryLabourCalc and qryPartsCalc, and for some reason it imports the data as four seperate records. at least this explains where the total of £18.00 i said i was getting in my last post(2+4+5+7 in the SubTotal column). i just cant undstand why.

 

[JeremyNYC]

Egglar,

Just got back into town and haven't actually read through your last two posts, but I did glance at them. One quick thought: are the queries joined in the query by example grid? It sounds like you've got the two queries in there but that they're not joined. If that's the case, click and drag the id field from one query to the id field from another query.

If that's not it, post back, and I'll actually read what you've written.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Egglar]

apologies for delayed reply.

i set up the query joins as u suggested, not actualy sure what i was doing, i just linked fields and ran the query until it worked. it did work. but then, when i wanted to use the query that i put the joines in another calculation, the output of the second query split the results up again and i end up with 4 outputs instead of the expected two.

i think what i need to do is join the queries by invoice number (the primary/forgien key in all tables). But every time i do that i get an error "Type missmatch in expresion".

could u just give me a short explnation of how queries should be joined (not how to do it, rather what fields should be joined what fields and on what queries. i will give you a summary of all the queries im using below so you can get the idea.

-----------------
qryPartsTotalCalc - based on tblParts. Groups all the records wtih the same InvoiceNo and sums them. Fields Contained:
InvoiceNo
PartTotal

-----------------
qryLabourTotalCalc - based on tblLabour. Groups all the records wtih the same InvoiceNo and sums them. Fields Contained:
InvoiceNo
LabourTotal

-----------------
qrySubTotalCalc - Adds the LabourTotal, and PartsTotal (from there respective queries), also the MileageFee and the CallOutFee from tblInvoice. Fields Contained:
InvoiceNo
SubTotal

-------------------
qryVAT - Takes the Subtotal and multiplies it by 0.175 to get the current VAT Rate.

-------------------
qryTotal - Sums the Subtotal, VAT and MOTFee (MOTFee cannot have VAT added, hence its added after VAT, MOTFee found in tblInvoice.

thanks for your help.