Percentage Help

[Losartan]

I have a table that documents the result of unit inspections. The data is Date inspected (once/month), Unit, and about 12 yes/no fields.

I want to run a query that shows the percentage compliant (yes) of all of the categories per month. I also want to be able to graph the results for one year per month to show trends.

What is the best way to do this?

Thanks,
Jason

 

[Pat Hartman]

Your table is not normalized so that makes the process more complex than necessary. You will need to do 12 calculations rather than 1.

In any event, first you need to calculate the domain so you need a query that counts the records. Then make another query that sums the yes/no fields. If the fields are defined as yes/no data type, the yes value will be -1 so you can count the yes values with a Sum().
Select Abs(Sum(fld1)) as Sum1, Abs(Sum(fld2)) as Sum2, etc.
Finally make a third query that joins the other two and performs the percentage calculation. Calculate the yes value by division and calculate the no value by subtracting the yes % from 100.

 

[Losartan]

I'm sure this is difficult to answer via a forum, but;

What would be the best way to create the table to allow me to run the reports easier?

Jason

 

[Pat Hartman]

Look for posts on normalization. When you have more than one of something, you have many so instead of 12 columns, you would have 12 rows in the many-side table.

 

[ScottGem]

I'm sure this is difficult to answer via a forum, but;

What would be the best way to create the table to allow me to run the reports easier?

Jason

Try this:

tblInspections
InspectionID (PK Autonumber)
InspectionDate
Unit

tblInspectionResults
ResultsID (PK Autonumber)
InpectionTypeID (FK)
Result (Yes/No)

tluInspectionType
InspectionTypeID (PK Autonumber)
TnspectionType

You would implement this with a main form bound to tblInspections and a subform bound to tblInspectionResults. The subform would have a Combobox to select the type and a check box to indicate Yes/No.

To produce your analysis you would group by inspection and Sum the ABS of the Result field divided by 12 (assuming you always have 12 results).

 

[Losartan]

Thanks for your help Scott. I'm a novice at Access. What would my relationships look like? I made the tables and linked at InspectionTypeID, Results ID and Inspection ID, and another table I named units. Everytime I select an Inspection type, it defaults all of the entries to that one inspection.

Do you have any suggestions?
Thanks,
Jason

 

[ScottGem]

Sorry, I left out one field. In tblInspectionResults you need a foreign key of InspectionID. This is the only relationship between tblInspections and tblInspectionResults. It tells you which Inspection the results are for. The tluInspectiontypes is a lookup table to provide standardization of types. You could create a relation between the TypeID. Generally I name my primary keys and foreign keys with the same name to make it easy to follow.

 

[Losartan]

Scott,

I done the things that you recommended. Everything seems to be working fine except for my subform. I set the main form and the subform to data entry and allow additions to "YES". Everytime I open the main form, the subform is blank and the properties shows the subform to be set to allow additions to "NO" I can then change the value to yes and can enter data. The next time I open the form, the setting is "NO"
How can I fix this?

Also, my percentage query shows duplicates. I attached a copy of my db.

Thanks,
Jason

 

[ScottGem]

That makes perfect sense. Since the main form is set to Data Entry, it will always open to a blank record. Since there is no record in the main form, the subform will be blank also since there is nothing to link to. Additionally, since the main form is blank you cannot enter a record in the subform, until a record is entered in the main form and committed.

I would not use the Date Entry property on a main/sub form combo. Instead I would set your menu so that the form opens in Add mode.