Query limit? Getting a little overwhlemed.

[Alhassani]

I am approaching the final stages of completing my database. The database is for my quality department. It collects all audits that are completed and then generates various types of scores including employee and project scores on a weekly and monthly basis. Excel can handle all of these functions with ease and has for a year now. One of the biggest reasons we are moving away from excel to an access database is that Excel cannot generate error specific information for our employees to know what they are getting hit on the most. (It can, but the amount of arrays it takes would be astronomical.) Through working with some individuals on this forum, we have figured out how to generate all of the types of scores that I need. We are not trying to come up with the user specific error informaiton.

I knew this portion was going to be rather in depth, but what I am currently facing is a little overwhelming. My scenario is outlined below. My main question at the end of all of this, is there a better way to do this?

The audit form consists of 55 fields. Each field has at minimum 4 options, and at a maximum 9 options for a single outcome. The options consist of "Pass", "FYI", "FYIP", and 1 of 76 available defect codes. Defect codes are along the lines of DC01A. DC stands for the defect code, 01 would be the general error, and the letter at the end stands for the specific type of error generated. The end state goal is to generate the top 3 errors for a single employee at the monthly level and the top 3 errors for the project at both a monthly and a weekly level. In order to do this, every field needs to be evaluated for every answer.



There are two way I can think of to go with this, both suck.

The first method: 661 queries
Query every field for every option for every time frame. This would include creating three sets of queries for all 55 fields. The first set of queries would include one to sum the passes, one for FYI, one for FYIP, and one for each defect code at an employee level on a monthly time from. The second set would to the same at the project level for a monthly time frame, and a third for weekly time frame. Quickly doing the math here, 3 sets of 79 queries (76 defect codes, pass, fyi, fyip) equates to 237 queries for each field. Realistically, it would be this much, that number is coming later. Across 55 fields, this equates to 13,035 queries. However, realistically looking at this, 48 of the 55 fields have only 1 defect option to chose from which equates to 4 queries. So, 3 sets of queries running 4 individual queries each on 48 fields equals (48*12) 576 queries. Rather the break down the remaining fields, the short version is that the other 7 fields add 85 queries for a total of 661 queries.

The second method: 228 queries
Move all field results to a single table that seperates the fields by their duplicate primary key (an identifying number and the audit field) and then give the result of the test. This is the same method I use to generate the scores for each audit. This would require building queries to search for every defect code at the 3 different scopes we are looking at. 76*3= 228 queries. Much less, but still not a reasonable solution.

Again, is there a better way to do this? I'm still new to access and very much open to feed back and alternative suggestions.

[orange]

It might be clearer for the reader if you could tell us about your tables and relationships. A jpg of your relationships window could help.
reader.
Your project number of queries seems excessive, but you know your data and requirements better than any

[rpeare]

the 55 fields reminds me of, I think, one of your other posts when people were suggesting you try to normalize your table structure and it doesn't look like you took that advice. SO I guess my main question is.. do each of your 55 audit categories ALWAYS appear in the same column, or can category 1 appear anywhere from your 1st to 55th column? I do not believe you are going to require hundreds of queries, you will likely have union queries to artificially normalize your table then perform functions on that union query to get your reporting results. I do not know the upper limit of union queries but I suspect you will be ok.

so let's say your table is like this:

tblTest
EmployeeID  OptionIDAudit1  ErrorIDAudit1  OptionIDAudit1  ErrorIDAudit1 ----> remaining audit fields
1           FYI             DC01A          FYIP           DC02A

now let's say you had a form where you had selected your employee named frmTestForm and on that form was a field called EmployeeID your union query would look like:

SELECT EmployeeID, OptionIDAudit1 as OptionLabel, ErrorIDAudit1 as ErrorLabel FROM tblTest WHERE (EmployeeID) = [Forms]![frmTestForm]![EmployeeID])
UNION ALL
SELECT EmployeeID, OptionIDAudit2 as OptionLabel, ErrorIDAudit2 as  ErrorLabel FROM tblTest WHERE (EmployeeID) =  [Forms]![frmTestForm]![EmployeeID])
UNION ALL
....

once you have the union query built you should be able to summarize it however you want.

[Alhassani]

rpeare,

You are correct, the 55 fields is reminiscent of a problem I previously had. The difference there was that I was trying to look at records as a whole. Since then, I have restructured the entire database to reflect a normalized structure so now instead of looking at rows (like you would in excel) I am looking at the individual columns (like how access and SQL is designed to work). The problem with the 55 fields this time is not in structure, it is in sheer magnitude of what I am trying to dig through to get usable information.

I will play with the union queries you suggested and see if I can get it to work. I'll be back on the forum on Monday, and I'll let you know if I was able to work it or not. Thanks for the help rpeare!

[rpeare]

it's not normalized if you still have 55 fields in a single record that all represent the same thing (outcome category for an audit) along with a defect code, in essence (since you didn't say my example wasn't what you had) you have 110 columns in a table instead of 2 for each audit category/result pair.

[Alhassani]

rpeare,

I think you are still confusing what I had earlier with what I have now. The example you gave for table set up is incorrect. The problem previously was solved by using the method you mentioned in your last post. I turned the 55 audit columns into 2 audit columns to consolidate those results and then calculate those results to provide a score for the audit as a whole. That is not what I am doing here.

For the initial part of this problem, all other audit columns are irrelevant. In this situation, I am looking only at the individual column. I want the individual column to be seperated by employee and month first, seperated by project and week second, and then project and month last. That would be 3 seperate queries on an individual column. For example, if an employee receives 5 audits in a week (which is the requirement), we'll say they received 4 scores of "Pass" and 1 score of "DC89D" in audit field 1. The score for that field would be 80% on the week for the first query. The table is already set up to do this. The second part of this problem would be to then consolidate all those scores in the manner you suggested above to return lowest 3 or 5 values for areas the employee misses consistently. And, then again for the other two types of queries.

What I am asking in the two options laid out above is if there is an easier way to do this rather than generating hundreds of queries. Even if done like I just listed, it would still require 3 queries per column for (155*3) 465 queries. Then additional queries would have to be run for each defect code to identify specific problems bringing the total to 541 queries. They could be run in either a single macro or a macro for each demographic we are looking at. If the answer is no, then I'll start writing the queries now. I'm just curious because its a lot of typing and error checking for 1 person to do.

Given this set-up, are union queries still a viable option to experiment with?

[rpeare]

you shouldn't need a bazillion queries to do this.

Can you give an example of your raw data and what you want the OUTPUT to look like

even better would be a sample db with some sample data in it.

[Alhassani]

As requested, sample database is attached. I deleted all but 5 audit fields to make it more simple to grasp.

The Archive_AuditDb table is what everything will be pulled from.

Query Results 1: Employee weekly. This is what the first query I talked about would show. It is the summary of the employees performance for the week by audit field.

Query Results 2: Project weekly. This is the second query. It shows how the project as a whole performed in each audit category.

Query Results 3: Project monthly. Looks the same as query 2, but only because i provided 1 week of sample data per month.

I know for the second half of what I am trying to do, I will have to make another table that run all of these scores into 2 columns, and then returns the bottom scores. I have the template for that and expect it should be easy since I already did it once in the main database. SampleDb.zip

[rpeare]

yeah, this isn't normalized

So let's just work with the structure you have

This is the data you have in your table Archive_AuditDB

Week	Employee	Audit Field 1	Audit Field 2	Audit Field 3	Audit Field 4	Audit Field 5
1214	Dwayne Wade	FYI	FYI	Pass	Pass	Pass
1214	Dwayne Wade	Pass	Pass	Pass	Pass	Pass
1214	Dwayne Wade	DC24A	DC24J	DC24B	DC24C	DC24E

This is how the same set of data is summarized in your table Query Result 1: Employee Week

Week	RMs	Total Audits	Audit Field 1	Audit Field 2	Audit Field 3	Audit Field 4	Audit Field 5
1214	Dwayne Wade	3	80%	80%	60%	100%	100%

I am assuming that this is what you want your end result to be.

So how are you calculating these values

If anything but a DC value is a 'pass' wouldn't your percentages be 66% across the board

Or is this where you intend to show your defect code and the percentages are supposed to show your top x many errors?

[Alhassani]

The percentages weren't exact. I just randomly typed in numbers to show what it could look like.

How it is calculated is dependant on the purpose of the query. Any queries to reflect an employee's scores would count Pass, FYI, and FYIP as a 'pass'. Any queries to reflect project scores would only count Pass and FYI as a 'pass'.

I'm not really looking at defect codes just yet, that will be the next set of queries. Right now I just want the scores.

Again, I've really tried to normalize the structure here. For all intents and purposes, all I am doing is applying filters to specific columns and then performing a calculation on the records that are left. What am I missing about this normalized structure thing? Luckily, I'm at a point where I think I can fix it without throwing too much off.

[rpeare]

Ok I added this table to your example database:

AuditCode	PassEmployee	PassProject
DC24A	No	No
DC24B	No	No
DC24C	No	No
DC24D	No	No
DC24E	No	No
DC24F	No	No
DC24G	No	No
DC24H	No	No
DC24J	No	No
DC24K	No	No
FYI	Yes	Yes
FYIP	Yes	No
Pass	Yes	Yes

The PASSEMPLOYEE and PASSPROJECT fields are yes/no fields, not text values

Then I ran this query:

SELECT Archive_AuditDb.Week, Archive_AuditDb.Employee, Sum(IIf([tblcodes_audit1]![passemployee]=-1,1,0))/Count([dcn]) AS Audit1Pct, Sum(IIf([tblcodes_audit2]![passemployee]=-1,1,0))/Count([dcn]) AS Audit2Pct, Sum(IIf([tblcodes_audit3]![passemployee]=-1,1,0))/Count([dcn]) AS Audit3Pct, Sum(IIf([tblcodes_audit4]![passemployee]=-1,1,0))/Count([dcn]) AS Audit4Pct, Sum(IIf([tblcodes_audit5]![passemployee]=-1,1,0))/Count([dcn]) AS Audit5Pct
FROM ((((Archive_AuditDb LEFT JOIN tblCodes AS tblCodes_Audit1 ON Archive_AuditDb.[Audit Field 1] = tblCodes_Audit1.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit2 ON Archive_AuditDb.[Audit Field 2] = tblCodes_Audit2.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit3 ON Archive_AuditDb.[Audit Field 3] = tblCodes_Audit3.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit4 ON Archive_AuditDb.[Audit Field 4] = tblCodes_Audit4.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit5 ON Archive_AuditDb.[Audit Field 5] = tblCodes_Audit5.AuditCode
GROUP BY Archive_AuditDb.Week, Archive_AuditDb.Employee;

This is the EMPLOYEE WEEK query

I ran this query:

SELECT Archive_AuditDb.Week, Sum(IIf([tblcodes_audit1]![passproject]=-1,1,0))/Count([dcn]) AS Audit1Pct, Sum(IIf([tblcodes_audit2]![passproject]=-1,1,0))/Count([dcn]) AS Audit2Pct, Sum(IIf([tblcodes_audit3]![passproject]=-1,1,0))/Count([dcn]) AS Audit3Pct, Sum(IIf([tblcodes_audit4]![passproject]=-1,1,0))/Count([dcn]) AS Audit4Pct, Sum(IIf([tblcodes_audit5]![passproject]=-1,1,0))/Count([dcn]) AS Audit5Pct
FROM ((((Archive_AuditDb LEFT JOIN tblCodes AS tblCodes_Audit1 ON Archive_AuditDb.[Audit Field 1] = tblCodes_Audit1.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit2 ON Archive_AuditDb.[Audit Field 2] = tblCodes_Audit2.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit3 ON Archive_AuditDb.[Audit Field 3] = tblCodes_Audit3.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit4 ON Archive_AuditDb.[Audit Field 4] = tblCodes_Audit4.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit5 ON Archive_AuditDb.[Audit Field 5] = tblCodes_Audit5.AuditCode
GROUP BY Archive_AuditDb.Week;

This is the PROJECT WEEK query

I ran this query:

SELECT MonthName(DatePart("m",[audit dtg])) AS AuditMonth, Sum(IIf([tblcodes_audit1]![passproject]=-1,1,0))/Count([dcn]) AS Audit1Pct, Sum(IIf([tblcodes_audit2]![passproject]=-1,1,0))/Count([dcn]) AS Audit2Pct, Sum(IIf([tblcodes_audit3]![passproject]=-1,1,0))/Count([dcn]) AS Audit3Pct, Sum(IIf([tblcodes_audit4]![passproject]=-1,1,0))/Count([dcn]) AS Audit4Pct, Sum(IIf([tblcodes_audit5]![passproject]=-1,1,0))/Count([dcn]) AS Audit5Pct
FROM ((((Archive_AuditDb LEFT JOIN tblCodes AS tblCodes_Audit1 ON Archive_AuditDb.[Audit Field 1] = tblCodes_Audit1.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit2 ON Archive_AuditDb.[Audit Field 2] = tblCodes_Audit2.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit3 ON Archive_AuditDb.[Audit Field 3] = tblCodes_Audit3.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit4 ON Archive_AuditDb.[Audit Field 4] = tblCodes_Audit4.AuditCode) LEFT JOIN tblCodes AS tblCodes_Audit5 ON Archive_AuditDb.[Audit Field 5] = tblCodes_Audit5.AuditCode
GROUP BY MonthName(DatePart("m",[audit dtg]));

This is the PROJECT MONTH query

[Alhassani]

rpeare,

Thank you very much, this is great. I understand almost everything that is going on in the SQL statements.

Can you explain a little about the table you added? My first thought was that this was populated to provide answers that correlate to DCNs. However, looking at it a little bit further I'm thinking it is a values table used for reference in the calculations. Trying to figure out what its purpose is so I can extend the codes to encompass all the fields. Thanks!

[rpeare]

The code table (you said you had something similar already) I am using to determine what constitutes a 'pass' because you have 2 different sets of 'pass' values. One set for employee based calculations, one for project based calculations. You could do the same thing with your existing code table by adding the two fields (or other similar mechanism) and substituting my table for your own.

the easiest thing to do to see what's going on is to create the same table with the same name with the same values in it in your version of your sample database, then just cut and paste the SQL into a query window to see how it's put together.

[Alhassani]

I started playing with it and figured out what your intent was. I have added the two columns to the existing table I have. I then pasted the SQL into word, replaced all of the tblcode with the correct table name, and then pasted into a new SQL statement in the main database. However, when I run the query in the sample database it is instant. When I run the same query (adjusted) in the main database it asks for the values in all of the columns, and then says "running query" in the bottom. The program is still technically "responding", but had been "running query" for over 20 minutes now.

[rpeare]

If it's prompting you for a value using the query I gave you then you have got another field in there that it doesn't know how to interpret. My suggestion is to get the query working in your sample database first. Then you can look at the design view, see how the table's are linked and re-create it in your actual database.