Date query

  • Thread starter Thread starter Bill Davis
  • Start date Start date
B

Bill Davis

I need help with building a query that will breakdown the
[Salesrep] and [OrderDate] by week, month and quarter and
that will prompt the user for the [salesrep] and month or
quarter.
Thanks in advance
 
hi,
I don't this you will be able to build a query to break it
down like that. maybe a report. querys just grab hunks of
data from a table. reports are for sorting it out. here we
usually download to xl then run a macro to break it down
like you want.
prompt:
in design mode In the criteria pane of [Salesrep] enter:
[enter the salerep]
in design mode In the criteria pane of [orderdate] enter:
=[enter a start date] and < [enter an end date]
Click to expand...
this would be a week range, month range or qarter range.
with a report you could just use date and tell the report
to group on salesrep and add totals for each rep.
-----Original Message-----
I need help with building a query that will breakdown the
[Salesrep] and [OrderDate] by week, month and quarter and
that will prompt the user for the [salesrep] and month or
quarter.
Thanks in advance

.
Click to expand...
 
Dear Bill:

A general answer to a general question is the best I can do.

You could write separate queries that give the values (sales?) by
weeks, months, and quarters. Keep this in normalized fashion - one
value per row. Then create a UNION ALL of them. Finally, assuming
you want a crosstab format, create the crosstab from that union.

If you are planning to use parameters, make sure they are identical so
it won't ask several times.

I expect there is considerable remaining challenge to doing this,
including getting the columns in the order you want and performing the
filtering from controls on a form if you should so choose. I hope
this is enough to get you started.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
I used the UNION All and it work.. I did not mess with
the parameters.
Thanks
-----Original Message-----
Dear Bill:

A general answer to a general question is the best I can do.

You could write separate queries that give the values (sales?) by
weeks, months, and quarters. Keep this in normalized fashion - one
value per row. Then create a UNION ALL of them. Finally, assuming
you want a crosstab format, create the crosstab from that union.

If you are planning to use parameters, make sure they are identical so
it won't ask several times.

I expect there is considerable remaining challenge to doing this,
including getting the columns in the order you want and performing the
filtering from controls on a form if you should so choose. I hope
this is enough to get you started.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I need help with building a query that will breakdown the
[Salesrep] and [OrderDate] by week, month and quarter and
that will prompt the user for the [salesrep] and month or
quarter.
Thanks in advance
Click to expand...

.
Click to expand...
 
Back
Top