can't find the how-to steps for query results I need

[Renauda]

Hi,

I tried posting in the queries area but will try my luck here too, since I
am a beginner.

I have a table that basically reads like this...date, salesperson, step 1,
step 2, step 3, step 4, step , sold. This tracks the number of contacts for
each salesperson, the number of each steps taken, and the number of sold
units. I need to create a query that spits out the numbers for each sales
person on a month to date basis. I do this 3 times a week and do it the
longggg way cause I don't know how to make the query I need.

This is what I do.
1. filter my sales person
2. sort date by descending order
3. highlight month to date and print the selection
4. manually count each category for each salesperson
5. I enter my totals on a spreadsheet and that's our sales report...giving
numbers on each salesperson

Can anyone tell me how, or where to find the how-to steps to create the
query I need? I've tried tips and lessons on the MS Office site, but to no
avail. It teaches me things but not the complete how-to steps to truly create
the query I need. I found a site called learnaccessnow. Same thing....I've
learned things but not the every step I need to take so I can conquer this
task.

Can anyone help me?

Thanks,

Renauda

 

[June7 via AccessMonster.com]

Use the Report designer or wizard to design a report (I don't like any of the
wizards). Either will help design the report's RecordSource query and
grouping/sorting and statistical calculations. Are you using Access2007?

In step 1. you say 'filter by salesperson', do you mean sort and report all
or filter and just report for one?

In step 4. you say 'manually count each category for each salesperson', do
you mean add the values in the step fields, i.e., step 1 + step 2 + step 3 +
step 4 ?

You should be able to build a report to produce the output described. So
check out the report builder, accomplish what you can, then come back with
specific issues.

 

[John Spencer]

Need some further information from you about your fields.

What are the field types of the Step fields and the Sold field?

Are they yes/no fields or text fields? If they are not Yes/No
(checkbox) fields, what do they contain if the step has been accomplished?

If your fields are Yes/no fields then the expression to count needs to
be different.

Assuming that your fields are Yes/No fields
In the SQL view you would have a query that looked something like the
following to count the August results:

SELECT Format([DateField],"yyyy-mm") as YearMonth
, [SalesPerson]
, Sum(Abs(Step1)) As Count1, Sum(Abs(Step2)) as Count2
, Sum(Abs(Step3)) as Count3, Sum(Abs(Step4)) as Count4
, Sum(Abs(Step5)) as Count5, Sum(Abs(Sold)) as CountSold
FROM [SomeTable]
WHERE [DateField] Between #2009-08-01# and #2009-08-31#
GROUP BY Format([DateField],"yyyy-mm")
, [SalesPerson]
ORDER BY Format([DateField],"yyyy-mm") Desc
, [SalesPerson]

You can do multiple months at once by changing the date range or you can
do the entire database by removing the where clause.

You can build this query in design view.
== Open a new query
== Add your table
== Add the date field, the salesperson field and the other fields
== Set the sort under the datefield to Descending
== Set the sort under the sales person field to Ascending
== Modify the date field to read
YearMonth: Format([DateField],"yyyy-mm")
== Modify the Sold and Step fields to read
Abs([NameOfTheField)
== Choose View: Totals from the menu bar
== Change Group by to Sum under the Sold and Step fields
== If you want to control the date range add the datefield AGAIN
== Change GROUP BY to WHERE
== Enter Date criteria range in the criteria


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================