obtaining and then grouping by week no's

I have a query that returns [TheDate],[Number of Bookings] and [The Day] :

12/05/04 3 Wednesday
13/05/04 0 Thursday


and so on

what I would like to do now is count the number of bookings by week so from
monday to friday from the first date get the sum of bookings.

How do I distinguish between the different weeks without any type of week
number?

Does a function exist that will allow me to get the week numbers between
dates and then that way I can create a new query to group by week number and
then count the sum of the bookings.

I did a few searches but havnt bee able to find any functions that go beyond
basic date calculations.

Regards in advance

Peter

re: obtaining and then grouping by week no's

Peter Bailey wrote:
[color=blue]
> I have a query that returns [TheDate],[Number of Bookings] and [The Day] :
>
> 12/05/04 3 Wednesday
> 13/05/04 0 Thursday
>
>
> and so on
>
> what I would like to do now is count the number of bookings by week so from
> monday to friday from the first date get the sum of bookings.
>
> How do I distinguish between the different weeks without any type of week
> number?
>
> Does a function exist that will allow me to get the week numbers between
> dates and then that way I can create a new query to group by week number and
> then count the sum of the bookings.
>
> I did a few searches but havnt bee able to find any functions that go beyond
> basic date calculations.
>
> Regards in advance
>
> Peter
>
>[/color]
Format(Date, "ww")


--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

re: obtaining and then grouping by week no's

"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message news:<cg6qpa$h1a$1@newsg3.svr.pol.co.uk>...[color=blue]
> I have a query that returns [TheDate],[Number of Bookings] and [The Day] :
>
> 12/05/04 3 Wednesday
> 13/05/04 0 Thursday
>
>
> and so on
>
> what I would like to do now is count the number of bookings by week so from
> monday to friday from the first date get the sum of bookings.
>
> How do I distinguish between the different weeks without any type of week
> number?
>
> Does a function exist that will allow me to get the week numbers between
> dates and then that way I can create a new query to group by week number and
> then count the sum of the bookings.
>
> I did a few searches but havnt bee able to find any functions that go beyond
> basic date calculations.
>
> Regards in advance
>
> Peter[/color]
================================================== ========================
Hi!
I'm not sure if this can help you, but this query:

SELECT
tblOrders.InvoiceID,
DatePart("yyyy",[MyDate],1,0) AS MyYeaR,
tblOrders.MyDate,
tblCust.Custnr,
tblKunder.FirstName,
Format(Sum([Price]*[Amount]),"Standard") AS Sales

FROM
(tblCust INNER JOIN tblOrders ON tblCust.CustID = tblOrders.CustID)
INNER JOIN
tblOrderDetails ON tblOrders.InvoiceID = tblOrderDetails.InvoiceID

GROUP BY
tblOrders.InvoiceID,
DatePart("yyyy",[MyDate],1,0),
tblOrders.MyDate,
tblCust.Custnr,
tblCust.Name

HAVING
(((DatePart("yyyy",[MyDate],1,0))=[TELL ME WHAT YEAR]))

ORDER BY
tblOrders.MyDate DESC;

produces the nicest report I've ever made.
The report is then having an unbound textbox in the toptext for MyDate:
=Format$([MyDate];"mmmm" & " yy";0;0)

and is being sorted:

MyDate DESCENDING;
Custnr ASCENDIND;

and the report will list all activity within each month, nicely output like:

AUGUST:

2004.05.01 Anka Paul 1250:- EUR
2004.05.06 Smith Gold 975:- EUR

and so on...

Hope this can help you!

Me.Name

re: obtaining and then grouping by week no's

"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message news:<cg6qpa$h1a$1@newsg3.svr.pol.co.uk>...[color=blue]
> I have a query that returns [TheDate],[Number of Bookings] and [The Day] :
>
> 12/05/04 3 Wednesday
> 13/05/04 0 Thursday
>
>
> and so on
>
> what I would like to do now is count the number of bookings by week so from
> monday to friday from the first date get the sum of bookings.
>
> How do I distinguish between the different weeks without any type of week
> number?[/color]

You don't. You create an expression to group on.
=Format$([SomeTime],"ww",0,0)
[color=blue]
> Does a function exist that will allow me to get the week numbers between
> dates and then that way I can create a new query to group by week number and
> then count the sum of the bookings.[/color]

?datediff("ww",#1/1/04#,date) <-- Today's Date.
33

So query 1 gets the differences in weeks, and then the second does the
grouping. Or you could do it in a report.
[color=blue]
> I did a few searches but havnt bee able to find any functions that go beyond
> basic date calculations.
>
> Regards in advance
>
> Peter[/color]

re: obtaining and then grouping by week no's

I wanted something more like this :

I have a qry that returns the number of bookings on any particular day
excluding saturday and sundays.

[date] [Num_of Bookings] [Day of week]

04/04/04 10 Monday
05/04/04 5 Tuesday
06/04/04 6 Wednesday
07/04/04 0 Thursday
08/04/04 3 Friday

11/04/04 10 Monday
12/04/04 7 Tuesday
13/04/04 3 Wednesday
14/04/04 2 Thursday
15/04/04 1 Friday


What I would like is a 4th Column titled week number that will allow me to
attach the week number (it could be any number) so that I can group on week.
ie the first set of dates here 4th to the 8th would show lets say week 1 and
then the second set of dates 11th to 15th would show week 2. unless I can
extract the natural week number of that week during the year.

04/04/04 10 Monday 1
05/04/04 5 Tuesday 1
06/04/04 6 Wednesday 1
07/04/04 0 Thursday 1
08/04/04 3 Friday 1

11/04/04 10 Monday 2
12/04/04 7 Tuesday 2
13/04/04 3 Wednesday 2
14/04/04 2 Thursday 2
15/04/04 1 Friday 2

I would then be able to create a new query to get the sum of booking for
each week by grouping on the week number column.

Regards in advance and my apologies for not being so clear.


"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cg6qpa$h1a$1@newsg3.svr.pol.co.uk...[color=blue]
> I have a query that returns [TheDate],[Number of Bookings] and [The Day] :
>
> 12/05/04 3 Wednesday
> 13/05/04 0 Thursday
>
>
> and so on
>
> what I would like to do now is count the number of bookings by week so[/color]
from[color=blue]
> monday to friday from the first date get the sum of bookings.
>
> How do I distinguish between the different weeks without any type of week
> number?
>
> Does a function exist that will allow me to get the week numbers between
> dates and then that way I can create a new query to group by week number[/color]
and[color=blue]
> then count the sum of the bookings.
>
> I did a few searches but havnt bee able to find any functions that go[/color]
beyond[color=blue]
> basic date calculations.
>
> Regards in advance
>
> Peter
>
>[/color]

re: obtaining and then grouping by week no's

That last post was fantastic my apologies for sending my post as the
datediff code worked a treat with a little tweaking.

Thank you so much for responding so quickly.

Regards
Peter
"Peter Bailey" <peterbailey@andaluz.fsbusiness.co.uk> wrote in message
news:cg6qpa$h1a$1@newsg3.svr.pol.co.uk...[color=blue]
> I have a query that returns [TheDate],[Number of Bookings] and [The Day] :
>
> 12/05/04 3 Wednesday
> 13/05/04 0 Thursday
>
>
> and so on
>
> what I would like to do now is count the number of bookings by week so[/color]
from[color=blue]
> monday to friday from the first date get the sum of bookings.
>
> How do I distinguish between the different weeks without any type of week
> number?
>
> Does a function exist that will allow me to get the week numbers between
> dates and then that way I can create a new query to group by week number[/color]
and[color=blue]
> then count the sum of the bookings.
>
> I did a few searches but havnt bee able to find any functions that go[/color]
beyond[color=blue]
> basic date calculations.
>
> Regards in advance
>
> Peter
>
>[/color]