"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
