Now that I have my data imported into tables, how can I calculate and print the results?
My calculations will be as simple as count how many records contain option "abc" in field D, for example.
I created a form with text fields where the user can input a few filtering parameters such as starting date, ending date, person name etc. When these are entered I can probably perform an sql query that will count records based on the filtering.
But considering I have quite a few of these statistic calculations - probably 2 or 3 pages long,
Is it reasonable to create an sql query for every one of the calculations?
Is report a good idea for organizing the output? If so, can a report update itself based on my filtering input?
it depends entirely on what you want to calculate
if you can do it it SQL then do it in SQL
however for some times SQL isn't practical (fer performance, data handling or other reasons). like so many things in the systems world their often is more than one right answer, and choosing 'the' right answer depends on circumstances, often the developers familiarity of certain techniques.
One thing to bear in mind with Access reports is that just like Access forms you can hang code behind events in the report. so you can often do a great deal of calculations or data manipulation in the forms on format (and other) events.
it comes down what information you are having to process and how its organised
bear in mind that an Access report doiesn't have to have anyhtign in a detail section, so you could arrange your report and just pull in summarised data by setting appropriate grouping in the report, and if you like use the aggregate functions =count([column name])
if you can write your SQL to do the summing then do so.. its going to be a lot more efficient than writing the code in the report.
if say you need to do a count by product group you can probably do that as a simple query
select Count(productid) as NoProducts, FieldD from mytable
group by FieldD
you will need to replace productid with a valid column name from your table
perhaps if you elaborate on what you want to achieve.. its a wee bit vague at present
Thank you, all valid suggestions.
In any case I'm now using the DCount function by passing it my filtered query, it makes things pretty efficient I think.
The query filters the global criteria such as date range and person's name - these won't vary throughout an entire calculation. So it only makes sense to filter them once.
Then the DCount function goes through the above query and counts records that meet more specific criteria.
I'm still not decided what is an easy way to organize all the information.
In Excel I would simply create a new worksheet and populate cells with the functions I need (See above screenshots).
Do I really need to create a text box for every value I want to print? There must be another way?
When you create a report in Access and other reporting tools, you create template for a Header(s), Detail, and Footer(s). Then Access uses the data to populate the report. If you are concerned about having to add too many textboxes then I would think you are trying to put too much information in one report.
The second screen shot looks like a good candidate for a Cross Tab query. The Object field would be your Row Header and the Place field would be the Column and the Data would be the Result column.
I found Cross Tabs to be a little confusing when I first started using them, so if you need more help let us know.
Thanks, crosstab query was pretty much what I was looking for.
I base a new crosstab query on my previous date and name filtering query, but I get error 3070 when running it.
Access complains that it can't recognize my 'forms!F_Welcome.Box_SDate' as a valid field(or any of the other fields if I omit this one), which is where the user inputs the criteria.
My filtering query works by its own, so it beats me why crosstab won't work.
The crosstab query is built by the wizard.
Here's my filtering query for reference:
WHERE (F2>=Forms!F_Welcome.Box_SDate) And F2<Forms!F_Welcome.Box_EDate) And
(F11=Forms!F_Welcome.Box_Technician Or "All"=Forms!F_Welcome.Box_Technician) And
(F5=Forms!F_Welcome.Box_Station Or "All"=Forms!F_Welcome.Box_Station);
Oh. Apparently I forgot to declare the parameters for the crosstab query. Yea it seems to work now.
But do I have to specify the column headings if I want to use the crosstab query inside a report? I would much rather leave the column headings to be decided automatically.