Finding Percentage of a Category

[JSlemons]

Hi All,

I set up a table to track defective parts on a shift-by-shift basis. The fields are: Entry Number (primary key auto number), Defect, Quantity, Inspector, Date, Shift, etc...

If someone inspects 1000 parts on day shift where 2 parts are found defective for rust and 1 is defective for lamination, I will enter the following data for "Defect" and "Quantity" into my database (Total of 3 new records):

[TABLE="width: 231"]
<TBODY>[TR]
[TD]Record #</SPAN>
[/TD]
[TD]Defect</SPAN>
[/TD]
[TD]Quantity</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]Total Inspected</SPAN>
[/TD]
[TD]1000</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]Rust</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]Lamination</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

I'm having trouble pulling information on what % of the total inspected parts each defect is. For example: I need to know what % of the parts were defective for rust. I have a query set up that will tell me how many parts were inspected, and another query that will tell me how many parts were rejected for rust, but what do I do to get the percentage? Can a query be devided by another query? Should I reformat my table to make this easier?

Any help would be greatly appreciated.

 

[xenou]

You can do this given your structure, but you need two "helper" queries, one to get the total quantity and the other to get the quantities by defect. Then you use these two helper queries as your source for the ultimate query:

Query 1 - Total Quantity

SELECT t1.InspectionDate, t1.Inspector, t1.Shift, 
			t1.Quantity AS Total_Inspected, t1.Defect
FROM Table1 t1
WHERE t1.Defect="Total Inspected";

Query 2 - Defect Quantity by Type

SELECT t2.Defect, t2.Quantity, t2.Inspector, 
		t2.InspectionDate, t2.Shift
FROM Table1 AS t2
WHERE t2.Defect<>"Total Inspected";

Query 3 - Final Result

SELECT t1.InspectionDate, t1.Inspector, t1.Shift, 
		t1.Defect, t1.Total_Inspected, 
		t2.Quantity, [Quantity]/[Total_Inspected] AS PCT
FROM Query1 AS t1 
INNER JOIN Query2 AS t2
	ON 
	(t1.InspectionDate = t2.InspectionDate) 
    AND 
	(t1.Inspector = t2.Inspector) 
	AND 
	(t1.Shift = t2.Shift);

If this project is growing you could use a more robust structure, where you have a "Header" table, which lists the general data about the count (inspector, date, quantity, shift, etc.). It also assigns an ID to it. The "Detail" table lists the particular exceptions by type. Your queries can then join these two tables to get results. It does require more though put into data entry because now you must have the "parent" ID number for each count, when you insert records into the "child" detail table - this is what relates the two tables. In that case a sample query would be:

SELECT 
	t1.EntryNumber, t1.Inspector, t1.InspectionDate, t1.Shift, t1.TotalQuantity, 
	t2.Defect, [t2.Quantity]/[t1.TotalQuantity] AS PCT
FROM 
	InspectionHead t1 INNER JOIN InspectionDetail t2
	ON t1.EntryNumber = t2.EntryNumber;

The above query assumes this type of table structure:
<img src="http://northernocean.net/etc/mrexcel/20120701_rel.jpg" />

Here are <a href="http://northernocean.net/etc/mrexcel/20120701_db.zip">Sample Databases</a> for both strategies (very simplified cases but hope it helps):
sha256 checksum (zip file): b6373ba145d8f4bf316cd3f5e1a5b854d3bedb5fb92977c5702e349f2e133907

 

[clayt101]

This is a related question (I think).
On the table below, is there a way to come up with a percentage in access instead of bringing it into excel? The total number is 501 in this case, but what about cases where I don't know the total number (or is that just way too difficult and I just should just sum them up separately)

Sunday	40
Saturday	43
Friday	76
Tuesday	78
Monday	81
Wednesday	83
Thursday	100

<caption> Query1 </caption> <thead>
[TH="bgcolor: #c0c0c0"] EVNT_DAY [/TH]
[TH="bgcolor: #c0c0c0"] CountOfEVNT_DAY
[/TH]

</thead> <tbody>
</tbody> <tfoot></tfoot>