Multiple Query Returns Incorrect Totals (1 Viewer)

[scuddersm]

So I am running a query against a table to return the total number of records within the table, this number changes following data imports.

Viewing the table there are 550 records.

Running just the query against the table returns a 550 count, but
Running the query after associating it with these other queries returns a count 654 records.

Why does the record count of the table keep increasing?

[SELECT Count(tblMasterFile.Count) AS Assigned, Count(qryPFTTrained.[PFT Trained]) AS PFTComplete, Count(qryCFTTrained.[CFT Trained]) AS CFTComplete, Count(qryRIFLE.[Rifle Untrained]) AS NoRifle, Count(qryRIFLETrained.[Rifle Trained]) AS Rifle
FROM (((tblMasterFile LEFT JOIN qryPFTTrained ON tblMasterFile.EDIPI = qryPFTTrained.EDIPI) LEFT JOIN qryCFTTrained ON tblMasterFile.EDIPI = qryCFTTrained.EDIPI) LEFT JOIN qryRIFLE ON tblMasterFile.EDIPI = qryRIFLE.EDIPI) LEFT JOIN qryRIFLETrained ON tblMasterFile.EDIPI = qryRIFLETrained.EDIPI;]

Thanks for any help.

Scott

 

[plog]

Why does the record count of the table keep increasing?

Most likely you have duplicate values in one of the tables on which you are joining. Here's a simplified example:

A
ID, Cost
1, 4
2, 1
2, 3
3, 1
2, 5
3, 7

B
ID, Sales
1, 1
2, 3
2, 6
3, 4
4, 2

If you JOIN A to B on their ID fields you will get this many records for each ID:

1 -> 1 record (1x1)
2 -> 6 records (3x2)
3 -> 2 records (2x1)
4 -> 0 records (0x1)

The 3 records for ID=2 in A find both of the ID=2 records in B resulting in 6 records. The number of records is a product of the records in the first table times the records in the second table that match. Throw in more tables with more duplicates and you get more results.

 

[plog]

Looking over your post again, it looks like the big picture issue is your method to begin with. I don't think you are going to be able to accomplish what you want in one query. Most likely this is going to take a subquery or two. Possibly a UNION.

Could you better illustrate what you want to occur? Post sample data like I did, then also post what you expect your results to be based on that sample data.

 

[MarkK]

It's a one-to-many relationship so one row in the parent table links to many rows in the child table. As a result, the count in the final query--depending on your constraints--is a count of the child rows.

 

[scuddersm]

I have one table that contains all of our assigned employees.
Other training event tables have Employee ID and training dates.

Looking to develop Trained and Untrained percentages based upon queries to populate to a dashboard on the main menu.

tblMasterList
ID Name EmployeeID
1 JOE 123
2 JIM 456
3 JOHN 789
4 JACK 147
5 JOSE 258

tblTraining1
ID EmployeeID DateofTraining
1 123 1/30/17
2 456 1/30/17
3 147 1/30/17
4 258 1/30/17
5 123 2/1/17

I created a query that creates a table of maximum date for each EmployeeID, as they may take training more than once, but we are only concerned with the latest date.

I also created queries that return those that are trained and those that aren't based on time line requirement for each training.

What I am having trouble with is generating a query that returns the percentage of trained based upon the number of total records in tblMasterList and total records returned from the query that returns those with current training.

Hopefully this makes sense.

I can do this with a single relationship using a total count query, but I am trying to combine multiple results into one query from multiple table relationships.

 

[scuddersm]

SELECT Count(tblMasterFile.EDIPI) AS Assigned, Count(qryATTrained.EDIPI) AS ATTrained, [ATTrained]/[Assigned] AS ATPct, Count(qryEOTrained.EDIPI) AS EOTrained, [EOTrained]/[Assigned] AS EOPct, Count(qryChamberTrained.EDIPI) AS ChamberTrained, [ChamberTrained]/[Assigned] AS ChamberPct, Count(qryHazingTrained.EDIPI) AS HazingTrained, [HazingTrained]/[Assigned] AS HazingPct
FROM (((tblMasterFile LEFT JOIN qryATTrained ON tblMasterFile.EDIPI = qryATTrained.EDIPI) LEFT JOIN qryEOTrained ON tblMasterFile.EDIPI = qryEOTrained.EDIPI) LEFT JOIN qryChamberTrained ON tblMasterFile.EDIPI = qryChamberTrained.EDIPI) LEFT JOIN qryHazingTrained ON tblMasterFile.EDIPI = qryHazingTrained.EDIPI;

This is where the records get multiplied and I lose validity.

I can create 25 or so individual queries to get the results, but I was hoping for an all in one solution or a least close as possible to one.

 

[plog]

Other training event tables have Employee ID and training dates

As in multiple tables? Like named with numeric suffixes? tblTraining1, tblTraining2, tblTraining3...etc? You've set up your tables wrong then. When you do that, you are essentially storing values in table names at that point. Instead the value that is in the table name should go into a field.

That means you need 1 table for training, it would have the same structure as the existing tables, but with one additional field--to hold which type of training the record is for:

tblTraining
ID, EmployeeID, DateofTraining, TrainingType
1, 123, 1/30/17, 1
2, 456, 1/30/2017, 1
3, 147, 1/30/2017, 1
...
17, 123, 2/1/2017, 2
18, 999, 1/30/2017, 2
...

Instead of storing that 1, 2, 3... in the table name, it goes into a field with the record. You do that and I am certain the result you want to obtain becomes simple.

 

[scuddersm]

The training types and information that is stored in the current training tables is too diverse to fit into one table, unless its un-normalized. As it stands I only have 4 training tables (normalized), which contain all of the training information, plus an employee table (normalized), and a few Maximum Value tables (normalized) for each of the training types (26 of them).

I will try to see if a union will work after I fix some of the relationships.
As it stands I might have to create a lot more queries to get my results.

 

[The_Doc_Man]

If your problem is that the training for type 1 doesn't map well with the training for type 2 (or 3 or 4) then you have a design flaw and no, your tables are NOT normalized. But the direct answer to your question is that when you do a LEFT JOIN (or RIGHT JOIN, for that matter) between two tables and the relationship between the tables is many-to-one, then OF COURSE you are going to get more records.

You wanted a list based on your EDIPI but it isn't based that way once you do the JOIN. Your table's count should now match whichever of those queries has the greatest population. And, given that left-join linking in that manner potentially could return nulls for some of the joins yet still participate overall, the count is ACTUALLY going to be some complex matrix that shows records whether they overlap or not. Reading the nested JOIN, I can't tell you what would happen, but if you want to determine the origin of the count you actually saw, add up the number of records in each of the four training detail tables and see if THAT matches the count.

I might recommend that you have one table for the employee list and ONE table for all training details, some fields of which will be common to all types and some of which will not. THEN turn this problem on its head. When you want to see details only for training 1, have a query that includes a WHERE clause to show you only the given training type - and only the relevant fields. You really don't care if a few fields are empty for some records as long as ALL training detail records are in the same table... because then you could do summations with group-by or other computations on a single table and training type. Or a cross-tab query, perhaps.

 

[Galaxiom]

The training types and information that is stored in the current training tables is too diverse to fit into one table, unless its un-normalized.

Maybe consider the structure I outlined in this post.

It supports adding new kinds of training and details without having to add fields.

 

[scuddersm]

OK. New plan same result.
I have 2 queries that return the trained and untrained records from their corresponding tables. I did not joining them, but I am still receiving a sum of the queries records as a result. I am only trying to return the count of the number of records returned by each query separately.

SELECT Count(qryAT.EDIPI) AS ATCount, Count(qryATTrained.EDIPI) AS TrainedCnt FROM qryAT, qryATTrained;

Is there any way to return the number of records in each query?

Scott

 

[MarkK]

Run a DCount() and save it somewhere. Consider code like...

dim countofA as single
dim countofB as single

countofA = DCount("*", "QueryA")
countofB = DCount("*", "QueryB")

 

[scuddersm]

Thanks, I will try that out. I've never used DCount(), but I am sure that I can figure something out.