Calculated Totals

This question goes along with my preveious question of union queries. Sorry for this to be so long, but trying to give an idea of whats happening.

Using Access 2000 - Police Activity Database / Reports

There are 5 categories: Tickets - Accidents - Warnings - Etc

Tickets:
Tickets are categorized as being either Moving Hazard or Non Moving Hazard. The table "ticket" has a code field that lists each class of ticket. The codes are from "005" to "012". The ticket table has fields, "TicketID", "DateIssued", "Code", "County","Offense", and "Officer". Along with that there are 4 other related tables to table "Ticket" that contains field, "TicketXXXID", "TicketID", "Code", and "XXXX". These 5 tables are each referenced with a parameter query with 3 [Form]![ReportDialog]![XXXX] criteria. The 5 queries are then joined by a union query to give me a "COUNT" sorted by the code from 001 thru XXX.

There are 4 other instances that work the same as tickets. I'm using a continous form for each instance (Tickets - Warning - Accident - etc) to enter the information. Each category is brought together by running a union query against the 5 parameter queries. The five Union queries are brought together using a final union query. Surprised to say, it all seems to work ok thus far.

My question is: In the middle of this long list of I need to include subtotals that are not recorded in a table. ie: codes 005-006-007-008 = 009 and 010-011=012. These needs to be calculated and included in the list on a report at the time of running the report.

I hope this is not confusing or that I have included enough info to give an idea of what is going on.

Thanks for any and all suggestions.

Ernest L. Kendricks

So let me see if I understand this right. Added into the middle of unioning the 5 groups of data together you also want to union in some calculated subtotals?

I'm afraid that is correct.

It should look something like:
002 Speeding 10> 5
002 Speeding 5> 2
003 Speeding 2> 3
005 Speeding 20> 4
006 TOTAL SPEEDING 14 (Calculated Total)
007 Window Tint 4
008 Def Equip 5
009 TOTAL 9 (Calculated Total)
ETC
152

I thought about additional parameter queries to return these totals
and then incorporate them into the union queries. That would mean an additional 8 to 10 more queries to all be tied back to one. Each table has a max of 6 fields with data sizes ranging from 4 to 15 char. Althought (to me) that seems reasonable, I'm concerned with performance and out right shutdown down the road when table size increases. My best quess is 700 or less entries per month for half of the tables and maybe 300 or so for the other half.

SQL would be a better choice, but simply not available in current eviroment. Upsizing to sql a year or two from now would/could be an option.

Thanks

Ernest

I'd say you're correct in that you'd need to add the extra queries and union in those results as well. I don't think you add that much overhead to generating your report.

That said, application complexity is really one of the big reasons to add a layer on top of your database using Visual Basic, PHP, ASP, etc. Not to mention the code is easier to maintain/change without touching the database.

Thanks ! ! !

I'll go for it and see how it all comes together

Ernest

You know, I really hate to ask this question, but:

How do I set the field value of the query to the value I want returned? When I have counted Code 005, 006, 006, etc, I need
the union query that called the parameter query to see this total
count as Code 012, and thus insert it into the proper ascending order.


Thanks

Ernest

You can add extra fields to the result of a query that return a string of your choosing by using the syntax: 'sometext' AS fieldname

To union the summing query results in with the data queries your summing query will have to return the same fields (at least something labelled the same) as the data queries. I don't know all the field names you're using but as an example you'd do:

Code: Select all

SELECT '012' AS Code, 'Speeding Total' AS Offense, Count(TicketID) AS CodeCount WHERE whatever...

which would give you
012 Speeding Total 14

Thanks ......

Seems to work great....


Ernest