need help sorting data in a query by day numbers

[Guest]

I am somewhat new to access and have learned alot but need some help.
I have 2 questions that i need answered, but here's what i have so far.
The database i'm creating will track salesreps and thier goals and thier
sales for the day. I currently have two tables, table one named "Employees"
has the salesreps id#, first name, last name, and thier daily, weekly, pay
period, monthly, quarterly, and yearly goals. Table two is the daily input of
sales named "Orders", salesid#, Today'sDate, name of salesrep from
EmployeesID#, sales amounts, cold sales amount, number of orders, number of
dials, whether they were in or not(check box), number of K/O, amount of K/O,
number of cancels, and amount of cancels.
Now i have everything working correctly and looks great, but my problem is i
want to have several team reports based on sales for the day, week, PP,
quarter, month, and year. I got all the reports working correctly except for
the day one, but i had to make a seperate queries to sort the data by day,
week, PP( i used between dates expression), month, quarter, and year. Right
now it works great and looks the way i wanted it to look.
Is thier an easier way to get the same data to show on reports based on team
sales for the daily, weekly, PP, monthly, quarterly, and yearly without
making six different queries? or is the way i have it the best way?
Second, when i try and sort the data by day, it does it by the day name
(monday, tuesday, ect.) regardless of what month it is. Which is not what i
want it to do, i want it to sort it by the day number (December 17, 2005,
December 16, 2005, December 15, 2005, November 30, 2005, November 29, 2005,
ect) and show the totals sales for all salesreps for that day.
In the first column of the teamsalesbyday query i have it look like this
Field: Today'sDate By Date: Format$([Orders.Today'sDate],"mmmm dd"", ""yyyy")
Table: Blank
Total: Group by
Sort: Blank
Show: Checked
Criteria: blank
Or: blank
all others have the Sum Of Sales: Sales, Sum of ColdSales: ColdSales, ect.,
which it does, but when i try and sort it in descending order, it doesn't
sort it the way i want it too. It will sort it like this...
November 23, 2005
November 21, 2005
November 15, 2005
December 15, 2005
December 12, 2005
December 10, 2005
When i sort in ascending order it sorts like this...
December 10, 2005
December 12, 2005
December 15, 2005
November 15, 2005
November 21, 2005
November 23, 2005
Which is not the way i want. I would like December 15, 2005 to be in the top
row of the query table.
If i add a another column to the query with Today'sDate in it, i can then
sort it by day number in ascending or descending but it will not sum the
total sales for that day for the team, it will show all the sales for that
day, which is not what i want it to do. Is thier an easier way to thist?
any help would be greatly appreciated.

Thanks,

Steve K.

 

[Allen Browne]

The Format() function is turning your dates into text. The result is that
you are getting sorted text, instead of sorted dates.

Remove the Format() function from the field, and (assuming it is a Date/Time
field), it will sort correctly. Then right-click this field in query design,
and choos Properties. Set the field's Format property to how you want it
displayed.

There are several ways to summarize the data. In query design view, you can
depress the Total button to get grouping by various fields, but in this case
that probably won't help, since you need your grouping to be dynamic (not
the same thing).

Here's a simple workaround:
1. Create a report into this table (or query if you need data from multiple
tables.)

2. Add the columns you want in the Detail section, such as the salesman and
Amount.

3. Right-click the grey bar labelled Details, and choose Properties. On the
Format tab of the Properties box, set the Visible property of the Detail
section to No. This suppresses the printing of every sales record, and will
only print the summaries created below.

4. Open the Sorting And Grouping dialog (View menu.)

5. Select the date/time field on the first row of the dialog.
In the lower pane of the dialog set these properties:
Group Header No
Group Footer Yes
Group On Year
Group Interval 1
You see Access adding a group footer to the report.

6. Add a text box to the group footer section, and set these properties:
Control Source =Sum([Amount])
Format Currency

7. Add another text box to the group footer, with Control Source:
="Total for " & Format([txtYear], "yyyy") & ":"

8. In the Sorting and Grouping dialog, choose the date/time field again on
the next row. Set the same properties, but set the Group On property to
Qtr.

9. Repeat steps 7 & 8 to get the total for the quarter in the group footer.

10. Repeat these steps again to get the total for the month, and again for
the day.

Test the report. You should see:
Nov 10 $6
Nov 12 $4
Nov 15 $2
Nov 21 $1
Nov 23 $3
Total for Nov $14
Dec 10 $5
...
Total for Dec $99
Total for 2005 $999

I'm not clear how you want to see the breakdown by salesperson, but you can
add the SalesRep field to the Sorting and Grouping dialog where it suits
you.

Now, if you want to suppress further group levels (e.g. to just show by year
and quarter), you can do this programmatically in the Open event procedure
of the report. Set the Visible property of the GroupLevel to No to suppress
the section.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am somewhat new to access and have learned alot but need some help.
I have 2 questions that i need answered, but here's what i have so far.
The database i'm creating will track salesreps and thier goals and thier
sales for the day. I currently have two tables, table one named
"Employees"
has the salesreps id#, first name, last name, and thier daily, weekly, pay
period, monthly, quarterly, and yearly goals. Table two is the daily input
of
sales named "Orders", salesid#, Today'sDate, name of salesrep from
EmployeesID#, sales amounts, cold sales amount, number of orders, number
of
dials, whether they were in or not(check box), number of K/O, amount of
K/O,
number of cancels, and amount of cancels.
Now i have everything working correctly and looks great, but my problem is
i
want to have several team reports based on sales for the day, week, PP,
quarter, month, and year. I got all the reports working correctly except
for
the day one, but i had to make a seperate queries to sort the data by day,
week, PP( i used between dates expression), month, quarter, and year.
Right
now it works great and looks the way i wanted it to look.
Is thier an easier way to get the same data to show on reports based on
team
sales for the daily, weekly, PP, monthly, quarterly, and yearly without
making six different queries? or is the way i have it the best way?
Second, when i try and sort the data by day, it does it by the day name
(monday, tuesday, ect.) regardless of what month it is. Which is not what
i
want it to do, i want it to sort it by the day number (December 17, 2005,
December 16, 2005, December 15, 2005, November 30, 2005, November 29,
2005,
ect) and show the totals sales for all salesreps for that day.
In the first column of the teamsalesbyday query i have it look like this
Field: Today'sDate By Date: Format$([Orders.Today'sDate],"mmmm dd"",
""yyyy")
Table: Blank
Total: Group by
Sort: Blank
Show: Checked
Criteria: blank
Or: blank
all others have the Sum Of Sales: Sales, Sum of ColdSales: ColdSales,
ect.,
which it does, but when i try and sort it in descending order, it doesn't
sort it the way i want it too. It will sort it like this...
November 23, 2005
November 21, 2005
November 15, 2005
December 15, 2005
December 12, 2005
December 10, 2005
When i sort in ascending order it sorts like this...
December 10, 2005
December 12, 2005
December 15, 2005
November 15, 2005
November 21, 2005
November 23, 2005
Which is not the way i want. I would like December 15, 2005 to be in the
top
row of the query table.
If i add a another column to the query with Today'sDate in it, i can then
sort it by day number in ascending or descending but it will not sum the
total sales for that day for the team, it will show all the sales for that
day, which is not what i want it to do. Is thier an easier way to thist?
any help would be greatly appreciated.

Thanks,

Steve K.

 

[Allen Browne]

The Format() function is turning your dates into text. The result is that
you are getting sorted text, instead of sorted dates.

Remove the Format() function from the field, and (assuming it is a Date/Time
field), it will sort correctly. Then right-click this field in query design,
and choos Properties. Set the field's Format property to how you want it
displayed.

There are several ways to summarize the data. In query design view, you can
depress the Total button to get grouping by various fields, but in this case
that probably won't help, since you need your grouping to be dynamic (not
the same thing).

Here's a simple workaround:
1. Create a report into this table (or query if you need data from multiple
tables.)

2. Add the columns you want in the Detail section, such as the salesman and
Amount.

3. Right-click the grey bar labelled Details, and choose Properties. On the
Format tab of the Properties box, set the Visible property of the Detail
section to No. This suppresses the printing of every sales record, and will
only print the summaries created below.

4. Open the Sorting And Grouping dialog (View menu.)

5. Select the date/time field on the first row of the dialog.
In the lower pane of the dialog set these properties:
Group Header No
Group Footer Yes
Group On Year
Group Interval 1
You see Access adding a group footer to the report.

6. Add a text box to the group footer section, and set these properties:
Control Source =Sum([Amount])
Format Currency

7. Add another text box to the group footer, with Control Source:
="Total for " & Format([txtYear], "yyyy") & ":"

8. In the Sorting and Grouping dialog, choose the date/time field again on
the next row. Set the same properties, but set the Group On property to
Qtr.

9. Repeat steps 7 & 8 to get the total for the quarter in the group footer.

10. Repeat these steps again to get the total for the month, and again for
the day.

Test the report. You should see:
Nov 10 $6
Nov 12 $4
Nov 15 $2
Nov 21 $1
Nov 23 $3
Total for Nov $14
Dec 10 $5
...
Total for Dec $99
Total for 2005 $999

I'm not clear how you want to see the breakdown by salesperson, but you can
add the SalesRep field to the Sorting and Grouping dialog where it suits
you.

Now, if you want to suppress further group levels (e.g. to just show by year
and quarter), you can do this programmatically in the Open event procedure
of the report. Set the Visible property of the GroupLevel to No to suppress
the section.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am somewhat new to access and have learned alot but need some help.
I have 2 questions that i need answered, but here's what i have so far.
The database i'm creating will track salesreps and thier goals and thier
sales for the day. I currently have two tables, table one named
"Employees"
has the salesreps id#, first name, last name, and thier daily, weekly, pay
period, monthly, quarterly, and yearly goals. Table two is the daily input
of
sales named "Orders", salesid#, Today'sDate, name of salesrep from
EmployeesID#, sales amounts, cold sales amount, number of orders, number
of
dials, whether they were in or not(check box), number of K/O, amount of
K/O,
number of cancels, and amount of cancels.
Now i have everything working correctly and looks great, but my problem is
i
want to have several team reports based on sales for the day, week, PP,
quarter, month, and year. I got all the reports working correctly except
for
the day one, but i had to make a seperate queries to sort the data by day,
week, PP( i used between dates expression), month, quarter, and year.
Right
now it works great and looks the way i wanted it to look.
Is thier an easier way to get the same data to show on reports based on
team
sales for the daily, weekly, PP, monthly, quarterly, and yearly without
making six different queries? or is the way i have it the best way?
Second, when i try and sort the data by day, it does it by the day name
(monday, tuesday, ect.) regardless of what month it is. Which is not what
i
want it to do, i want it to sort it by the day number (December 17, 2005,
December 16, 2005, December 15, 2005, November 30, 2005, November 29,
2005,
ect) and show the totals sales for all salesreps for that day.
In the first column of the teamsalesbyday query i have it look like this
Field: Today'sDate By Date: Format$([Orders.Today'sDate],"mmmm dd"",
""yyyy")
Table: Blank
Total: Group by
Sort: Blank
Show: Checked
Criteria: blank
Or: blank
all others have the Sum Of Sales: Sales, Sum of ColdSales: ColdSales,
ect.,
which it does, but when i try and sort it in descending order, it doesn't
sort it the way i want it too. It will sort it like this...
November 23, 2005
November 21, 2005
November 15, 2005
December 15, 2005
December 12, 2005
December 10, 2005
When i sort in ascending order it sorts like this...
December 10, 2005
December 12, 2005
December 15, 2005
November 15, 2005
November 21, 2005
November 23, 2005
Which is not the way i want. I would like December 15, 2005 to be in the
top
row of the query table.
If i add a another column to the query with Today'sDate in it, i can then
sort it by day number in ascending or descending but it will not sum the
total sales for that day for the team, it will show all the sales for that
day, which is not what i want it to do. Is thier an easier way to thist?
any help would be greatly appreciated.

Thanks,

Steve K.

 

[Guest]

Thanks Allen,
I didn't have to recreate the whole query and report again, i just
incorporated what you said to my already created query and report, and it
worked like a charm. Now dates are in right order that i want and it totals
the sales for that day. This was the only query and report that was not
sorting the date right. As for the sales by salesreps for those 6 different
reports, i got those to work with no problems.
Thanks again.

I may have worded that second question wrong. What i have is now is six
different team sales queries for day, week, month, pp, quarter and year, i
wasn't intending to have all six queries show up on one report. (Which i may
have to make later, so this info will certainly come in helpful.) I already
have six different queries to do what i want for those six reports, i was
just wondering if thier is another way to make one query do the same as six
queries, but have six different reports for the data needed from that one
query. If there is great, if not, then i'll stick with the way i have it now.
Thanks again for the help allen, you helped me get some sleep tonight, since
it has been bugging me for a couple of days now.

Steve K.

The Format() function is turning your dates into text. The result is that
you are getting sorted text, instead of sorted dates.

Remove the Format() function from the field, and (assuming it is a Date/Time
field), it will sort correctly. Then right-click this field in query design,
and choos Properties. Set the field's Format property to how you want it
displayed.

There are several ways to summarize the data. In query design view, you can
depress the Total button to get grouping by various fields, but in this case
that probably won't help, since you need your grouping to be dynamic (not
the same thing).

Here's a simple workaround:
1. Create a report into this table (or query if you need data from multiple
tables.)

2. Add the columns you want in the Detail section, such as the salesman and
Amount.

3. Right-click the grey bar labelled Details, and choose Properties. On the
Format tab of the Properties box, set the Visible property of the Detail
section to No. This suppresses the printing of every sales record, and will
only print the summaries created below.

4. Open the Sorting And Grouping dialog (View menu.)

5. Select the date/time field on the first row of the dialog.
In the lower pane of the dialog set these properties:
Group Header No
Group Footer Yes
Group On Year
Group Interval 1
You see Access adding a group footer to the report.

6. Add a text box to the group footer section, and set these properties:
Control Source =Sum([Amount])
Format Currency

7. Add another text box to the group footer, with Control Source:
="Total for " & Format([txtYear], "yyyy") & ":"

8. In the Sorting and Grouping dialog, choose the date/time field again on
the next row. Set the same properties, but set the Group On property to
Qtr.

9. Repeat steps 7 & 8 to get the total for the quarter in the group footer.

10. Repeat these steps again to get the total for the month, and again for
the day.

Test the report. You should see:
Nov 10 $6
Nov 12 $4
Nov 15 $2
Nov 21 $1
Nov 23 $3
Total for Nov $14
Dec 10 $5
...
Total for Dec $99
Total for 2005 $999

I'm not clear how you want to see the breakdown by salesperson, but you can
add the SalesRep field to the Sorting and Grouping dialog where it suits
you.

Now, if you want to suppress further group levels (e.g. to just show by year
and quarter), you can do this programmatically in the Open event procedure
of the report. Set the Visible property of the GroupLevel to No to suppress
the section.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am somewhat new to access and have learned alot but need some help.
I have 2 questions that i need answered, but here's what i have so far.
The database i'm creating will track salesreps and thier goals and thier
sales for the day. I currently have two tables, table one named
"Employees"
has the salesreps id#, first name, last name, and thier daily, weekly, pay
period, monthly, quarterly, and yearly goals. Table two is the daily input
of
sales named "Orders", salesid#, Today'sDate, name of salesrep from
EmployeesID#, sales amounts, cold sales amount, number of orders, number
of
dials, whether they were in or not(check box), number of K/O, amount of
K/O,
number of cancels, and amount of cancels.
Now i have everything working correctly and looks great, but my problem is
i
want to have several team reports based on sales for the day, week, PP,
quarter, month, and year. I got all the reports working correctly except
for
the day one, but i had to make a seperate queries to sort the data by day,
week, PP( i used between dates expression), month, quarter, and year.
Right
now it works great and looks the way i wanted it to look.
Is thier an easier way to get the same data to show on reports based on
team
sales for the daily, weekly, PP, monthly, quarterly, and yearly without
making six different queries? or is the way i have it the best way?
Second, when i try and sort the data by day, it does it by the day name
(monday, tuesday, ect.) regardless of what month it is. Which is not what
i
want it to do, i want it to sort it by the day number (December 17, 2005,
December 16, 2005, December 15, 2005, November 30, 2005, November 29,
2005,
ect) and show the totals sales for all salesreps for that day.
In the first column of the teamsalesbyday query i have it look like this
Field: Today'sDate By Date: Format$([Orders.Today'sDate],"mmmm dd"",
""yyyy")
Table: Blank
Total: Group by
Sort: Blank
Show: Checked
Criteria: blank
Or: blank
all others have the Sum Of Sales: Sales, Sum of ColdSales: ColdSales,
ect.,
which it does, but when i try and sort it in descending order, it doesn't
sort it the way i want it too. It will sort it like this...
November 23, 2005
November 21, 2005
November 15, 2005
December 15, 2005
December 12, 2005
December 10, 2005
When i sort in ascending order it sorts like this...
December 10, 2005
December 12, 2005
December 15, 2005
November 15, 2005
November 21, 2005
November 23, 2005
Which is not the way i want. I would like December 15, 2005 to be in the
top
row of the query table.
If i add a another column to the query with Today'sDate in it, i can then
sort it by day number in ascending or descending but it will not sum the
total sales for that day for the team, it will show all the sales for that
day, which is not what i want it to do. Is thier an easier way to thist?
any help would be greatly appreciated.

Thanks,

Steve K.