By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

obtaining and then grouping by week no's

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Peter Bailey wrote:
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

Format(Date, "ww")
--

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

Nov 13 '05 #2

P: n/a
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<cg**********@newsg3.svr.pol.co.uk>...
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

================================================== ========================
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
Nov 13 '05 #3

P: n/a
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<cg**********@newsg3.svr.pol.co.uk>...
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?
You don't. You create an expression to group on.
=Format$([SomeTime],"ww",0,0)
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.
?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.
I did a few searches but havnt bee able to find any functions that go beyond
basic date calculations.

Regards in advance

Peter

Nov 13 '05 #4

P: n/a
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" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cg**********@newsg3.svr.pol.co.uk...
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

Nov 13 '05 #5

P: n/a
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" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:cg**********@newsg3.svr.pol.co.uk...
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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.