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.

 

[MichaelRed]

Check "Partition" in Help. Generally this will do it. If not, it should at least get you started.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[aaahhhrrggghhhh]

Hi.

Try :

Iif(DateDiff("m",invoicedate,statementdate) = 0, "Current", Iif(DateDiff("m",invoicedate,statementdate) = 1, "OneMonth", Iif(DateDiff("m",invoicedate,statementdate) = 2, "TwoMonth", Iif(DateDiff("m",invoicedate,statementdate) = 3, "ThreeMonth", "OverDue&quot ) ) )

Regards,
Mr Big Dont be small. be BIG

 

[Egglar]

thanks for your replies, i put in that code you gave me Mr Big and it works great. It gives me a table with the criteria listed, but what i also need it do to is to add the total of the invoice (InvoiceAmount) of all the currents together, and the same for the one month two month three months and overdue. Ive tried messing adapting that code you gave me but with no joy. it needs to be something like:

IF InvoiceStatus = Current THEN ADD TO CurrentTotal
and so forth
so i get four outputs, each of the totals of the InvoiceAmount that are in each catogary

im sorry if i seem like i need to be spoon fed on this but i really havent much clue

thanks.

 

[JeremyNYC]

You could make four fields, (Current, etc) and use a single one of the Iif statements posted above in each one. That would give you three fields of zeros with the appropriate field holding the value of the transaction.

Then you could either turn it into a totals query or make another query based on this that is a totals query (not sure the first option will work, though it should).

Spend some time with this and you'll get it done. Use the help files a lot (iif, totals query, etc.).

Hope this helps.

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

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[Egglar]

ive been playing with the iif expression all morning, in a query. i thought i had it at one point, but it didnt work.

i worked out the best way to go would be to use 4 fields in a query, and use the iif function to pick out what dates are "current" and add it to its self, this expression is what i came up with.

CurrentTotal: IIf(DateDiff("m",[invoicedate],[statementdate])=0, [CurrentTotal]+[qryInvoiceTotalCalc]![InvoiceTotal])

i keep getting an error when i run the query
&quot;Circular reference caused by alias <name> in query definition's SELECT list. (Error 3103)&quot;

I guess this is because im trying to get it to add to its self, which is what i need it to do in order to get an overall total.

also another problem i can see coming up is the query getting the InvoiceTotal from the other query.

ive hit a wall once again

any ideas

 

[MichaelRed]

&quot;Partition&quot; see help, I believe that it is either this, or the hassle you are experiencing.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JeremyNYC]

Michael,

I am indeed glad to learn about the partition function. Thanks for that. I'd never seen it before, and I'm sure it will become quite useful.

But partition puts different groups in different rows, and I'm not sure that's what the original poster wants. And iif statements can do a fine job of that.

Eggar, the thing that gave you a hard time on this is that you were trying to do the adding in your field definition, but what you want to do is add across rows. Did you look into the help file for Totals Query? That's where you'd learn how to do that kind of adding.

In any case, here is a query I built on one of my tables that does what I'm talking about.

SELECT
Sum(IIf(DateDiff(&quot;m&quot;,[DueDate],[CompletionDate])=0,[ArchiveGLAmount])) AS CurrentTotal,

Sum(IIf(DateDiff(&quot;m&quot;,[DueDate],[CompletionDate])=1,[ArchiveGLAmount])) AS LateTotal,

Sum(IIf(DateDiff(&quot;m&quot;,[DueDate],[CompletionDate])=2,[ArchiveGLAmount])) AS ReallyLateTotal,

Sum(IIf(DateDiff(&quot;m&quot;,[DueDate],[CompletionDate])>2,[ArchiveGLAmount])) AS WayTooLateTotal

FROM tblAction;

It works fine to leave the blank spaces in and just paste this into the sql view of a new query, though you might want to use a word processor to quickly replace my table and field names with yours.

Hope this helps.

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

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[MichaelRed]

But partition puts different groups in different rows?, and I'm not sure that's what the original poster wants. And iif statements can do a fine job of that.

do you mean columns?

My help shows columns. while the set of IIF statements CAN do the identification, it SEEMS to me that the partition does the identification and the aggregating - with a single statement, as opposed to the several -along with the attendant possability of errors (overlapping or leaving gaps).

Using a date format to get the records' month along w/ the partition should make the whole process just two or three fields in the aggregate query - whic more-orless- automatically sort out the whole affair.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JeremyNYC]

Michael,

Have you used the partition function? I tried it before posting the last time around, and it definitely puts the data in different rows, not different columns.

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

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[MichaelRed]

I must be mis-reading the info. As viewed through SQL, you are correct, as &quot;Group By&quot; is necessary. It would require an additional transform.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JeremyNYC]

Michael,

This isn't meant to be as snide as it could sound, just a little friendly ribbing, but I do want to point out that if you'd listened to your sig file, you might have run a quick test on partition to see what it actually did...
&quot;There is never time to do it right but there is always time to do it over&quot;

In any case, I don't know for sure that my solution is better for the original poster than yours, I just wanted to make sure that it was clear that the iif method would work fine.

And I really _am_ glad you pointed out the partition function, as I really will use that a good bit in the future.

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

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[MichaelRed]

The tag line comes from the school of hard knocks, but that doesn't deter all mistakes.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Egglar]

Thanks guys, thanks for your time and your effort in posting, i really am greatfull. Ive read down this column about three times now, extracting as much information as i could, and i tried the sql you pasted Jeremy thanks for that, but didnt manage to get it to work. So ive created 4 differnt queries, using the group by function (i worked out how to do this after you pointed me in the direction of Totals) and putting the criteria as the number of months to be the difference, setting the date as the date() function and putting the datediff expresion in a hidden group by column, then setting the sum of InvoiceTotal.

i have one tiny problem left, the query i use to calculate the total of the invoice keeps coming up with &quot;Enter Parameter&quot; for the [SubTotal] and the [VAT], this is because they are both in one query used to work out the invoice total in stages. Is there a way of stoping the query asking for these paramters, because after the query has run, it works them out any way like its ment too, the sql is as follows (aplogies for the length of it):

SELECT tblInvoice.InvoiceNo, tblInvoice.InvoiceDate, Nz([LabourTotal],0)+Nz([CallOutFee],0)+Nz([MileageFee],0)+Nz([MOTFee],0)+Nz([PartTotal1],0)+Nz([PartTotal2],0)+Nz([PartTotal3],0)+Nz([PartTotal4],0)+Nz([PartTotal5],0)+Nz([PartTotal6],0)+Nz([PartTotal7],0)+Nz([PartTotal8],0)+Nz([PartTotal9],0)+Nz([PartTotal10],0)+Nz([PartTotal11],0)+Nz([PartTotal12],0)+Nz([PartTotal13],0)+Nz([PartTotal14],0)+Nz([PartTotal15],0)+Nz([PartTotal16],0)+Nz([LabourBreakDown1],0)+Nz([LabourBreakDown2],0)+Nz([LabourBreakDown3],0)+Nz([LabourBreakDown4],0)+Nz([LabourBreakDown5],0)+Nz([LabourBreakDown6],0)+Nz([LabourBreakDown7],0)+Nz([LabourBreakDown8],0)+Nz([LabourBreakDown9],0)+Nz([LabourBreakDown10],0)+Nz([LabourBreakDown11],0)+Nz([LabourBreakDown12],0)+Nz([LabourBreakDown13],0) AS SubTotal, [SubTotal]*0.175 AS VAT, [VAT]+[SubTotal] AS InvoiceTotal, tblAddress.Address1, tblAddress.Address2, tblAddress.Town, tblAddress.County, tblAddress.PostCode, tblInvoice.Customer
FROM tblAddress INNER JOIN tblInvoice ON tblAddress.Name = tblInvoice.Customer
GROUP BY tblInvoice.InvoiceNo, tblInvoice.InvoiceDate, Nz([LabourTotal],0)+Nz([CallOutFee],0)+Nz([MileageFee],0)+Nz([MOTFee],0)+Nz([PartTotal1],0)+Nz([PartTotal2],0)+Nz([PartTotal3],0)+Nz([PartTotal4],0)+Nz([PartTotal5],0)+Nz([PartTotal6],0)+Nz([PartTotal7],0)+Nz([PartTotal8],0)+Nz([PartTotal9],0)+Nz([PartTotal10],0)+Nz([PartTotal11],0)+Nz([PartTotal12],0)+Nz([PartTotal13],0)+Nz([PartTotal14],0)+Nz([PartTotal15],0)+Nz([PartTotal16],0)+Nz([LabourBreakDown1],0)+Nz([LabourBreakDown2],0)+Nz([LabourBreakDown3],0)+Nz([LabourBreakDown4],0)+Nz([LabourBreakDown5],0)+Nz([LabourBreakDown6],0)+Nz([LabourBreakDown7],0)+Nz([LabourBreakDown8],0)+Nz([LabourBreakDown9],0)+Nz([LabourBreakDown10],0)+Nz([LabourBreakDown11],0)+Nz([LabourBreakDown12],0)+Nz([LabourBreakDown13],0), [SubTotal]*0.175, [VAT]+[SubTotal], tblAddress.Address1, tblAddress.Address2, tblAddress.Town, tblAddress.County, tblAddress.PostCode, tblInvoice.Customer, tblInvoice.PaidDate
HAVING (((tblInvoice.Customer)=[Forms]![frmStatment]![cboStatmentCustomerSelect]) AND ((tblInvoice.PaidDate) Is Null));


if the sql was reorderd would these parameters still be asked for?

 

[JeremyNYC]

Egglar,

Well, I didn't manage to read through that whole query, as I'm pretty shot after a crazy day, but I can say that I don't think that changing the order of things in your SQL statement will have any effect on whether or not you are prompted for those parameters. On the other hand, you could build one query that gets those values and then build another query on top of that.

But...how much did you play with the sql I sent you? It's a pretty standard way to go about something like this. What was going wrong with it? If you send your table names and the last sql you got in your attempts at this way it should be easy enough to whip it into shape.

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

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Egglar]

my table names

tblInvoice (contains all costs and dates)
StatmentDate (current date)
InvoiceDate (date of invoice found in tblInvoice)
InvoiceTotal (found in qryInvoiceTotalCalc - its the total of the SubTotal and the VAT. and i susspect the the cause of many of the problems im having is the SubTotal, VAT and InvoiceTotal calculation

THis is the sql im using to calculate the aged analysis. there are 5 querys, one for each catagory, curent, overdue, onemonth, twomonths, and threemonths.

SELECT Sum(qryInvoiceTotalCalc.InvoiceTotal) AS SumOfInvoiceTotal
FROM tblInvoice INNER JOIN qryInvoiceTotalCalc ON tblInvoice.InvoiceNo = qryInvoiceTotalCalc.InvoiceNo
HAVING (((DateDiff(&quot;m&quot;,[qryInvoiceTotalCalc]![InvoiceDate],Date()))=0));

and it works, until i set txt boxes to the report to display each catgory, at which point it says #Error.

if i could put all of the invoice calculation and the aged analysis calculation into one query i suspect will solve this problem, but as yet i havent worked out how to group 4 differnt sets of data in a query and then work out the totals for each group.

the sql in my above post is taken from the qryInvoiceTotalCalc. it works out the SubTotal, VAT and InvoiceTotal.

if there is a way to combine the whole lot into one query, more so the aged analysis into one query, but stop these Enter Parameter Value boxes coming up.

again thanks for your time in helping me.

 

[aaahhhrrggghhhh]

Hi Egglar,

Can you post the table descriptions for us, the sql is a little too tricky if we dont know what the table layout is. This will allow us to replicate the problem. It seems like you've gotten yourself in a little muddle :

eg.

Customer
--------
Customer ID Autonumber
Customer Forename Text(50)
...

Regards,
Mr Big. Dont be small. be BIG

 

[Egglar]

-----
tblAddress
-----
Name Text
Address1 Text
Address2 Text
Town Text
County Text
Postcode Text
PhoneNumber Text
FaxNumber Text
Type Text
Notes Memo

-----
tblInvoice
-----

InvoiceNo AutoNumber
InvoiceDate Date
Customer Text
OrderNumber Text
DescriptionOfWork Memo
VehcileRef Text
VehicleMileage Text
PartQtyX Number
PartDescriptionX Text
PartTotalX Currency
(X = numbers 1 to 16, ie there are 48 partqty, description and total fields all together. The part total is not multiplied by the quanity because of the way the business works, PartTotal is just a manualy imputted figure.
LabourBreadownX
(X = 1 to 13, same as above, 13 seperate part labour fields.)
PaidDate Date

-----
tblVehicle
-----

VehicleRef
Make
Model
Owner

-------------------

Name, Customer and Owner are all related fields. So is VehicleRef to eachother accross tblInvoice and tblVehicle.
I can put the database in 2002 or 97 format for download if this would make life eaiser, just let me know. THanks

 

[JeremyNYC]

Egglar,

OK, so I'm still being lazy here and not reading everything, but one thing is very clear here. You need to create two new tables: one to hold part details and one to hold labor details.

This is critical, and will make your life MUCH easier.

It might not seem like it now, but I promise it is true.

What I would recommend is that you do a google search for &quot;data normalization&quot; and read up on the topic. You only need to know the first three rules, but those rules should become part of your blood. There are times when those rules should be broken, but this is most assuredly not one of those times.

</evangelicalMode>

That said, it looks to as if you should just build two queries. One would build the subtotal, the other, based on the first, would do the other calculations. Or, if you want, you could copy and paste that whole calculation in place of the &quot;[subtotal]&quot; in both &quot;[SubTotal]*0.175 AS VAT&quot; and &quot;[VAT]+[SubTotal] AS InvoiceTotal&quot;, but I would imagine that would make for one SLOW query.

But really, I hesitate to even give you that advice, because changing your table structure will make your life SO much easier.

Hope it helps.
Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[Egglar]

i know normalisation from my college days (college in the uk that is) we did the first three levels, but i was to lazy to do it for this little project of mine, but i see what your saying, so i should create 2 more tables

tblParts and tblLabour

and have PartQtyX PartDecriptionX and PartTotalX (X being as above 1 to 16) in tblParts, and the same for tblLabour. Is that a better way of doing it? oh and of course have the forgien key in there.

is that the correct way?