Ms Access: Using Aggregate data in calculated field [Discrete count?]

[Nokoff]

Hi Guys,



I've been pulling my hair out on this one for a while and I feel like I've run into a dead end and maybe need to redesign my db structure a bit to accomplish this task. I'm sure if I was a SQL expert this would be easy but I'm struggling to find any resource that allows me to do this in Access. Please if anyone could shed some light that would help a lot.

Basically I have a table that contains compliance audit results for a particular set of documentation. It's a small part of the overall database.

There can be x number of documents sampled, (usually 5), and then I need to run a query to determine how many of those x documents sampled did not have a checkbox ticked. So if we audited 3 simswop documents and 2 of them have "FALSE" anwers for A1 then I would like to append the value 66.6%, (2/3) to the Master Audit table. I have attached an export of tblSimswops so that you can get a better idea of the structure.

tblSimswops.zip

It's linked back to a master audit by the PK AuditID_FK.

Now here is the trouble. I tried to create a query using "Totals", in order to "Group By" AuditID_FK. If I set the other fields to "Count", this query returns 3 records (since there are 3 discrete instances of AuditID, namely 1001-15,1015-15 and 1020-15. So this query is now telling me that for the first row, 5/5 (100%) of the answers were TRUE. In the second row, 3/4 (75%) were true and 100% for the last row. These are the values I would like to append to my master audit table, however I cannot use the Totals function in an append query.

CountOfSimswopID	SumOfSimswop A1	AuditID
5	-5	1001-15
4	-3	1015-15
1	-1	1020-15

Please does anyone know of a workaround for this problem? I will literally buy you a beer I have been struggling for so long on this with no avail.

Thank you kindly for taking the time to read this post.

Kind regards
Nokoff

[ranman256]

You must get the counts of the trues falses...
Q1=select [simSwap], Count([simSwap]) from tbtbl

you get
TRUE ,4
False, 3

Now take THAT query and get percents,
Q2= select *, countOfSwap/ sum(countOfSwap) as pct from Q1