Sales Report Help

[Sean Tech]

Hello,
I am currently working on a sales tracking report and could use some assistance. Currently I have a form that the user opens and picks a week date to view the report. In the form there is multiple text boxes with date ranges that auto populate once the week is chosen. What I am looking for the report to do is give me “Net Sales” for the week that was chosen, month of the week that was chosen and the year all grouped by sales person.

Example:
Sales person   Chosen Week	Month	       Year
	        Net Sales      Net Sales    Net Sales
Sales Person 1	 10,000	       100,000	    1,000,000
Sales Person 2	 10,000	       100,000	    1,000,000
Total	         20,000	       200,000	    2,000,000

I have created queries for week, month, and year but I really do not want to do 3 sub reports if it is not necessary. I apologize if all the required information has not been supplied; I am unsure what exactly is needed. I am however fairly knowledgeable with access and just can’t figure out the best way to go about doing this.
Tried Thus Far:
1. Crosstab Query
o Could not get columns formatted properly. (Crosstab Query Knowledge is weak)
2. Combined Query – Combination of Weekly, Monthly, and Yearly queries
o Numbers way off
3. Regular Query
o Tried to group [Sale_Date] by week, month, year (Provided wrong results)

Thank You,
Sean

[yarbrough40]

One way would be to create four queries, three for your week totals, month totals, year totals respectively then a fourth to bring them all into a single result.

[Sean Tech]

Thank You for replying so quickly.

I have tried to put all of the queries together I have noticed one problem with that. One I get duplicate values and I am unsure how to hide them.

[yarbrough40]

by duplicate "values" do you mean duplicate records?
you shouldn't be getting duplicate records if you are doing sums of sales amounts grouped by person. have you tried using SELECT DISTINCT?

[Sean Tech]

I just tried SELECT DISTINCT and did not change anything. The only way I could add all the queries together without getting bogus numbers is by doing a totals with all the queries.

By duplicates I mean because every query I add it duplicates the sum values. I managed to find out how to omit duplicates in a report. However, because I am combining queries there is not way to group them. Meaning I have the wrong data for the wrong person

[yarbrough40]

ok supposing you have 3 queries: Week_qry, Month_qry, Year_qry. and each have 2 columns: SalesPerson, NetSales

you can get your result with a query like so

SELECT Month_qry.SalesPerson, Week_qry.NetSales AS [Week Sales], Month_qry.NetSales AS [Month Sales], Year_qry.NetSales AS [Year Sales]
FROM Week_qry INNER JOIN (Month_qry INNER JOIN Year_qry ON Month_qry.SalesPerson = Year_qry.SalesPerson) ON Week_qry.SalesPerson = Month_qry.SalesPerson;

if you are getting duplicate records then one of the original 3 queries is producing more than one record for each sales person and you'll have to fix that.

[Sean Tech]

That did work, I did not know it was possible to do inner joins with-in the query.Thank You. I do have another problem now however. some weeks there is no data for some sales people, when there is no data it eliminates that person entirely instead of just not giving data for the week. Is there anyway to prevent this ?

[yarbrough40]

Create yet another query "SalesPerson_qr y" which does a SELECT DISTINCT SalesPerson. then you can call this query with a LEFT JOIN to your original results query like so:

SELECT SalesPerson_qry.SalesPerson, Result_qry.[Week Sales], 
Result_qry.[Month Sales], Result_qry.[Year Sales]
FROM SalesPerson_qry 
LEFT JOIN Result_qry ON SalesPerson_qry.SalesPerson = Result_qry.SalesPerson;

This solution starts to pain me to look at, though because your creating so many query objects. nothing wrong with it really I just like single query solutions where possible. You could do all this utilizing subqueries too.

[Sean Tech]

I got it to work by changing how the relationships worked. Thank you very much for your help yarbrough.

Take Care,
Sean