Finding Percentage of a Category

JSlemons

New Member
Joined
Jun 27, 2012
Messages
1
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):

Record #</SPAN>
Defect</SPAN>
Quantity</SPAN>
1</SPAN>
Total Inspected</SPAN>
1000</SPAN>
2</SPAN>
Rust</SPAN>
2</SPAN>
3</SPAN>
Lamination</SPAN>
1</SPAN>

<TBODY>
</TBODY>

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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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

Code:
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

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

Query 3 - Final Result
Code:
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:
Code:
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
 
Upvote 0
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)

EVNT_DAYCountOfEVNT_DAY
Sunday40
Saturday43
Friday76
Tuesday78
Monday81
Wednesday83
Thursday100

<caption> Query1 </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>
 
Upvote 0

Forum statistics

Threads
1,211,792
Messages
6,104,000
Members
447,889
Latest member
birdman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top