Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    7

    Unanswered: create a weekly work schedule

    Hi all, thanks in advance for any help.

    I am trying to display the contents of a table in a week view format.

    The table contains the fields below:

    ID, JobNum, VehicleType, Reg, SiteName, Date, DayOfWeek, Day

    Day contains the day i.e 'Monday' for the date in the 'Date' column.

    Each record is for one Vehicle and its location on a given date.

    I would like to be able to show a single record with all of the locations for one vehicle a week on one row i.e.

    VehicleType Reg Monday Tuesday Wednesday Thursday Friday

    Vactor 1500 VX05 SiteA SiteB SiteC SiteD SiteE
    Mega Whale KX06 SiteB Sitec SiteA SiteD SiteE


    I am not sure if this is possible, but any pointers would be greatly appreciated

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Play with the crosstab query wizard and see if you get what you're after. By the way, you don't need a field for the day of the week. It can always be derived from the date field with built-in functions. And the date field should not be named "date", as it is a reserved word:

    http://support.microsoft.com/default...b;EN-US;209187
    Paul

  3. #3
    Join Date
    May 2003
    Posts
    7
    Thanks for the reply and advice

    I have played with the crosstab query wizard, the results that i am getting are close but it still can't get the results to display as one row, i.e if there is more than one site for the week they are displayed on different rows. I have played with the grouping options but am still unable to get the results i'm after.

    I think I may be missing something somewhere.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db we can play with?
    Paul

  5. #5
    Join Date
    May 2003
    Posts
    7
    Thanks will do one now

  6. #6
    Join Date
    May 2003
    Posts
    7
    tblJobTickets contains data inputted by the user, the Vehicle, REG, Date and location are then exported to the tblPlanner table by an SQL insert statement.

    I am tyring to get the results displayed the same as the tblWorksPlannerFinal table.

    The crosstab query is displaying the results as seperate rows for the sites.
    Attached Files Attached Files

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This appears to match your first post:

    TRANSFORM Max(tblPlanner.SiteName) AS MaxOfSiteName
    SELECT tblPlanner.VehicleType, tblPlanner.Reg
    FROM tblPlanner
    GROUP BY tblPlanner.VehicleType, tblPlanner.Reg
    PIVOT tblPlanner.Date;
    Paul

  8. #8
    Join Date
    May 2003
    Posts
    7
    That works a treat, thank you very much for your help.

Posting Permissions

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