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
8 1754
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.
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.
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?
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
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.
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 ?
Create yet another query "SalesPerson_qry" 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.
I got it to work by changing how the relationships worked. Thank you very much for your help yarbrough.
Take Care,
Sean
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike |
last post by:
Hello All,
I'm trying to deploy my windows-based application using
crystal report. i package the setup by including the
crystal report file, the exe file, and two merge modules
for the crystal...
|
by: Tiago Miguel Silva |
last post by:
I there. I´m almost desperate with a report that i´m doing!
I am creating a report using the push method, being the data returned by a
typed dataset.
The report shows activities by week and...
|
by: Neil MacDonald |
last post by:
Hi. Thanks in advance for your help.
I have a database in which one report will not show the text from a memo
field. If I run the underlying query, the field shows up in the table,
with all...
|
by: steven |
last post by:
I am creating some financial reports in Access 2000, and I am having
problem that I can't resolve.
The report sorts information first by vendor, then by budget line
number. It generates the...
|
by: report help |
last post by:
I have a report that is supposed to be two pages. The first page
should contain the header, some data from a query, and the footer. The
second page should contain the header, two subreports, and...
|
by: Takeadoe |
last post by:
Dear NG - I'm very new to Access and reports are completely foreign to
me. I was hoping that I could get some general guidance on how to
proceed with the following:
I've got a form (8.5x11")...
|
by: goldweapon |
last post by:
I need help on creating a report. I have a sales application. Product consists of several parts and the parts have selection of material and then color. The buyer can select different options for...
|
by: DeanL |
last post by:
Hi guys,
I'm in need of a little help with a report I'm setting up on Access
2000. The report shows data depending on entries on a form that has 7
fields (Min and Max Cost, Fiscal Year, Min and...
|
by: ranesmitas |
last post by:
hello,
i using vb 6 and i did report seagate crystal report .
i want to know how it use in vb . i did some code but it not display anything
code is
Public strReportPath As String
Public...
|
by: adsaca |
last post by:
there basic sections in Crystal report namely
Report Header
Page Header
Group Header
Details
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |