Combining two queries in code?

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:33
Joined
Jul 9, 2003
Messages
17,503
This SQL is getting a bit advanced for me, so I was wondering if anyone could give me some pointers.

I have two queries:
qryDonDateRange (a select query)
qryDonationCrosstab (a cross tab query)

the cross tab query is based on the select query. However I want to reproduce these queries in the module, but I have no idea how to combine them!

the SQL of these queries is:
Code:
'>>> qryDonDateRange <<<
SELECT DatePart("m",[PaymentDate]) AS [Month], tlkpInfo.tlkpInfoDesc AS Donation, tblDonations.PaymentAmount AS Amount
FROM tlkpInfo INNER JOIN tblDonations ON tlkpInfo.tlkpInfoID = tblDonations.DonationcodeID
WHERE (((tblDonations.PaymentDate) Between #1/1/2006# And #1/1/2007#));

'>>> qryDonationCrosstab <<<
TRANSFORM Sum(qryDonDateRange.Amount) AS SumOfAmount
SELECT qryDonDateRange.Donation
FROM qryDonDateRange
GROUP BY qryDonDateRange.Donation
PIVOT qryDonDateRange.Month;

Any comments suggestions or advice gratefully received!
Cheers Tony.
 
You can use qryDonDateRange as an in-line query within qryDonationCrosstab, thus:
Code:
TRANSFORM Sum(qryDonDateRange.Amount) AS SumOfAmount
SELECT qryDonDateRange.Donation
FROM
 [b](SELECT DatePart("m",[PaymentDate]) AS [Month], 
  tlkpInfo.tlkpInfoDesc AS Donation, 
  tblDonations.PaymentAmount AS Amount
  FROM tlkpInfo INNER JOIN tblDonations
  ON tlkpInfo.tlkpInfoID = tblDonations.DonationcodeID
  WHERE tblDonations.PaymentDate Between #1/1/2006# And #1/1/2007#
 ) AS qryDonDateRange[/b]
GROUP BY qryDonDateRange.Donation
PIVOT qryDonDateRange.Month;
 
Thank you ByteMyzer,

that is excellent, and is a lot simpler than the example I have been struggling with. I have a very old book: Running Microsoft Access 97 By John L. Viescas Which has an advanced SQL chapter, I just couldn't get his example on Page 421 to work.

Interestingly his example has a final statement:
IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

which when I add it in gives me the "Data type mismatch" error message, I assume it is because I am feeding the query the months as numbers, and then I am trying to change them to text? Any ideas ?
 
I am getting there slowly!

This code correctly titles the columns,

PIVOT Format(qryDonDateRange.Month, "mmm")
IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

but now only the first and last columns fill with data...............

having fun!

cheers Tony
 
Anyone know of any resources to help learn this stuff? There are some if excellent and advanced examples in John L. Viescas' book, but I am not connecting with his style of writing, I need a different viewpoint.

Cheers Tony...
 
Try the Format in the first part of the statement
SELECT Format([PaymentDate],"mmm") AS [Month],
 

Users who are viewing this thread

Back
Top Bottom