Query filter question (attempting crosstab query)

  • Thread starter Thread starter joave
  • Start date Start date
J

joave

Hi:

I am attempting to filter out the month of a query but don't seem to be
having any luck. I can successfully use a date range but want to just enter
the month and year. Is this possible? I built a crosstab query but it comes
up empty:

TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE (("mmm"="Jul" AND "yyyy" = 2003)) 'doesn't matter what I put here or
how I put it; it still comes up empty
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

Thank you in advance for your help.
 
Try:
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Month(CheckOutDate)=7 AND Year(CheckOutDate) = 2003
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm") IN ("2003 JUL");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Format(CheckOutDate,"yyyymm")="200307"
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");
 
WOW thank you sooo much for this in-depth response!! You are the MAN!

Dave

Duane Hookom said:
Try:
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Month(CheckOutDate)=7 AND Year(CheckOutDate) = 2003
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm") IN ("2003 JUL");

OR
TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE Format(CheckOutDate,"yyyymm")="200307"
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");


--
Duane Hookom
Microsoft Access MVP


joave said:
Hi:

I am attempting to filter out the month of a query but don't seem to be
having any luck. I can successfully use a date range but want to just enter
the month and year. Is this possible? I built a crosstab query but it comes
up empty:

TRANSFORM Sum(tblReservations.TotalCharge) AS SumOfTotalCharge
SELECT tblFacilities.FacilityName
FROM tblFacilities INNER JOIN tblReservations ON tblFacilities.FacilityID =
tblReservations.FacilityID
WHERE (("mmm"="Jul" AND "yyyy" = 2003)) 'doesn't matter what I put here or
how I put it; it still comes up empty
GROUP BY tblFacilities.FacilityName
PIVOT Format([CheckOutDate],"yyyy mmm");

Thank you in advance for your help.
Click to expand...
Click to expand...
 
Back
Top