Breakdown of data by Day of the week

[LesLdh]

I currently hold sales data which is grouped by WeekNumber as follows;

WeekNumber, OrderDate, Rep, ContractValue

WeekNumber is in text format and OrderDate is in Short Date format. I have
been asked to produce a report that shows the sales for each Rep with the
user typing in which WeekNumber to display, so that the output would be
something like;

WeekNumber Rep

ContractValue
Mon (sum of contracts)
Tue (sum of contracts)
Wed (sum of contracts)
Thu (sum of contracts)
Fri (sum of contracts)
Sat (sum of contracts)
Sun (sum of contracts)

WeekNumber nextRep

ContractValue
Mon (sum of contracts) etc...

I haven't got a clue how to achieve this. Can anybody point me in the right
direction?

thanks in anticipation.
Les.

 

[MA]

I currently hold sales data which is grouped by WeekNumber as follows;

WeekNumber, OrderDate, Rep, ContractValue

WeekNumber is in text format and OrderDate is in Short Date format. I
have been asked to produce a report that shows the sales for each Rep
with the user typing in which WeekNumber to display, so that the
output would be something like;

WeekNumber Rep

ContractValue
Mon (sum of contracts)
Tue (sum of contracts)
Wed (sum of contracts)
Thu (sum of contracts)
Fri (sum of contracts)
Sat (sum of contracts)
Sun (sum of contracts)

WeekNumber nextRep

ContractValue
Mon (sum of contracts) etc...

I haven't got a clue how to achieve this. Can anybody point me in the
right direction?

thanks in anticipation.
Les.

Luckly your record track is normalized.
So you need only make a group query putting Sum for the contact and
format([OrderDate],"dd")
Don't forget of course the weekNum or it will sum ll the Mon, Tue....
--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it

 

[LesLdh]

Thanks for your reply, but I still haven't grasped it. How do I make a group
query? Is it the same as a cross-tab query as that is the only one where I
can see 'Group by'? When I tried putting Format([OrderDate],"dd") in the
criteria of the OrderDate field I got the error 'Data type mis-match in
Criteria expression'. Any further assistance you can give me would be much
appreciated.

Les.

I currently hold sales data which is grouped by WeekNumber as follows;

WeekNumber, OrderDate, Rep, ContractValue

WeekNumber is in text format and OrderDate is in Short Date format. I
have been asked to produce a report that shows the sales for each Rep
with the user typing in which WeekNumber to display, so that the
output would be something like;

WeekNumber Rep

ContractValue
Mon (sum of contracts)
Tue (sum of contracts)
Wed (sum of contracts)
Thu (sum of contracts)
Fri (sum of contracts)
Sat (sum of contracts)
Sun (sum of contracts)

WeekNumber nextRep

ContractValue
Mon (sum of contracts) etc...

I haven't got a clue how to achieve this. Can anybody point me in the
right direction?

thanks in anticipation.
Les.

Luckly your record track is normalized.
So you need only make a group query putting Sum for the contact and
format([OrderDate],"dd")
Don't forget of course the weekNum or it will sum ll the Mon, Tue....
--
_ _
Ciao
MAssimiliano Amendola www.accessgroup.it
Cisa - Conferenza Italiana per Sviluppatori Access
Info: www.donkarl.com/it