Four Tables, 1 Query

**athyeh** · 08-21-2013, 02:47 PM

Hello All,

I have Four Tables:

Table Errors 1
Table Errors 2
Table Production 1

Table Production 2

The Four tables are fed through forms that are used by different processors and different processes.

How do I create a query that queries all Four Tables to find out:

[Date] [Operator] [Errors] [Process] [Production]
8/21/2013 Johnny 5 Apple Picking 8

**Dal Jeanis** · 08-21-2013, 03:16 PM

That depends on the relationships of the tables.

I'm guessing you'll be looking for a union of two queries -

Code:

   SELECT 
      E1.(field list), 
      P1.(field list) 
   FROM 
      [Table Errors 1] AS E1 
      INNER JOIN 
      [Table Production 1] AS P1
      ON E1.[somekeyfield] = P1.[somekeyfield]
UNION
   SELECT 
      E2.(field list), 
      P2.(field list) 
   FROM 
      [Table Errors 2] AS E2 
      INNER JOIN 
      [Table Production 2] AS P2 
      ON E2.[somekeyfield] = P2.[somekeyfield]
;

**athyeh** · 08-27-2013, 10:38 AM

Code:

SELECT qryStepOne.[Audit Date], qryStepOne.[LAN ID], Count(qryStepOne.[Loan Identifier]) AS [CountOfLoan Identifier]
FROM tblNames INNER JOIN qryStepOne ON tblNames.[LAN ID] = qryStepOne.[LAN ID]
GROUP BY qryStepOne.[Audit Date], qryStepOne.[LAN ID]
HAVING (((qryStepOne.[Audit Date])>=Format([Forms]![frmOpReports]![DateStart],"mm/dd/yyyy") And (qryStepOne.[Audit Date])<=Format([Forms]![frmOpReports]![DateTo],"mm/dd/yyyy")) AND ((qryStepOne.[LAN ID])=[Forms]![frmOpReports]![Combo38]))
UNION
SELECT tblMainDB.[Audit Date], tblMainDB.Operator, Count(tblMainDB.[Loan Identifier]) AS [CountOfLoan Identifier]
FROM tblNames INNER JOIN tblMainDB ON tblNames.[LAN ID] = tblMainDB.Operator
GROUP BY tblMainDB.[Audit Date], tblMainDB.Operator
HAVING (((tblMainDB.[Audit Date])>=Format([Forms]![frmOpReports]![DateStart],"mm/dd/yyyy") And (tblMainDB.[Audit Date])<=Format([Forms]![frmOpReports]![DateTo],"mm/dd/yyyy")) AND ((tblMainDB.Operator)=[Forms]![frmOpReports]![Combo38]))
UNION
SELECT Format([Time Added],"mm/dd/yyyy") AS Expr1, [D1-Prod-MainTable].Operator, Count([D1-Prod-MainTable].LID) AS CountOfLID
FROM [D1-Prod-MainTable]
GROUP BY Format([Time Added],"mm/dd/yyyy"), [D1-Prod-MainTable].Operator, [D1-Prod-MainTable].Process
HAVING (((Format([Time Added],"mm/dd/yyyy"))>=Format([Forms]![frmOpReports]![DateStart],"mm/dd/yyyy") And (Format([Time Added],"mm/dd/yyyy"))<=Format([Forms]![frmOpReports]![DateTo],"mm/dd/yyyy")) AND (([D1-Prod-MainTable].Operator)=[Forms]![frmOpReports]![Combo38]));

I have this.. Now how do I get them all on the same line, so for 08/14/2013 it shows errors from tblMainDB, tblExceptions, and production from the two prod tables:
[08/14/2013] [John] [5] [6] [7] [8]

Thanks!

**Dal Jeanis** · 08-27-2013, 06:10 PM

Okay, the first thing that looks problematic is that you are attempting to format your date fields in the query, rather than in the results. Not a good idea; even if it works it will do weird things to the sort orders. Use the Datepart() function instead, to get the date portion of a date/time field.

The second thing that looks problematic is that you're joining tblNames into two of those queries, and not using any fields from tblNames. Is that intentional?

The third thing that looks problematic (this one's a killer) is that you're grouping the third query by a field (PROCESS) that's not in the query.

I also very much prefer to alias the query and table names to much shorter nicknames, so I can read the SQL code easier.

So, here's your UNION query, as revised. I left in the tblNames table, but you should remove it unless you need it for some reason. (I assumed that [Time Added] is in the [D1-Prod-MainTable] table.)

Code:

SELECT 
    Q1.[Audit Date], 
    Q1.[LAN ID], 
    Count(Q1.[Loan Identifier]) AS [CountOfLoan Identifier] 
FROM 
   tblNames AS T1
   INNER JOIN 
   qryStepOne AS Q1
   ON T1.[LAN ID] = Q1.[LAN ID] 
GROUP BY Q1.[Audit Date], Q1.[LAN ID] 
HAVING ((Q1.[Audit Date]>=[Forms]![frmOpReports]![DateStart])
    AND (Q1.[Audit Date]<=[Forms]![frmOpReports]![DateTo]) 
    AND (Q1.[LAN ID])=[Forms]![frmOpReports]![Combo38])) 

UNION 

SELECT 
   T2.[Audit Date], 
   T2.Operator, 
   Count(T2.[Loan Identifier]) AS [CountOfLoan Identifier] 
FROM 
   tblNames AS T1
   INNER JOIN 
   tblMainDB AS T2
   ON T1.[LAN ID] = T2.Operator 
GROUP BY T2.[Audit Date], T2.Operator 
HAVING ((T2.[Audit Date]>=[Forms]![frmOpReports]![DateStart])
    AND (T2.[Audit Date]<=[Forms]![frmOpReports]![DateTo]) 
    AND (T2.[LAN ID])=[Forms]![frmOpReports]![Combo38])) 

UNION 

SELECT 
   DatePart(D1.[Time Added]),
   D1.Operator, 
   Count(D1.LID) AS CountOfLID 
FROM [D1-Prod-MainTable] AS D1
GROUP BY D1.[Time Added], D1.Operator
HAVING ((DatePart(D1.[Time Added])>=Format([Forms]![frmOpReports]![DateStart]) 
    AND (DatePart(D1.[Time Added])<=Format([Forms]![frmOpReports]![DateTo]) 
    AND (D1.Operator=[Forms]![frmOpReports]![Combo38]));

Let's call that QUery2Union.

Now, the question of how to deal with summing the dups - you can just run the results of that query through another GROUP BY query.

Code:

Select 
   Q2.[Audit Date], 
   Q2.[LAN ID], 
   Sum(Q2.[CountOfLoan Identifier])
From Query2Union as Q2
GROUP BY Q2.[Audit Date], Q2.[LAN ID]

This can all get done in one grand query. Also, you don't have to do the GROUP BY twice. Assuming that you didn't need the Name table JOINED in for some reason, that would look something like this:

Code:

SELECT ErrDate, ErrOper, Sum(ErrCount) as ErrCount
FROM 
(  SELECT Q1.[Audit Date] AS ErrDate, Q1.[LAN ID] As ErrOper, 1 AS ErrCount
   FROM qryStepOne AS Q1
   WHERE ((Q1.[Audit Date]>=[Forms]![frmOpReports]![DateStart])
      AND (Q1.[Audit Date]<=[Forms]![frmOpReports]![DateTo]) 
      AND (Q1.[LAN ID])=[Forms]![frmOpReports]![Combo38])) 
UNION 
   SELECT T2.[Audit Date] AS ErrDate, T2.Operator AS ErrOper, 1 AS ErrCount 
   FROM tblMainDB AS T2
   WHERE ((T2.[Audit Date]>=[Forms]![frmOpReports]![DateStart])
      AND (T2.[Audit Date]<=[Forms]![frmOpReports]![DateTo]) 
      AND (T2.[LAN ID])=[Forms]![frmOpReports]![Combo38])) 
UNION 
   SELECT DatePart(D1.[Time Added]) AS ErrDate, D1.Operator AS ErrOper, 1 AS ErrCount
   FROM [D1-Prod-MainTable] AS D1
   GROUP BY D1.[Time Added], D1.Operator
   HAVING ((DatePart(D1.[Time Added])>=Format([Forms]![frmOpReports]![DateStart]) 
      AND (DatePart(D1.[Time Added])<=Format([Forms]![frmOpReports]![DateTo]) 
      AND (D1.Operator=[Forms]![frmOpReports]![Combo38])); 
) 
GROUP BY ErrDate, ErrOper;

See how much cleaner that looks? It's air code, so I may have missed some parenthesis somewhere. If it doesn't work right off, remove the second and third queries that are UNIONed in, and get it working with only the first query. After that's working, add the UNION for the second query, then repeat for the third.

**athyeh** · 09-03-2013, 12:09 PM

Hi Dal,

Thank you very much for your reply, I've been working through it step-by-step and have encountered a problem that I can't seem to negotiate on the first segment of code:

Code:

UNION SELECT 
   DatePart(D1.[Time Added]),
   D1.Operator, 
   Count(D1.LID) AS CountOfLID
FROM [D1-Prod-MainTable] AS D1
GROUP BY D1.[Time Added], D1.Operator
HAVING ((DatePart(D1.[Time Added])>=Format([Forms]![frmOpReports]![DateStart]) 
    AND (DatePart(D1.[Time Added])<=Format([Forms]![frmOpReports]![DateTo]) 
    AND (D1.Operator=[Forms]![frmOpReports]![Combo38]))
;

I have managed to get everything up to work UNTIL that point.

In so doing, I have been looking at the results of the query and just wanted to confirm that all the results from 3 different tables will be one a single line in the results?

So:

Query Results will look exactly like this:

[Audit Date] [# of Error's from T1] [# of Error's from T2] [Prod #'s from T3] [Prod #'s from T4]
08/01/2013 5 6 7 8

Thank you very much for your assistance, truly appreciated.

**athyeh** · 09-05-2013, 07:19 AM

Don't abandon me Dal...

**Dal Jeanis** · 09-10-2013, 08:45 AM

Sorry, life's been quite hectic the last few weeks.

**Dal Jeanis** · 09-10-2013, 09:06 AM

Think about it this way -

UNION operates to merge two or more files that have the same layout into a single file

GROUP BY operates to aggregate together all the records in a file that have the same key. (in this case, a date)

If you have each Select put out a record containing a constant field with value 1, then you can easily SUM those 1's to get a count of the records.

Let's suppose we have three tables, A, B and C. If we do this, then we are merging three sets of results into a single UNION table.

Code:

   (SELECT A.Keyfield AS MyKey, 1 AS MyCount FROM A WHERE (conditionA) )
UNION
   (SELECT B.Keyfield AS MyKey, 1 AS MyCountFROM B  WHERE (conditionB) )
UNION
   (SELECT C.Keyfield AS MyKey, 1 AS MyCountFROM C  WHERE (conditionC) );

I've put parenthesis around each select - think of each select as a query or table in its own right, and the UNION operator as an operation that lumps the tables together into a single, new table or query.

TECHNICALLY, in a UNION we don't have to label any fields after the first table, only the field names in the first table can be used in the resulting UNION, but I'm including them in each SELECT because it will make a later example easier for you to understand.

Now we have a single result file that has a record for each desired record on each table, and a 1 in the Mycount field.

If we use a group by against that result table, like this -

Code:

SELECT MyKey, Sum(MyCount) AS MySum
FROM
(that whole union statement above)
GROUP BY MyKey;

then we'll get one summed record per key (which we'll assume is a date)

Code:

   MyKey      MySum
08/01/2013      12
08/02/2013       9
08/05/2013      33

But you really want something that keeps each type of Count separate, right? All we have to do is spread out the results from different tables into different Count fields, and for each file put zeros in the Count fields for the other files.

Code:

SELECT MyKey, Sum(MyCountA) AS MySumA, Sum(MyCountB) AS MySumB, Sum(MyCountC) AS MySumC
FROM 
 (
   (SELECT A.Keyfield AS MyKey, 1 AS MyCountA, 0 As MyCountB, 0 AS MyCountC FROM A WHERE (conditionA) )
 UNION
   (SELECT B.Keyfield AS MyKey, 0 As MyCountA, 1 As MyCountB, 0 As MyCountC FROM B WHERE (conditionB) )
 UNION
   (SELECT C.Keyfield AS MyKey, 0 As MyCountA, 0 As MyCountB, 1 As MyCountC FROM C WHERE (conditionC) )
 )
GROUP By MyKey;

resulting in something like this -

Code:

   MyKey      MySumA    MySumB    MySumC
08/01/2013       2         8        2      
08/02/2013       9         0        0      
08/05/2013       1         2       30

Does that make sense?

So, start by creating your layout for the final report, then code each SELECT to put zeroes in each column that it shouldn't affect, and put a constant 1 in the column that you want a count from. (Or put the value that you want to sum up, if you're not doing a count.)

For simplicity, readability and my own sanity, my preference is to output the individual records from the selects and put a single GROUP BY clause after the end of the UNION, against the final aggregate results table.

However, there may be valid reasons NOT to do that. Some are mandatory, like if the desired selection criteria are based upon the aggregate values... for example, table A should only give the results for operators who had more than 10 errors on a given day. Some are discretionary, like if the UNIONed source tables are on different places on a network, so it would be better practice to GROUP BY the results from each table, to save network traffic.

If nothing like those example cases is in play here, then I'd recommend getting rid of the table-level aggregation/GROUP BY/HAVING and switch to a WHERE clause for each table. Use the same conditions as the HAVING.

BY THE WAY, make sure your GROUP by is using only the "date" part of the Time Added field, or your results may be squirrely.

Code:

GROUP BY Datepate(D1.[Time Added]), D1.Operator

**Dal Jeanis** · 09-17-2013, 08:26 AM

Oh, dear. That should have been:

Originally Posted by Dal Jeanis

Code:

GROUP BY Datepart(D1.[Time Added]), D1.Operator

**athyeh** · 10-09-2013, 12:54 PM

Hi dal,

Appreciate the replies and input, I've been poring over this post for several weeks now. After further research I'm still at square one. Doesn't a UNION query "stack" results? So, multiple rows?

I'm looking to join the results of two queries together based on criteria: Date and Operator.

So one row: Date, Operator, Table1.Count, Table2. Count

Is this possible? Is Left Join, Right Join, Outter Join the answer?

**athyeh** · 10-09-2013, 01:12 PM

The table below is a resulit of this query:

Code:

SELECT ProdReport.Date, ProdReport.Month, qryIndQuality.Operator, ProdReport.LID, qryIndQuality.LID
FROM ProdReport INNER JOIN qryIndQuality ON ProdReport.Operator = qryIndQuality.Operator
GROUP BY ProdReport.Date, ProdReport.Month, qryIndQuality.Operator, ProdReport.LID, qryIndQuality.LID;

Date	Month	Operator	ProdReport.LID	qryIndQuality.LID
10/01/2013	10	John Smith	55	1
10/01/2013	10	John Smith	55	2
10/01/2013	10	John Smith	55	5
10/02/2013	10	John Smith	35	1
10/02/2013	10	John Smith	35	2
10/02/2013	10	John Smith	35	5
10/03/2013	10	John Smith	2	1
10/03/2013	10	John Smith	2	2
10/03/2013	10	John Smith	2	5
10/04/2013	10	John Smith	136	1
10/04/2013	10	John Smith	136	2
10/04/2013	10	John Smith	136	5
10/07/2013	10	John Smith	184	1
10/07/2013	10	John Smith	184	2
10/07/2013	10	John Smith	184	5
10/08/2013	10	John Smith	96	1
10/08/2013	10	John Smith	96	2
10/08/2013	10	John Smith	96	5
10/09/2013	10	John Smith	108	1
10/09/2013	10	John Smith	108	2
10/09/2013	10	John Smith	108	5

It is close but it is .... presenting the data incorrectly.

Query1:

Code:

SELECT Format([Print_Time],"mm/dd/yyyy") AS [Date], Month([Print_Time]) AS [Month], Print_Actions.Print_User AS Operator, Count(Print_Actions.Note_ID) AS LID
FROM Print_Actions
GROUP BY Format([Print_Time],"mm/dd/yyyy"), Month([Print_Time]), Print_Actions.Print_User, Print_Actions.Print_Code
HAVING (((Format([Print_Time],"mm/dd/yyyy"))>=Format(Forms!Reporting!From,"mm/dd/yyyy") And (Format([Print_Time],"mm/dd/yyyy"))<=Format(Forms!Reporting!To,"mm/dd/yyyy")) And ((Print_Actions.Print_User)=Forms!Reporting!OpAut) And ((Print_Actions.Print_Code)=2));

Produces:

Date	Month	Operator	LID
10/01/2013	10	John SMith	55
10/02/2013	10	John SMith	35
10/03/2013	10	John SMith	2
10/04/2013	10	John SMith	136
10/07/2013	10	John SMith	184
10/08/2013	10	John SMith	96
10/09/2013	10	John SMith	108

Query2:

Code:

SELECT tblMainDB.[Audit Date] AS [Date], Month([Audit Date]) AS [Month], tblMainDB.Operator, Count(tblMainDB.[Loan Identifier]) AS LID
FROM tblMainDB
GROUP BY tblMainDB.[Audit Date], Month([Audit Date]), tblMainDB.Operator
HAVING (((tblMainDB.[Audit Date])>=Format([Forms]![Reporting]![From],"mm/dd/yyyy") And (tblMainDB.[Audit Date])<=Format([Forms]![Reporting]![To],"mm/dd/yyyy")) AND ((tblMainDB.Operator)=[Forms]![Reporting]![OpAut]));

Produces:

Date	Month	Operator	LID
10/1/2013	10	John Smith	1
10/4/2013	10	John Smith	2
10/7/2013	10	John Smith	5
10/9/2013	10	John Smith	1

**athyeh** · 10-09-2013, 01:14 PM

How do I write a query that can create these results:

Date	Month	Operator	ProdReport.LID	qryIndQuality.LID
10/01/2013	10	a194688	55	1
10/02/2013	10	a194688	35
10/03/2013	10	a194688	2
10/04/2013	10	a194688	136	2
10/07/2013	10	a194688	184	5
10/08/2013	10	a194688	96
10/09/2013	10	a194688	108	1

**athyeh** · 10-09-2013, 01:35 PM

Ok,

Created a relationship using Relationship manager on Date and we are very, very close now.

Code:

SELECT ProdReport.Date, ProdReport.Operator, ProdReport.LID, qryIndQuality.LID
FROM ProdReport INNER JOIN qryIndQuality ON ProdReport.Date = qryIndQuality.Date
GROUP BY ProdReport.Date, ProdReport.Operator, ProdReport.LID, qryIndQuality.LID;

Produces this result:

Date	Operator	ProdReport.LID	qryIndQuality.LID
10/01/2013	John Smith	55	1
10/04/2013	John Smith	136	2
10/07/2013	John Smith	184	5
10/09/2013	John Smith	108	1

Now the question is: How do we get the query to show 0 for dates where John Smith didn't have any errors, but still had production?

**athyeh** · 10-09-2013, 01:56 PM

Thread solved. relationship manager, and edited Join Type.

Thank you for all your replies.

**Dal Jeanis** · 10-09-2013, 03:17 PM

Looks like you got it working by yourself!

Bet you learned stuff too. Great job!

Four Tables, 1 Query

Thread Tools

Four Tables, 1 Query

Overview of UNIONs and Merging Tables

Similar Threads

Query from 2 tables

Query records from multiple tables and include records not mentioned on other tables

1 query 2 tables help?

Trying this query from 2-Tables

Query on 3 tables

Posting Permissions