Results 1 to 6 of 6
  1. #1
    paultje_bos is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Posts
    13

    Planning timeline

    Hi all,



    I am trying to create some sort of a planning or timeschedule. It should display the days on the top and the locations on the left. So if a location is planned on a certain date, it should be highlighted or marked in the planning. I tried a couple of things already, but nothing really works. What I did so far, was a crosstab query, I managed to get the top row and locations displayed, but to have to right result at the right place I did not succeed.

    Maybe I should try something else, so I am open to suggestions.

    Here a view how it should like like

    01-01 02-01 03-01 04-01 05-01 06-01 07-01
    A X X X X X
    B X X
    C X X X
    D X X X X

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you provide your table details (field names and datatypes and if not apparent (remember we don't know your data) indicating which fields relate to rows, columns and value, perhaps we can help - otherwise can only suggest what you have already tried

  3. #3
    paultje_bos is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Posts
    13
    You are right Ajax, sorry for the insufficient info. So I am still working out what kind of tables I should use, this is also the reason why I didn't mentioned them. I did created some tables:
    - Tbl1: Dates (in this table I put all the sequential dates because it can happen that on one day nothing is planned, but I still want it to be visible)
    - Field: Autonumber;Date
    - Tbl2: Location (in this table I have all the locations)
    - Field: Autonumber;Location
    - Tbl3: Visit_Planning (in here I have all planned visits and which day
    - Field: Autonumber;Date;Location;Reason;Cancelled

    I hope this is enough, like I said I am still in the planning phase, so any other suggestions are welcome as well.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	53.3 KB 
ID:	25720

    for some reason IE froze when I tried to save my response but I was able to take a screenshot before closing IE to save me typing it again - hence the screenshot!

  5. #5
    paultje_bos is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Posts
    13
    Thanks for the response Ajax, I already experienced the Date restriction, so this I changed it already.

    The value I want to display should be the Reason.
    You can only have one visit per location per day.
    The number of columns could be limited, I want to use a date select (from and to) to display the dates. But then I will create an VBA that limits it to something like 60 days

    I didn't meant that I couldn't use a crosstab, I just didn't succeed to get the Value, every time I received an error.
    But have used your code and it works almost perfectly, only problem is that on dates that there is no visit it shows a blanco "Location" and put's an "X" on the date there is no visit. What I want is that the date is visible, but just blank record.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The value I want to display should be the Reason.
    change the "X" to [Reason]

    But have used your code and it works almost perfectly, only problem is that on dates that there is no visit it shows a blanco "Location" and put's an "X" on the date there is no visit.
    Not tested but try adding criteria of the location column of is not null

    In sql it will add another line

    WHERE...
    GROUP BY...
    HAVING Location is not null
    PIVOT....

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

Similar Threads

  1. query between dates and timeline
    By blacknight74 in forum Queries
    Replies: 3
    Last Post: 09-04-2014, 06:05 AM
  2. Timeline + Events Report
    By schwabe in forum Reports
    Replies: 1
    Last Post: 07-31-2013, 05:14 PM
  3. Report Timeline
    By brownk in forum Reports
    Replies: 3
    Last Post: 05-31-2012, 02:53 PM
  4. Timeline View in Access
    By brettbarton in forum Forms
    Replies: 1
    Last Post: 11-08-2011, 07:35 PM
  5. timeline form
    By barkarlo in forum Programming
    Replies: 0
    Last Post: 08-07-2010, 12:39 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