krisred
asked on
Sales Analysis Report
Hi,
I would really appreciate if somebody could walk me throught the following report
I need to create a report with the following fields:
Customer Code | Customer Name | Sales month to date (current year) | Sales month to date (last year) | current year to date | last year to date | % Change by year
Say if this is october 10/31 I want to see all the customers with october sales till 31st for the current year and the same for last year.The year to date for current year and last year should be user defined dates and sppropriate totals
Fields in the table are:
sldno (customer Code)
Cusnme (Customer Name)
dteshp (Date Shipped)
total_net (total amount)
Please advice
Thanks,
kris
I would really appreciate if somebody could walk me throught the following report
I need to create a report with the following fields:
Customer Code | Customer Name | Sales month to date (current year) | Sales month to date (last year) | current year to date | last year to date | % Change by year
Say if this is october 10/31 I want to see all the customers with october sales till 31st for the current year and the same for last year.The year to date for current year and last year should be user defined dates and sppropriate totals
Fields in the table are:
sldno (customer Code)
Cusnme (Customer Name)
dteshp (Date Shipped)
total_net (total amount)
Please advice
Thanks,
kris
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
furtheron you will need to build sums per customer
lets call the first query "qry_base"
SELECT qry_base.sIdno, qry_base.Cusnme, Sum(qry_base.tm) AS [Summe von tm], Sum(qry_base.tmly) AS [Summe von tmly], Sum(qry_base.tytd) AS [Summe von tytd], Sum(qry_base.lytd) AS [Summe von lytd]
FROM qry_base
GROUP BY qry_base.sIdno, qry_base.Cusnme;
this should be the datasource for your report
Regards, Franz
lets call the first query "qry_base"
SELECT qry_base.sIdno, qry_base.Cusnme, Sum(qry_base.tm) AS [Summe von tm], Sum(qry_base.tmly) AS [Summe von tmly], Sum(qry_base.tytd) AS [Summe von tytd], Sum(qry_base.lytd) AS [Summe von lytd]
FROM qry_base
GROUP BY qry_base.sIdno, qry_base.Cusnme;
this should be the datasource for your report
Regards, Franz
i changed the fieldnames of the second query:
SELECT qry_base.sIdno, qry_base.Cusnme, Sum(qry_base.tm) AS [tm], Sum(qry_base.tmly) AS [tmly], Sum(qry_base.tytd) AS tytd], Sum(qry_base.lytd) AS [lytd]
FROM qry_base
GROUP BY qry_base.sIdno, qry_base.Cusnme;
i did a basic report with the %
download and fill in testdata:
http://www.tplus.at/~sissi-franz/test/srep.mdb
regards, Franz
SELECT qry_base.sIdno, qry_base.Cusnme, Sum(qry_base.tm) AS [tm], Sum(qry_base.tmly) AS [tmly], Sum(qry_base.tytd) AS tytd], Sum(qry_base.lytd) AS [lytd]
FROM qry_base
GROUP BY qry_base.sIdno, qry_base.Cusnme;
i did a basic report with the %
download and fill in testdata:
http://www.tplus.at/~sissi-franz/test/srep.mdb
regards, Franz
ASKER
Hi Fraz,
I really appreciate your help . It was very helpful.
I created the report and it work, but what the are the date ranges for last year and current year sales. Is it the calender year or ?
Is there any I can have a prompt for the dates?
Thanks again,
Kris
I really appreciate your help . It was very helpful.
I created the report and it work, but what the are the date ranges for last year and current year sales. Is it the calender year or ?
Is there any I can have a prompt for the dates?
Thanks again,
Kris
ASKER
Hi Franz,
Also, when I create the percentage field I get a error running the query. I think its because of division by zero, if the last year sales were 0 then it creates a problem
Thanks,
Kris
Also, when I create the percentage field I get a error running the query. I think its because of division by zero, if the last year sales were 0 then it creates a problem
Thanks,
Kris
date ranges tytd and lytd
...Month(Now())+1>Month([d teshp]) ....
is actual month and all months before, same for this year and last year
otherwise a comparison would make liitle sense, i think
to avoid a null division error:
If you use A2000 or above, there is a Nz() function to wrap th Formula
otherwise you can exclude with an If-Clause
if you experience further problems post at this thread again
Regards, Franz
...Month(Now())+1>Month([d
is actual month and all months before, same for this year and last year
otherwise a comparison would make liitle sense, i think
to avoid a null division error:
If you use A2000 or above, there is a Nz() function to wrap th Formula
otherwise you can exclude with an If-Clause
if you experience further problems post at this thread again
Regards, Franz
Kind regards,
Sven Karlsen