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

    Some kind of Crosstab Query (part3): Conditional formatting

    Question



    I have got 3 tables:

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

    Customer
    Id Name Start_Date
    1 Matthew 1/1/2018
    2 Jake 2/1/2018
    3 Els 3//1/2018


    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


    I have created a Crosstab query which lists the Customers as Row headings, the Date as Column headings, and the Reservation Type as Value. I have limited the colums by showing only dates for the current week. This results in:

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

    Question:
    How to color the background of the cell where the Date in the column headings equals the Start_Date of the corresponding Customer.
    Thus in the scenario above,
    cell Matthew - 1/1/2018 should be colored
    cell Jake - 2/1/2018 should be colored
    cell Els - 3/1/2018 should be colored

    Thank 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
    Can't be done in query. Would have to use Conditional Formatting of textbox. Structure like:

    Expression Is: [Start_Date]=Label12.Caption


    Query:
    TRANSFORM Max(ReservationTypes.ResType) AS MaxOfResType
    SELECT Customers.Start_Date, Customers.CustName
    FROM Calendar INNER JOIN (Customers INNER JOIN (ReservationTypes INNER JOIN Reservations ON ReservationTypes.ID = Reservations.ResTypeID) ON (Customers.ID = Reservations.CustID) AND (Customers.ID = Reservations.CustID)) ON Calendar.ID = Reservations.CalID
    GROUP BY Customers.Start_Date, Customers.CustName
    PIVOT Calendar.DateRes;


    And the problem with basing a report on this CROSSTAB is that the column headers will change and the report design will have to be modified to change the field names textboxes are bound to.
    Last edited by June7; 02-19-2018 at 12:54 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.

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

    You are THE GREATEST !!! It is working as I was hoping it to be.
    Many many thanks.
    Up to my (part4) post ;-)

    Kind regards,
    Bart

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

    You're right concerning the column headers that need to be changed. Can this be done programmatically ?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably, but nothing I've ever done. I expect it would required a lot of code.

    Could try building a stable report using method from this link http://www.access.hookom.net/Dynamic...rosstabRpt.htm
    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.

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

    Since the Source control refers to the TODAY date, the next control is TODAY()+1, I think it should be feasible using the Expression Builder ... I'll try this evening (after work) and let you know... Again, thank you.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Also have to get creative for dynamic label captions. Use textboxes as 'labels'. They can have expressions calculating the dates same as in the query design. I have used this technique.
    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.

  8. #8
    FL0XN0X is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Belgium
    Posts
    84
    Click image for larger version. 

Name:	Week preview.jpg 
Views:	12 
Size:	262.9 KB 
ID:	32704


    Hey June7,

    I have uploaded some screenshots to explain the issue I'm facing.
    I have created a form based on a crosstab query. The column headers of the form are dynamically created based on the On Load event of the Form (cfr; Screenshot). Column one holds the date of today(day when Form is loaded) , the second columns hold the dates of Today +1; column 2 = Today +2 and so on .... However, since it was 21/2/2018 that I have created the form, the control source name refers to this hardcoded .. second control source refers to 22/2/2018 ..and so on. As a result, the form works fine on the day that the Form is created, but the day after, it tries to refer to the control source names which were initially assigned to the control source, resulting in the Name? error as indicated in the screenshot. Question, how to create the control Source name to be dynamically ?

    Thanks in advance.
    Kind regards,
    Bart

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I use an UNBOUND textbox where user inputs start date. And my 'labels' are textboxes with expression, not VBA code setting label Caption.

    Now() includes time components. Just use Date().

    Following Hookum's tutorial, here is query:

    PARAMETERS [Enter Start Date] DateTime;
    TRANSFORM Max(ReservationTypes.ResType) AS MaxOfResType
    SELECT Customers.Start_Date, Customers.CustName
    FROM Calendar INNER JOIN (Customers INNER JOIN (ReservationTypes INNER JOIN Reservations ON ReservationTypes.ID = Reservations.ResTypeID) ON (Customers.ID = Reservations.CustID) AND (Customers.ID = Reservations.CustID)) ON Calendar.ID = Reservations.CalID
    GROUP BY Customers.Start_Date, Customers.CustName
    PIVOT DateDiff("m",[Enter Start Date],[DateRes])+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

    Now bind textboxes to the 1, 2, etc fields. Build Conditional Formatting on these textboxes.
    Keep in mind if your start date is not January then 1 does not mean January.


    Change the [Enter Start Date] parameter to reference control on form.


    If you want to limit output to Jan - Dec of a single year, can be accomplished without CROSSTAB.
    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 June7,

    Thank you fo ryour feedback. I have managed to fix it by setting the ControlSource property in the On Load event of the form. Just waited until now to post the result just to reflect a new day situation ( lcal time is now 0h04 :-)
    I have included 2 screenshots, one before and one after midnight. Now, I have a 1 week preview of the different occumpation per day, and if there is a new customer during that day, it's start day is colored green. If someone is leaving (stopdate), the upcoming week, it is reflected by a red conditional formatting.....

    I'm almost at the end of the development of this 'little project'.
    Thank you guys for this great forum. I would have been ale to pull it off without your help.

    (Let's not cry victory yet, there is still some reporting stuff to develop) :-), but I'm sure I will succeed with your help.
    Than kyou again.

    Here are the screenshots:

    Click image for larger version. 

Name:	Weekpreview.jpg 
Views:	10 
Size:	106.8 KB 
ID:	32708

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am glad you have solution but it should be possible without VBA.
    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. Some kind of Crosstab Query (part2)
    By FL0XN0X in forum Access
    Replies: 3
    Last Post: 02-18-2018, 04:13 AM
  2. Not a crosstab but formatting a select query
    By mindbender in forum Queries
    Replies: 15
    Last Post: 09-25-2017, 06:34 AM
  3. Conditional Formatting Report from Crosstab Query
    By zmster2033 in forum Reports
    Replies: 1
    Last Post: 01-27-2014, 02:54 PM
  4. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  5. Conditional formatting in crosstab report
    By squirrelmaster in forum Reports
    Replies: 0
    Last Post: 06-24-2010, 06:31 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