Breakdown of data by Day of the week

L

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
 
D

Dennis

It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.
 
O

Ofer

In the query design tool bar you can see this symbol ∑ ,press it and it will
make the query group by.
or
In the menu bar, select view and one of the options is group by.
A group by query is different then cross tab query.
========================================
About the error you getting, you need to check if all the date fields fas
value in them, a format on a null value can cause an error.

use this
iif(not isnull([OrderDate]),format([OrderDate],"dd"))
 
L

LesLdh

Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


Dennis said:
It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

LesLdh said:
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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
 
D

Dennis

The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

LesLdh said:
Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


Dennis said:
It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

LesLdh said:
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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 
L

LesLdh

It goes back to the reason I'm writing the query. I need a report that will
total by Day, not date, to give me 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...

Any ideas?
Les.



Dennis said:
The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

LesLdh said:
Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


Dennis said:
It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

:

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 
D

Dennis

In that case make the 2nd Column
DayofWeek: Format(OrderDate,"dd")
instead of OrderDate

LesLdh said:
It goes back to the reason I'm writing the query. I need a report that will
total by Day, not date, to give me 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...

Any ideas?
Les.



Dennis said:
The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

LesLdh said:
Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


:

It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

:

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 
L

LesLdh

Great! That works perfectly thanks. One final question though. How would I
get the report to show a zero entry for any days where no orders have been
taken instead of just missing them out?

Dennis said:
In that case make the 2nd Column
DayofWeek: Format(OrderDate,"dd")
instead of OrderDate

LesLdh said:
It goes back to the reason I'm writing the query. I need a report that will
total by Day, not date, to give me 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...

Any ideas?
Les.



Dennis said:
The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

:

Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


:

It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

:

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 
D

Dennis

Can't think of an answer for that at the moment. Suggest you post it as a new
question.

LesLdh said:
Great! That works perfectly thanks. One final question though. How would I
get the report to show a zero entry for any days where no orders have been
taken instead of just missing them out?

Dennis said:
In that case make the 2nd Column
DayofWeek: Format(OrderDate,"dd")
instead of OrderDate

LesLdh said:
It goes back to the reason I'm writing the query. I need a report that will
total by Day, not date, to give me 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...

Any ideas?
Les.



:

The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

:

Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


:

It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

:

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 
L

LesLdh

Thanks for all your help Dennis

Dennis said:
Can't think of an answer for that at the moment. Suggest you post it as a new
question.

LesLdh said:
Great! That works perfectly thanks. One final question though. How would I
get the report to show a zero entry for any days where no orders have been
taken instead of just missing them out?

Dennis said:
In that case make the 2nd Column
DayofWeek: Format(OrderDate,"dd")
instead of OrderDate

:

It goes back to the reason I'm writing the query. I need a report that will
total by Day, not date, to give me 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...

Any ideas?
Les.



:

The contract value is numeric so it does not need a criteria line.
The 'format' line you have just gives you the day value of a date field so I
don't understand where that would fit as crietria in any of your columns.

:

Thanks Dennis, that works great. Can you help me with the second part of my
question? When I tried putting Format([OrderDate],"dd") in the criteria for
the Contract Value column, which is set to sum, and run the query it doesn't
find any records. What I now have in my query is;

Field: WeekNumber OrderDate ContractValue
Table: Orders Orders Orders
Total: Group By Group By Sum
Sort: Ascending
Criteria:
format([OrderDate],"dd")

Should this work, or am I missing something obvious?

TIA
Les.


:

It is not the same as a cross tab query. Take your normal select query in
design view and click on the totals button on the toolbar (this is the one
with the greek sigma as it picture)
This will put group by in all your columns and then you change the relevant
group by to sum. In a simple example you would have Column 1 as the Week
Number, Column 2 as the Day of the Week and column 3 as your Contract value.
Columns 1 and 2 would be left as Group By and column 3 would be changed to
Sum.

:

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 posted this question earlier in the week and got a reply which said;
" 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.... "

However 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.

thanks in anticipation.
Les.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Breakdown of data by Day of the week 2
function for date of first and last day through weeknumber 1
DSum with Criteria 8
CrossTab report "super" columns 5
Weeknum(today()) gives wrong weeknummer 6
Reduce # of date columns in crosstab query 4
Roll Up Query Data 2
Running total by group 1

Top