Calculating Multiple Items in Report

[andrewneal]

Let me start by saying, I am not sure if a Report would be my best option here or if a printable Form would be optimal.

Anyway, doing a manning database to calculate number of employees versues number of authorized employees (military). I want this to show as a column for percentages ... ie ... This has to be done by each rank (about 20 different ranks) on the same report. If there are 100 SrA authorized and 75 SrA working, then the text box would show 75%.

I can do the "math" part of figuring this all out, but my question is would a Report or printable Form be my best bet?

Also, would I benefit from building a query for each pay-grade in each unit or can I do this through VBA? Will one "count" query be able to be used and just the squadron and count of rank I need be filtered out of thi

s one query via the expression in the text box for each squadron and rank? I pasted my main "count" query and the results for each squadron and rank so you can get an idea of what I am talking about.

SQUADRON RANK CountOfRank
23 AMXS AB 11
23 AMXS AMN 11
23 AMXS A1C 119
23 AMXS SRA 116
23 AMXS SSGT 138
23 AMXS TSGT 73
23 AMXS MSGT 41
23 AMXS SMSGT 10
23 AMXS CMSGT 3
23 AMXS 2LT 1
23 AMXS 1LT 3
23 AMXS CAPT 5
23 AMXS MAJ 1
23 CMS AB 5
23 CMS AMN 5
23 CMS A1C 46
23 CMS SRA 21
23 CMS SSGT 38
23 CMS TSGT 20
23 CMS MSGT 14
23 CMS SMSGT 2
23 CMS CMSGT 1
23 CMS 2LT 1
23 CMS CAPT 2
23 CMS MAJ 2
23 EMS AB 24
23 EMS AMN 16
23 EMS A1C 115
23 EMS SRA 138
23 EMS SSGT 187
23 EMS TSGT 90
23 EMS MSGT 56
23 EMS SMSGT 7
23 EMS CMSGT 2
23 EMS 2LT 5
23 EMS 1LT 1
23 EMS CAPT 3
23 EMS MAJ 1
23 EMS MR 1
23 EMS MRS 1
23 MOS AB 2
23 MOS A1C 16
23 MOS SRA 19
23 MOS SSGT 39
23 MOS TSGT 20
23 MOS MSGT 11
23 MOS SMSGT 1
23 MOS CAPT 1
23 MOS MAJ 1
23 MOS MR 1
23 MXG STAFF AMN 1
23 MXG STAFF A1C 7
23 MXG STAFF SRA 9
23 MXG STAFF SSGT 36
23 MXG STAFF TSGT 22
23 MXG STAFF MSGT 1
23 MXG STAFF SMSGT 4
23 MXG STAFF CMSGT 2
23 MXG STAFF CAPT 2
23 MXG STAFF LT COL 1
23 MXG STAFF COLONEL 1
23 MXG STAFF MR 11
23 MXG STAFF MRS 1
723 AMXS AB 27
723 AMXS AMN 36
723 AMXS A1C 148
723 AMXS SRA 80
723 AMXS SSGT 154
723 AMXS TSGT 60
723 AMXS MSGT 35
723 AMXS SMSGT 5
723 AMXS CMSGT 3
723 AMXS 2LT 3
723 AMXS CAPT 3
723 AMXS MAJ 1
723 AMXS LT COL 1
723 AMXS MRS 2
UNASSIGNED A1C 1

 

[boblarson]

Reports are optimized for printing, forms are not. Reports have extra grouping and sorting capabilities that forms do not. Most of the time, and I believe including this time, Reports are the way to go for printing.

 

[boblarson]

Oh, and you can do the counts either by using a query or by using the grouping and sorting feature and then using the aggregates in a report.

 

[andrewneal]

Thanks for the very quick response. After reading your post I am not so worried about "printing" as much as viewing the data easily. The individuals viewing this data are upper management and do not always understand the efforts involved nor the time used in getting them the data they want to see...how they want to see it.

In this database, printing the data is a secondary option that may be used rarely but not enough to, in and of itself, require a report. Now, if making multiple calculations on a report is easier than a form, then I will persue building a report.

 

[boblarson]

Thanks for the very quick response. After reading your post I am not so worried about "printing" as much as viewing the data easily. The individuals viewing this data are upper management and do not always understand the efforts involved nor the time used in getting them the data they want to see...how they want to see it.

In this database, printing the data is a secondary option that may be used rarely but not enough to, in and of itself, require a report. Now, if making multiple calculations on a report is easier than a form, then I will persue building a report.

A report in this case I think would suit you very well.

 

[andrewneal]

Just so I don't head down the wrong path; what is the best way to show multiple query and table information on a single report? Or is that even possible. I could be running as many as 30 queries on a single report.

 

[andrewneal]

I will need to calculate multiple percentages on the report (30+). I have a pretty good grasp on how to do the math part of figuring this out (thanks to previous posts in this awesome forum); question is...how do I get the different "unbound" text boxes to see and compute multiple queries and tables on the same report? Do I need to build 200 subreports for this or is there a VBA code I could get it to run and pull all the data from there?

 

[boblarson]

Actually, based on your example I was thinking you could use the report's grouping capabilities to group by squadron, rank and then use a text box in the rank footer that is like =Count([Rank])

But, you might be able to do a lot of it with queries first and then bind the text boxes to the query value. It really depends on what you are calculating.

 

[andrewneal]

I will need to calculate the percentages of each rank under each squadron in a chart type thing.

I attached a picture of a general idea of how they want it to look...though it won't be "exactly" like this...it will need to be close. So, the report will be crunching a lot of numbers like an excel spreadsheet type thing. I just want it to pull all this info from the main table that everyone is entered in.

Trying to automate the process as much as possible and save some time for the manning director.

 

[andrewneal]

How do I bind text boxes to a query result?

Does the query have to be "open" at the time the report is opened, or will the report run the query in the background?

 

[boblarson]

How do I bind text boxes to a query result?

Does the query have to be "open" at the time the report is opened, or will the report run the query in the background?

A report has a Record Source property and you would select the query to bind the report to that output. You may need a few reports as subreports and you can bind them to their recordsource and then link the master/child links to the appropriate field.

 

[zariazakeryzavien05]

In addition, learn the dlookup function. It is very useful, and allows you to get information from sources other than your record sources.