Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query by date (XP)

    TRANSFORM Count(Q_PharmacistsByError.RNA_ID) AS CountOfRNA_ID
    I'm trying to do a cross tab query by dates but I get the error message "The microsoft jet database engine does not SELECT Q_PharmacistsByError.CounseledEmployee, Count(Q_PharmacistsByError.RNA_ID) AS [Total Of RNA_ID] recognize '[Start Date]' as a valid field name or expression." The query I am using for the crosstab query looks like this:
    SELECT T_Counseled.CounselID, T_Counseled.CounseledDate, T_Counseled.CounseledDept, T_Counseled.CounseledEmployee, T_Patients.RNA_ID, T_Patients.ErrorNotificationDate, T_Patients.DispError
    FROM T_Patients INNER JOIN T_Counseled ON T_Patients.RNA_ID = T_Counseled.RNA_ID
    WHERE (((T_Counseled.CounseledDept)="pharmacists") AND ((T_Patients.ErrorNotificationDate) Between [Start Date] And [End Date]) AND ((T_Patients.DispError) Is Not Null));

    I tried removing the "Between [Start Date] And [End Date]) " from that query and placing the ErrorNotificationDate into the crosstab query and replace the "Between [Start Date] And [End Date]) " directly into the crosstab query but that didn't work either.
    So this is what I have that works but I need to query by dates. How do I do that?

    FROM Q_PharmacistsByError
    GROUP BY Q_PharmacistsByError.CounseledEmployee
    PIVOT Q_PharmacistsByError.DispError;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: crosstab query by date (XP)

    Your post looks mixed up to me, but try the following:
    - Open Q_PharmacistsByError in design view.
    - Select Query | Parameters...
    - Under parameter, enter [Start Date] and select Date/Time as data type.
    - In the second row, enter [End Date] and select Date/Time as data type.
    - Click OK.
    See if the crosstab query works now.
    If not, repeat the above for the crosstab query.

  3. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query by date (XP)

    Hans, you are just so awesome!!!!!!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Don't know how I would get through these tough days without you and this board. As usual, worked like a charm and I learned something new. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •