Results 1 to 11 of 11
  1. #1
    FL0XN0X is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Belgium
    Posts
    84

    Crosstab Query ?

    Dear,

    I have 3 tables:

    Calendar Customer Type of reservation
    Id Date Id Name Id Reservation Type
    1 1/01/2018 1 Matthew 1 AM
    2 2/01/2018 2 Jake 2 PM
    3 3/01/2018 3 Els 3 D
    Table 1: Table holding a calendar
    Table 2: Table holding Customers info
    Table 3 : Table holding information of the type of reservation

    Now, I got a query that populates a table that show per date which customer has which type of reservation, resulting in table:

    Reservation
    Id CalendarId CustomerId Type of Reservation Id
    1 1 1 1
    2 1 2 1
    3 1 3 3
    4 2 1 1
    5 2 2 2
    6 2 3 1
    7 3 1 3
    8 3 2 2
    10 3 3 1
    11


    Question
    How to make the query that lists the dates (group by) and then shows a column per type of reservation and count the number of times that type of reservation occurred for that date. For the example above, result should be:

    Query
    Id Date # AM # PM # D
    1 1/01/2018 2 0 1
    2 2/01/2018 2 1 0
    3 3/01/2018 1 1 1




    Thank you in advance.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Consider:

    TRANSFORM Nz(Count(Reservations.CustID),0) AS CountOfCustID
    SELECT Calendar.DateRes
    FROM Calendar INNER JOIN (ReservationTypes INNER JOIN Reservations ON ReservationTypes.ID = Reservations.ResTypeID) ON Calendar.ID = Reservations.CalID
    GROUP BY Calendar.DateRes
    PIVOT ReservationTypes.ResType In ("AM","PM","D");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is there a reason to start a new thread?
    yes - ridders was out for an hour

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ajax/orange
    Just got back. LOL.
    Our team Banana Republic just came second from last at quiz night
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    hey second - better than third

    one of our quiz nights - worst team of the night get a prize. Since most teams know they don't stand a chance to win the top prize, there is huge competition to come last!

  8. #8
    FL0XN0X is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Belgium
    Posts
    84
    Hey,
    Thank you for the reply. It's working except for the fact that it asks for a customerId when executing ... Any idea how to avoid this dialogue pop-up ?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use your actual field name.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    FL0XN0X is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Belgium
    Posts
    84
    Hey,

    Indeed, there was a typo (custumerId instead of CustomerId) ....
    Final question on the topic:
    How can I use the individual columns in calculations? Eg. If I want to sum the number of 'AM' and 'D' together?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Use the CROSSTAB as source for another query or a report and sum the columns in query or report textbox.

    Or:

    TRANSFORM Nz(Count(Reservations.CustID),0) AS CountOfCustID
    SELECT Calendar.DateRes
    FROM Calendar INNER JOIN (ReservationTypes INNER JOIN Reservations ON ReservationTypes.ID = Reservations.ResTypeID) ON Calendar.ID = Reservations.CalID
    GROUP BY Calendar.DateRes
    PIVOT IIf([ResType]="PM", "PM", "AM") In ("AM","PM");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 07-13-2017, 05:23 PM
  2. Replies: 8
    Last Post: 05-11-2017, 07:54 AM
  3. Replies: 2
    Last Post: 04-30-2014, 03:01 PM
  4. Replies: 2
    Last Post: 08-16-2013, 01:36 AM
  5. Replies: 1
    Last Post: 07-30-2010, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums