Combining 2 queries..I am loosing my mind...

D

dwaynesworld

I have the following query based on 2 queries:

SELECT qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt],
Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants],
Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs],
Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs], Sum(Nz([Total ISD
Hours],0)) AS [Total ISD Hrs], Sum(Nz([Total Admin Hours],0)) AS [Total Admin
Hrs]
FROM (qryAdjustedAUSGrandTotals LEFT JOIN qryAdminData ON
qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name]) LEFT
JOIN qryISDData ON qryAdjustedAUSGrandTotals.[Course Name] =
qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];

My totals from qryAdminData and qryISDData are very large and I cannot seem
to correct this. Help!!!
 
C

Chris2

dwaynesworld said:
I have the following query based on 2 queries:

SELECT qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt],
Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants],
Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs],
Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs], Sum(Nz([Total ISD
Hours],0)) AS [Total ISD Hrs], Sum(Nz([Total Admin Hours],0)) AS [Total Admin
Hrs]
FROM (qryAdjustedAUSGrandTotals LEFT JOIN qryAdminData ON
qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name]) LEFT
JOIN qryISDData ON qryAdjustedAUSGrandTotals.[Course Name] =
qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];

My totals from qryAdminData and qryISDData are very large and I cannot seem
to correct this. Help!!!
Click to expand...

dwaynesworld,

Here is your query, straightened up a bit.

SELECT qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt]
,Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants]
,Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs]
,Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs]
,Sum(Nz([Total ISD Hours],0)) AS [Total ISD Hrs]
,Sum(Nz([Total Admin Hours],0)) AS [Total Admin Hrs]
FROM (qryAdjustedAUSGrandTotals
LEFT JOIN
qryAdminData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name])
LEFT JOIN
qryISDData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];


There is nothing obvious in the SQL that I can see.

My guess is that the data in the tables is not correct.

1 -- Please provide the SQL of all queries involved.
2 -- Please provide the relevant table structures, including keys and indexes, for all
tables involved.
3 -- Please provide relevant sample data from the tables involved.
4 -- Please provide a sample chart of incorrect results.
5 -- Please provide a sample chart of the desired results.


Comments:

I see that fully qualified column names are being used for some columns, but not all.

I recommend that they be used throughout. And if you're going that far, using properly
abbreviated table aliases would help on readability, too.

? represents table aliases that could not be assigned because the origin of the column
could not be determined. They are meant to be replaced by actual table aliases.

I also recommend that you eliminate spaces (or special characters) in all column names (or
table, form, control, report, macro, or module names, as well). This eliminates all
brackets.

SELECT GT1.CourseName
,GT1.SumOfAUSCourseCount AS AUSCourseCnt
,Sum(Nz(?.SumOfTotalParticipants,0)) AS TotalPrticipants
,Sum(Nz(?.SumOfFacilitatorHours,0)) AS FacilitatorHrs
,Sum(Nz(?.SumOfPrepWrapHours,0)) AS PrepWrapHrs
,Sum(Nz(?.TotalISDHours,0)) AS TotalISD Hrs
,Sum(Nz(?.TotalAdminHours,0)) AS TotalAdminHrs
FROM (qryAdjustedAUSGrandTotals GT1
LEFT JOIN
qryAdminData AD1
ON GT1.CourseName = AD1.CourseName)
LEFT JOIN
qryISDData ID1
ON GT1.CourseName = ID1.ProjectName
GROUP BY GT1.CourseName
,GT1.SumOfAUSCourseCount;

Voila, a more readable query.


Sincerely,

Chris O.
 
D

dwaynesworld

Thanks for the response Chris. I figured out what was wrong. One of the
tables contained SUM data and the other two did not. Thanks!!!

Chris2 said:
dwaynesworld said:
I have the following query based on 2 queries:

SELECT qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt],
Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants],
Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs],
Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs], Sum(Nz([Total ISD
Hours],0)) AS [Total ISD Hrs], Sum(Nz([Total Admin Hours],0)) AS [Total Admin
Hrs]
FROM (qryAdjustedAUSGrandTotals LEFT JOIN qryAdminData ON
qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name]) LEFT
JOIN qryISDData ON qryAdjustedAUSGrandTotals.[Course Name] =
qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];

My totals from qryAdminData and qryISDData are very large and I cannot seem
to correct this. Help!!!
Click to expand...

dwaynesworld,

Here is your query, straightened up a bit.

SELECT qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt]
,Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants]
,Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs]
,Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs]
,Sum(Nz([Total ISD Hours],0)) AS [Total ISD Hrs]
,Sum(Nz([Total Admin Hours],0)) AS [Total Admin Hrs]
FROM (qryAdjustedAUSGrandTotals
LEFT JOIN
qryAdminData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name])
LEFT JOIN
qryISDData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];


There is nothing obvious in the SQL that I can see.

My guess is that the data in the tables is not correct.

1 -- Please provide the SQL of all queries involved.
2 -- Please provide the relevant table structures, including keys and indexes, for all
tables involved.
3 -- Please provide relevant sample data from the tables involved.
4 -- Please provide a sample chart of incorrect results.
5 -- Please provide a sample chart of the desired results.


Comments:

I see that fully qualified column names are being used for some columns, but not all.

I recommend that they be used throughout. And if you're going that far, using properly
abbreviated table aliases would help on readability, too.

? represents table aliases that could not be assigned because the origin of the column
could not be determined. They are meant to be replaced by actual table aliases.

I also recommend that you eliminate spaces (or special characters) in all column names (or
table, form, control, report, macro, or module names, as well). This eliminates all
brackets.

SELECT GT1.CourseName
,GT1.SumOfAUSCourseCount AS AUSCourseCnt
,Sum(Nz(?.SumOfTotalParticipants,0)) AS TotalPrticipants
,Sum(Nz(?.SumOfFacilitatorHours,0)) AS FacilitatorHrs
,Sum(Nz(?.SumOfPrepWrapHours,0)) AS PrepWrapHrs
,Sum(Nz(?.TotalISDHours,0)) AS TotalISD Hrs
,Sum(Nz(?.TotalAdminHours,0)) AS TotalAdminHrs
FROM (qryAdjustedAUSGrandTotals GT1
LEFT JOIN
qryAdminData AD1
ON GT1.CourseName = AD1.CourseName)
LEFT JOIN
qryISDData ID1
ON GT1.CourseName = ID1.ProjectName
GROUP BY GT1.CourseName
,GT1.SumOfAUSCourseCount;

Voila, a more readable query.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

dwaynesworld said:
I have the following query based on 2 queries:

SELECT qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt],
Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants],
Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs],
Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs], Sum(Nz([Total ISD
Hours],0)) AS [Total ISD Hrs], Sum(Nz([Total Admin Hours],0)) AS [Total Admin
Hrs]
FROM (qryAdjustedAUSGrandTotals LEFT JOIN qryAdminData ON
qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name]) LEFT
JOIN qryISDData ON qryAdjustedAUSGrandTotals.[Course Name] =
qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name],
qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];

My totals from qryAdminData and qryISDData are very large and I cannot seem
to correct this. Help!!!
Click to expand...

dwaynesworld,

Here is your query, straightened up a bit.

SELECT qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count] AS [AUS Course Cnt]
,Sum(Nz([SumOfTotal Participants],0)) AS [Total Participants]
,Sum(Nz([SumOfFacilitator Hours],0)) AS [Facilitator Hrs]
,Sum(Nz([SumOfPrep/Wrap Hours],0)) AS [Prep/Wrap Hrs]
,Sum(Nz([Total ISD Hours],0)) AS [Total ISD Hrs]
,Sum(Nz([Total Admin Hours],0)) AS [Total Admin Hrs]
FROM (qryAdjustedAUSGrandTotals
LEFT JOIN
qryAdminData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryAdminData.[Course Name])
LEFT JOIN
qryISDData
ON qryAdjustedAUSGrandTotals.[Course Name] = qryISDData.[Project Name]
GROUP BY qryAdjustedAUSGrandTotals.[Course Name]
,qryAdjustedAUSGrandTotals.[SumOfAUS Course Count];


There is nothing obvious in the SQL that I can see.

My guess is that the data in the tables is not correct.

1 -- Please provide the SQL of all queries involved.
2 -- Please provide the relevant table structures, including keys and indexes, for all
tables involved.
3 -- Please provide relevant sample data from the tables involved.
4 -- Please provide a sample chart of incorrect results.
5 -- Please provide a sample chart of the desired results.


Comments:

I see that fully qualified column names are being used for some columns, but not all.

I recommend that they be used throughout. And if you're going that far, using properly
abbreviated table aliases would help on readability, too.

? represents table aliases that could not be assigned because the origin of the column
could not be determined. They are meant to be replaced by actual table aliases.

I also recommend that you eliminate spaces (or special characters) in all column names (or
table, form, control, report, macro, or module names, as well). This eliminates all
brackets.

SELECT GT1.CourseName
,GT1.SumOfAUSCourseCount AS AUSCourseCnt
,Sum(Nz(?.SumOfTotalParticipants,0)) AS TotalPrticipants
,Sum(Nz(?.SumOfFacilitatorHours,0)) AS FacilitatorHrs
,Sum(Nz(?.SumOfPrepWrapHours,0)) AS PrepWrapHrs
,Sum(Nz(?.TotalISDHours,0)) AS TotalISD Hrs
,Sum(Nz(?.TotalAdminHours,0)) AS TotalAdminHrs
FROM (qryAdjustedAUSGrandTotals GT1
LEFT JOIN
qryAdminData AD1
ON GT1.CourseName = AD1.CourseName)
LEFT JOIN
qryISDData ID1
ON GT1.CourseName = ID1.ProjectName
GROUP BY GT1.CourseName
,GT1.SumOfAUSCourseCount;

Voila, a more readable query.


Sincerely,

Chris O.
Click to expand...

Thanks for the response Chris. I figured out what was wrong. One of the
tables contained SUM data and the other two did not. Thanks!!!

:
Click to expand...

dwaynesworld,

I am glad you were able to resolve the issue.


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM totals to high 0
What am I doing wrong? 8
COMBINING QUERIES INTO 1 3
combining queries into 1 8
Combining 3 queries 2
Combining 2 crosstab queries into 1 query: Help 1
Am I forcing the 0's correctly? 5
Query on 2 other queries 1

Top