Union Query

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

How would I make these two Queries into an Union Query in a strSQL string?

Here is what I have:

***Query 1***
strSQL = "SELECT [Unique ID], Sum([Amount]) AS [TotalDonation], [Entrydate]
"
strSQL = strSQL & "FROM ThankYouDetails As TYD "
strSQL = strSQL & "WHERE DateValue([ThankYouDetails].[Entrydate]) >=#"
strSQL = strSQL & DateValue(dtmMyStartDate) & "# And "
strSQL = strSQL & "DateValue([ThankYouDetails].[Entrydate]) <=#"
strSQL = strSQL & DateValue(dtmMyEndDate) & "# "
strSQL = strSQL & "GROUP BY [Unique ID], [Entrydate] "
strSQL = strSQL & "HAVING (Sum([ThankYouDetails].[Amount]) > 0) "
strSQL = strSQL & "ORDER BY [Entrydate] DESC;"

***Query 2***
strSQL = "SELECT [Unique ID], Sum([Amount]) AS [Total Donation], [Entrydate]
"
strSQL = strSQL & "FROM [Donation details] As DD"
strSQL = strSQL & "WHERE DateValue([Donation details].[Entrydate]) >=#"
strSQL = strSQL & DateValue(dtmMyStartDate) & "# And "
strSQL = strSQL & "DateValue([Donation details].[Entrydate]) <=#"
strSQL = strSQL & DateValue(dtmMyEndDate) & "# "
strSQL = strSQL & "GROUP BY [Unique ID], [Entrydate] "
strSQL = strSQL & "HAVING (Sum([Donation details].Amount)) > 0) "
strSQL = strSQL & "ORDER BY [Entrydate] DESC;"


How would I write this as strSQL string as a Union Query?

-TFTH
-Bryan
 
Bryan said:
Hello,

How would I make these two Queries into an Union Query in a strSQL string?

Here is what I have:

***Query 1***
strSQL = "SELECT [Unique ID], Sum([Amount]) AS [TotalDonation], [Entrydate]
"
strSQL = strSQL & "FROM ThankYouDetails As TYD "
strSQL = strSQL & "WHERE DateValue([ThankYouDetails].[Entrydate]) >=#"
strSQL = strSQL & DateValue(dtmMyStartDate) & "# And "
strSQL = strSQL & "DateValue([ThankYouDetails].[Entrydate]) <=#"
strSQL = strSQL & DateValue(dtmMyEndDate) & "# "
strSQL = strSQL & "GROUP BY [Unique ID], [Entrydate] "
strSQL = strSQL & "HAVING (Sum([ThankYouDetails].[Amount]) > 0) "
Click to expand...

- delete the ORDER BY clause from the 1st query

strSQL = strSQL & " UNION "
***Query 2***
Click to expand...

- just concat the string strSQL to the 2nd query

strSQL = strSQL & "SELECT [Unique ID], Sum([Amount]) AS [Total
Donation], [Entrydate] "
strSQL = strSQL & "FROM [Donation details] As DD"
strSQL = strSQL & "WHERE DateValue([Donation details].[Entrydate]) >=#"
strSQL = strSQL & DateValue(dtmMyStartDate) & "# And "
strSQL = strSQL & "DateValue([Donation details].[Entrydate]) <=#"
strSQL = strSQL & DateValue(dtmMyEndDate) & "# "
strSQL = strSQL & "GROUP BY [Unique ID], [Entrydate] "
strSQL = strSQL & "HAVING (Sum([Donation details].Amount)) > 0) "
strSQL = strSQL & "ORDER BY [Entrydate] DESC;"


How would I write this as strSQL string as a Union Query?
Click to expand...

If the column EntryDate and the variables dtmMyStartDate and
dtmMyEndDate are DateTime data types you can simplify the query by
replacing this:
strSQL = strSQL & "WHERE DateValue([ThankYouDetails].[Entrydate]) >=#"
strSQL = strSQL & DateValue(dtmMyStartDate) & "# And "
strSQL = strSQL & "DateValue([ThankYouDetails].[Entrydate]) <=#"
strSQL = strSQL & DateValue(dtmMyEndDate) & "# "
Click to expand...

with this:

strSQL = strSQL & "WHERE EntryDate Between #" & dtmMyStartDate & "# "
strSQL = strSQL & "And #" & dtmMyEndDate & "# "

Use the Between instead of the >= & <= expressions.
 
Back
Top