Group on Weekday - show all days 1

[djayam]

Hi all,

I have searched for the answer to this already but not found it although I'm sure it's a common issue!

I want to report on deliveries scheduled this week. I have a date field [ShipDate]. I can group on this field easily enough and display the header OK, but I want to have a group for EVERY day of the week, even if there are no deliveries scheduled for that day.

Any ideas?

 

[dhookom]

You can create a table that would have every date in it. Join this new table to your report's record source with a join that includes all records from the date table.

There are methods of taking a table of values from 0-9 and creating a query of all dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[djayam]

Hi dhookom - thanks for replying.

I did indeed create a table of Weekdays, but can't figure out the join. I guess the only thing I can do is to add a field to my deliveries table called [DeliveryDay], run an update query to set the values based on [ShipDate] then LEFT OUTER JOIN my Weekdays table to that.

Cheers,

Jason

 

[dhookom]

You don't need to add a field when you can use a function to calculate it. Use the WeekDay([YourDateField]) function to find the 1-7 which you can then use in your join.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[djayam]

Hi dhookom

SELECT Weekdays.Weekday, Deliveries.ShipDate
FROM Weekdays LEFT OUTER JOIN Deliveries ON Weekdays.DayNumber = Weekday(ShipDate);

I get an error - Join Expression Not Supported. Know what I'm doing wrong?

Cheers,

Jason

 

[dhookom]

Maybe create a query from Deliveries that contains the calculated column

SELECT *,Weekday(ShipDate) AS DayNumber
FROM Deliveries

Then use this query rather Deliveries in you report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[djayam]

I think that should work! Thanks Duane.

Jason