Show all Dates

[mike1975]

I will try and make this as clear as possible. Basically I have a databast that tracks orders and ultimately deliveries. I would like a query to show the activities on a range of dates. My orders table holds the order date and the delivery date. However I need to produce a report that will show all dates regardless of if an order or delivery was made on that date. At present I can only force it to count actual date values but would like to be able to show 0 in days where there are no entries. For example if 29/10 had 2 orders and 2deliveries, but the 28/10 had no deliveries the report would show;
28/10 29/10 30/10
Orders 0 2
Deliveries 2 2

I hope you understand what i am getting at and pass on my thanks to anyone in advance.

MIKE

 

[danvlas]

Use an outer join in the query. The arrow should start from the 'AllDatesTableOrQuery' and point to 'OrdersAndDeliveries'. This will show all records from AllDatesTableOrQuery and only those records from the OrdersAndDeliveries that match. All non-matched records from OrdersAndDeliveries will have Null in the query result.
In the report, format the text boxes as:
@;0
to show 0 instead of null.

You may need a 'dates' table to hold all dates of the year.

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[ladybyrd]

I think you will need another table that includes all possible order/delivery dates. Then join it to your orders table, including all records from the Date table. The date table doesn't necessarily have to be a permanent table. You could create it "on the fly" from a make-table query whenever you need it.

 

[danvlas]

ladybyrd: no offence, but IMO it's better to keep those dates in a permanent table.

Make-table queries are a real pain in the neck sometimes:
1. They delete the existing table (but do not release space)
2. They create a table (and use more space)
3. If you have a front-back end application, you will unnecessarily create that table in the front end.
4. If it's a multi-user application, imagine what happens if two guys decide to do the same thing at the same time, but with different data. The best thing would be an error. but if not, one of them will get probably fired.

That's why I call them 'make-trouble' queries and I try to avoid them to the maximum extent possible.

But the idea of keeping the dates in a table is good.

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[mike1975]

Thanks for the replies, I was hoping that I would not have to create a table with all the possible dates in - however I cannot see another workable solution. Is there a quick way to create such a table and maybe build some kind of routine so that it will automatically add the next months dates in as it gets towards the end of the month???

 

[ladybyrd]

Point well taken, danvlas. A permanent date table wouldn't take up that much room anyway.

mike1975, you could create the date table in Excel in a matter of minutes and export it to Access.