Combining two queries in code? (1 Viewer)

[Uncle Gizmo]

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:

'>>> 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.

 

[ByteMyzer]

You can use qryDonDateRange as an in-line query within qryDonationCrosstab, thus:

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;

 

[Uncle Gizmo]

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 ?

 

[Uncle Gizmo]

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

 

[Uncle Gizmo]

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...

 

[Rich]

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