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.

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.

The field is StartDate and uses a calendar Long Date. I will try the query grouping and count help though, Thanks!

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.

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.

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.

Attachment 719Attachment 721Attachment 723Attachment 725

Here are the tables in question tblSchedule and tblJobs

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

Attachment 733 This is the entire database. Just hover over the AZLawn.zip

I do not see the attachment

AZLawn2.zip

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.

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

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.

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?

Your suggestions really helped me figure it out! I think I have what the teacher requires! Thanks alot

Good luck with your course. If you need more assistance just post to the forum.

It's a hard course because the teacher sucks so bad, and you have to try to interpret what the books says because it's either vague or omits steps needed or asks for the wrong procedure to perform.