Sending data from a form to a report through a query 2

[bfamo]

Working on a little project at work.
We have a database in which we register alot of information about measurements we do. We grade each measurement between 0 and 70, and these grades is put into the database together with the date that the measurement has been done.

What I basically want is a report that counts all the measurements which have grades between 0 to 40, within a determined period of time.

To illustrate my problem, I have made an example of what I'm thinking the finished result would look like.

First off, I have a table where the information is stored:

This forms the basis for the report.

Then, you open the form "FrmSelectYear" and select for which year you want the report to count:

When you press the button "View Report" in the form, the report runs a query that collects and counts all of the grades between 0 and 40 given in 2003, and shows it in a report called "RptShowResults".


I have been trying alot of differen approaches, but I guess my sql and report skills are not what it should be...

I have made a query that just collects all of the values/grades and the belonging dates in the whole time period. Then, a second query counts the grades.
From here I have been trying to link the query to the form and report but I cant seem to find a solution.

Anyone who can give me some tips on this one?

thanks alot!

 

[dhookom]

Create a query like:

SELECT Count(*) as NumOf
FROM tblNoName
WHERE Year([Date]) = Forms!frmSelectYear!cboYear
AND [Value] BETWEEN 0 AND 40;

This will return only a single value. How do you want to display this in a report or form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bfamo]

thanks dhookom, this is what I was thinking about.

In the example above, [Value] represents the number of deviations whithin one of many criterias that we have measured(so that in my project at work there will be [Value1], [Value2] etc. dependent on the number of criterias). The grades between 0 and 40 is considered to be a deviation from what is allowed in our measurements, so the report gives us an idea of how many deviations we have each year within the different measured criterias. My idea of how the report will look is something like this:

Report for the year 2003.

Deviation in work enviorment:

Overall hygiene ---> [Value1]
Quality of lighting ---> [Value2]
Use of protective equipment ---> [Value3]


Hope that was explanation wasn't too difficult to understand

Your query covers the first [Value], but what do I do when there is more criterias to be put into the report? Do I make one query for each [value] or can I get it all into one query?

With this in mind, the challenge is also to link this one (or several) query to the "View Report" button.

thanks alot!

 

[dhookom]

Can you start from the beginning and provide information about your table(s) with some sample records? Then provide how you want to filter these records and how you want them displayed in the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bfamo]

yeah, let's take this from the beginning

What we do:
Inspections for our customers to ensure that working conditions is in accordance with the law.

How we do it:
Fill out a form during inspections with criterias such as "Use of protective equipment". Our customers is graded on each of the criterias we inspect. This grading scale has 10 intervals and reaches from 10 to 100.

Let's say we find that few of our customers employees use protective equipment when this is required. In that case we would give the grade 20 in the criteria "Use of protective equipment". All grades ranging from 10 to 40 is considered to be a deviance from what is in accordance with the law, and is marked with what we call a "bad mark".

In our report we want to see how many "bad marks'" we give each year within the different criterias. This report does not have to go in to specifics (ex. which customer got what grades and so on), simply just work as a counter to give us a general view.

The Database:
Covers all the relevant information we need about our customers. This is also where we store all of the inspections.

"TblCustomers"contains info about the customer such as:
- Name
- Adress
- Phone
- E-mail

"TblInspections" contains info about the inspections such as:
- NameInspector
- InspectionDate
- Customer
- GradeCriteria1
- GradeCriteria2
- GradeCriteria3 etc.

These tables are connected with a one-to-many relation.

Since I can't provide any real records from our system, I'll show you an example of a record in "TblInspections":

NameInspector: David
InspectionDate: 21.04.2003
Customer: Car Repair
GradeProtectiveEquipment: 30
GradeOverallHygiene: 70
GradeQualityLighting: 40
GradeFireRegulation: 80


David has given "Car Repair" two bad marks in this inspection. It's these bad marks we want to filter out in all our records.


The Report

The reports' heading should say which year the report represents.
Under the heading you see all the criterias that is graded, together with all registered bad marks.

Last comments:
The sql-query you provided worked just fine. the only problem is that it only counts one of the criterias, and not all of them.


thanks a million for helping me on this one!!

 

[PHV]

["TblInspections" contains info about the inspections such as:
- NameInspector
- InspectionDate
- Customer
- GradeCriteria1
- GradeCriteria2
- GradeCriteria3 etc
Have a look here:

http://r937.com/relational.html

If you can't change the (bad) design of your DB, then you may consider a normalization union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bfamo]

Thanks for the comment PHV. It is in fact a relational database, it contains many related tables. I didn't write much about that, because the report I want only needs data from one table, "TblInspections".

 

[dhookom]

PH is correct as usual. I created a similar application for my employer. Each "criteria grade" should create its own record in a table. I would add an InspectionID to tblInspections and remove the GradeCriteria fields. Your actual grade table would look something like:
[tt]
tblInspGrades
===================
InspGradeID
InspectionID link to tblInspections.InspectionID
CriteriaID link to tblCriteria.CriteriaID
Grade value/grade received
InspComments could add more fields for comments etc.
[/tt]
My application works very well and users can add or change criteria without having to add fields or modify forms, reports, queries, code,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bfamo]

ah... I see.

Only problem is that I'm unable to change the design of the database because of heaps of VBA code using this table.

Do I have other alternatives to changing the design?

I must say, and this is on basis of my limited experience with sql, that it seems somewhat weird that I'm not able to collect and count all grades from 10 to 40 in a period of time and present them in a report.

I've made a similar report which does all the counting, the only problem is that you're not able to determine the period of time...

This might be the right place for PHV's normalization union query?

 

[dhookom]

I imagine you might need "heaps of VBA code using this table" when your tables aren't set up normalized.

You could normalize your tblInspections. I would first add a primary key (possibly an autonumber) named InspectionID. Then create a union query like:

SELECT InspectionID, [GradeCriteria1] as Grade, "Use of Protective Equipment" as Criteria
FROM tblInspections
UNION ALL
SELECT InspectionID, [GradeCriteria2], "Over All Hygiene" 
FROM tblInspections
UNION ALL
SELECT InspectionID, [GradeCriteria3], "Quality Of Lighting" 
FROM tblInspections
-- etc --
FROM tblInspections;

You can then join the union query to tblInspections on InspectionID and more easily create your queries/reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bfamo]

the VBA code has to do with the interface in the access forms, and not so much the processing of data.

Here is the relations in the database:

 

[bfamo]

 

[dhookom]

I was only guessing that your code was used to work around your table structure. Did you try the union query? This would enable you to do the report/query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bfamo]

good got the union query working, and tested it in a temporary report. The only thing remaining now is the Date. This way we can choose from what year we want to see the results.

 

[dhookom]

tblInspections has the date. You can join to this table with the InspectionID field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]