Re: can't find the how-to steps for query results I need
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Mon, 07 Sep 2009 09:08:53 -0400
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
'====================================================
Renauda wrote:
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
- References:
- can't find the how-to steps for query results I need
- From: Renauda
- can't find the how-to steps for query results I need
- Prev by Date: Re: Date filter
- Next by Date: Re: time calculation problem
- Previous by thread: Re: can't find the how-to steps for query results I need
- Next by thread: Re: time calculation problem
- Index(es):
Relevant Pages
|
