Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: create queries to find jobs for each day of the week

  1. #1
    Join Date
    Jan 2015
    Posts
    10

    create queries to find jobs for each day of the week

    I am trying to find out how to run a query that will look at one table(tblSchedule) and create queries for each day of the week. I am trying to learn access and the teacher and book we have to use suck! Then I have to create a query that will count the number of jobs on each day of the week. When I asked the teacher he said look it up in the textbook(not there) or use the net! Kind of sucks that this is a college class! Any help would be greatly appreciated. We have not learned anything about SQL and the teacher says look in another class(advanced Micro Apps) for help with access. I am in the third week of that class and we are only covering word so far.

  2. #2
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Do you have a field where the day of the week is stored? If so, then use Access Help and search for Query Grouping and Count.
    Allan

  3. #3
    Join Date
    Jan 2015
    Posts
    10
    The field is StartDate and uses a calendar Long Date. I will try the query grouping and count help though, Thanks!

  4. #4
    Join Date
    Jan 2015
    Location
    Birmingham AL - USA
    Posts
    7
    An 'Aggregate Query' in query design view is the big E symbol (Sigma) - - but if long date has minutes I think, though can't remember off hand, it would be too fine to be the grouping field - - but let's say you have a simple date field - and then it will group by date, and you can change the default 'group' parameter to count (or sum or avg or etc) - - so that is an Aggregate Query that you want to research. The term 'grouping' is more often used in Reports where that is a specific feature.

    It is sometimes easier to first make a simple Select Query that establishes the date range - and returns let's say: >= 1/1/15 AND <=1/7/15 for one week - - save that as 'DateRangeQuery'. Then when you make your Aggregate Query use this as the starting point record set, rather than the table itself, so you are working with just the range that you want.

    Hope this helps.

  5. #5
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    CahabaData and Gandolfe1963

    The requirement is to Count the number of jobs for each day of the week.

    Gandolfe - in your tblSchedule do you have a field to store the Day of the Week? If you are only using start date then some extra work is required for the day of the week grouping.

    CahabaData solution will work for each date but it will not give the solution to the above requirement

    Please post your tblSchedule to assist us in helping you with your problem.
    Allan

  6. #6
    Join Date
    Jan 2015
    Posts
    10

    Can't attach any files

    I have snippits of the tables and even tried to attach the whole program but it keeps saying invalid file type and they can't be converted. The entire file is a accdb extension and it can't be converted.

  7. #7
    Join Date
    Jan 2015
    Posts
    10

    hopefully this works

    tblSceduleDataView.PNGTblJobsDesignView.PNGtblScheduleDesignView.PNGTblJobsDataView.PNG

    Here are the tables in question tblSchedule and tblJobs

  8. #8
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Next to the Post Quick Reply there is a button called Go Advanced this used for long message and attachments etc.

    Click on the Go Advanced
    - Scroll down the page until you come to attachments
    - Zip your database and click on the Manage Attachments button
    - A popup is displayed
    - Click on Add Files
    - A popup is displayed
    - Click on Select Files
    - Select the zipped file from your computer
    - Click on Open
    - Click on Upload Files
    - Click on Done
    Allan

  9. #9
    Join Date
    Jan 2015
    Posts
    10

    zipped database

    AZLawn2.zip This is the entire database. Just hover over the AZLawn.zip
    Last edited by Gandolfe1963; 01-30-2015 at 05:06 PM.

  10. #10
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    I do not see the attachment
    Allan

  11. #11
    Join Date
    Jan 2015
    Posts
    10
    AZLawn2.zip

  12. #12
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    Attached is an updated version of your database with the query DayNameCount as per your requirement.

    DayName: WeekdayName(WeekDay([ServiceDate]))

    DayName is the name of the column in query as is used as a heading to display the result.

    ServiceDate – the field used to provide the number of jobs per the day of the week.

    WeekDay – This is a function that returns a number representing the day of the week. Unless specified in the function it assumes that Sunday is the first day of the week. If the first day of the week is Monday then the above would be Weekday([ServiceDate],2)

    WeekDayName – This returns a string representing the day of the week given a number from 1 to 7. Unless specified in the function it assumes that Sunday is the first day of the week.

    DayName is the name of the weekday generated by the functions.

    How it works
    1. It from the inside to the outside.
    2. WeekDay functions returns the number the day of the week.
    3. WeekDayName looks at the value return by the WeekDay function and returns the name of the week day as Day Name

    SortOrder: Weekday([ServiceDate])

    This is a hidden column in the result of the query, it is used to SORT the value returned by WeekDay in Ascending so that the DayName is displayed as Monday, Tuesday, Wednesday and not in a random order. If the DayName column was sorted Ascending or Descending then you would display the incorrect order for the day of the week.

    Note
    I prefer to use Short Date as the format for a Date in a table.
    Attached Files Attached Files
    Allan

  13. #13
    Join Date
    Jan 2015
    Posts
    10
    I have to design the database as stated due to the teacher requiring certain parameters. This is for a college Access & SQL course with a teacher who wrote the text, is ready to retire, and doesn't teach you anything but says look it up in the chapter or on the net! If you want to laugh at a college text book read "Customizing Access Databases with VBA and SQL" By Steven Gramlich

  14. #14
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    I do not have access to the book in Australia.
    What is the chapter contents, is it just basic query design?
    Do you need to create 7 queries one for each day of the week as per the teacher's requirements? If you need to create 7 queries I will send you an updated version.
    Allan

  15. #15
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275
    In the attached database I have added the following queries.

    TuesdayCount - this the same as DayNameCount but the criteria is Tuesday, This will the number of jobs scheduled for Tuesday

    WednesayRecords - Use the same method as DayNameCount to determine the day of the week but the criteria is Wednesday. This give all the records that are scheduled for a Wednesday.

    WednesdayCount - is a query that counts the number of jobs that are scheduled for a Wednesday.

    Using the Tuesday or Wednesday method create the queries for the remaining days of the week by changing the criteria to the selected day of the week.

    If this is not suitable what are the actual parameters?
    Attached Files Attached Files
    Allan

Posting Permissions

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