weekly Query

[idtm]

hi,

I am creating a booking system and I need to create a query for a report that will show how many bookings are made per week and the total of the bookings. I have managed to make a query that will get the number of bookings and the total, but i have no idea on how to do this for each of the weeks in a month. From this query i hope to then make a report showing 4 weeks at a time.

Any help would be great.

 

[gromit]

Hi - welcome!

Probably the simplest approach is to use the DatePart function to convert a booking date into a week number. E.g.

WeekNum = DatePart ("ww",BookingDate)

This will give you a week number for the year.

You can build a query that has an expression to generate the WeekNum and then use a second query to calculate totals.

See the attached example.

hope that helps,

- g

 

[idtm]

Thanks, this is just what I needed. Strange values came up the first time I tried to use it in my database, but when i re-did the queries it all worked fine.

 

[gromit]

Good deal!