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

    Some kind of Crosstab Query (part2)


    Question

    I have got 3 tables:

    Calendar
    Id Date
    1 1/1/2018
    2 2/1/2018
    3 3/1/2018
    Customer

    Id Name
    1 Matthew
    2 Jake
    3 Els

    Reservation type


    Id Reservation Type
    1 AM
    2 PM
    3 D

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


    Id CalendarId CustomerId ReservationTypeId
    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
    9 3 3 1

    Question:
    How to make the query that lists the dates (group by) and then shows a column per Customer and the values should show the type of reservation. For the example above, the result should be:



    Date Matthew Jake Els
    1/1/2018 AM AM D
    2/1/2018 AM PM AM
    3/1/2018 D PM AM

    Thank you in advance.
    Kind regards,
    Bart

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is this really practical? If you have hundreds of customers, the query will truncate at 255 because can't have more than 255 fields.

    But to answer your question, start by building a normal SELECT query that includes all the relevant tables. Pull in the descriptive text and date fields, then click the CROSSTAB query on the Design tab and assign the crosstab criteria.
    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.

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

    The list of customers is limited to max 21, so no issue here.

    However, I start off with a normal Select that includes all relevant tables, then click Crostab, for the ROWS and COLUMNS it is clear what to do, but what expression do I need to put for the 'Reservation type' to show up?
    Thank you.
    Kind regards,
    Bart

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use Max or even First should work in this case.
    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. Unmatched Query (Kind of)
    By lzook88 in forum Queries
    Replies: 5
    Last Post: 12-23-2015, 12:49 PM
  2. Some kind of lookup query
    By borge in forum Queries
    Replies: 1
    Last Post: 10-16-2011, 03:13 PM
  3. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  4. Replies: 3
    Last Post: 01-22-2011, 05:12 PM
  5. What kind of query do I need?
    By cowboy in forum Queries
    Replies: 1
    Last Post: 02-17-2010, 04:09 PM

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