displaying data by month

  • Thread starter Thread starter PF
  • Start date Start date
P

PF

Hi all,

i have sales data by product for 12 month

i need to store that in a table

i have to display it in a form like product, sales1, sales2, ....sales12 on
ONE line

i put the data in a table like

tblData
----------
prodNo
sales
monthNo

How can i use a select to display the productNo plus all the sales on a
single line.
Many month have sales = 0
So i choose a table with few rows an a month number
Do i have to go back to the 13 coloms row to do what i want?

regards,
pierre
 
You can probably use a crosstab query. Set the ProdNo as the Row Heading,
"Sales" & MonthNo as the Column Heading and Sum of Sales as the Value. Set
the Column Headings property to:
sales1, sales2, ....sales12
 
Thanks for the answer;

I dont want to use a crosstab query. It is quite to slow.
My recordset is big.

Is it possible that the best way to do what i want is
to put the data in 12 colums to have speed of response?

There must be a way of selecting the month in 12 colum
while the data is in one colum for the data and one column for the month.

What am i missing here?


regards
pierre
 
I'm not sure what type of solution will be faster than a Crosstab query
where you have entered the column headings. Make sure the ProdNo and MonthNo
are indexed.
 
PF,

Whatever you do, don't revert back to using a separate column for each
month. That is just bad database practice. I'm with Duane regarding using
the cross tab query. Although it might be a little slow, it should not be
significantly slower than any other aggregation query if your fields are
indexed properly.

Dale
 
Pierre,

I had a similar requirement once - maybe this will be what you are looking
for...

Assumptions:

sales is currency or numeric
monthno is integer (ie. Jan=1, Feb=2, Mar=3, etc.)

There is no "year" limiter. If the recordset has 5 years of data for product
"AA123", the sum of sales is for the 5 years (but a year parameter could be
added).


Create a new query. In SQL view paste the following:

SELECT tblData.ProdNo, Sum(IIf([MonthNo]=1,[Sales],0)) AS Jan,
Sum(IIf([MonthNo]=2,[Sales],0)) AS Feb, Sum(IIf([MonthNo]=3,[Sales],0)) AS
Mar, Sum(IIf([MonthNo]=4,[Sales],0)) AS Apr, Sum(IIf([MonthNo]=5,[Sales],0))
AS May, Sum(IIf([MonthNo]=6,[Sales],0)) AS Jun,
Sum(IIf([MonthNo]=7,[Sales],0)) AS Jul, Sum(IIf([MonthNo]=8,[Sales],0)) AS
Aug, Sum(IIf([MonthNo]=9,[Sales],0)) AS Sep, Sum(IIf([MonthNo]=10,[Sales],0))
AS Oct, Sum(IIf([MonthNo]=11,[Sales],0)) AS Nov,
Sum(IIf([MonthNo]=12,[Sales],0)) AS [Dec]
FROM tblData
GROUP BY tblData.ProdNo;

Save it as subTest1 (if it works, you can rename everything later)


Create another query. In SQL view, paste the following:

SELECT tblData.ProdNo, subTest1.Jan, subTest1.Feb, subTest1.Mar,
subTest1.Apr, subTest1.May, subTest1.Jun, subTest1.Jul, subTest1.Aug,
subTest1.Sep, subTest1.Oct, subTest1.Nov, subTest1.Dec
FROM subTest1 INNER JOIN tblData ON subTest1.ProdNo = tblData.ProdNo
GROUP BY tblData.ProdNo, subTest1.Jan, subTest1.Feb, subTest1.Mar,
subTest1.Apr, subTest1.May, subTest1.Jun, subTest1.Jul, subTest1.Aug,
subTest1.Sep, subTest1.Oct, subTest1.Nov, subTest1.Dec;

Save it as Test1

When run, the left column is prodno, then columns for Jan - Dec.

Months with no sales for the produce will be 0 (zero).
 
I'm not sure why you would create the first query since it is basically the
same as a crosstab. Do you think it is faster or more efficient? Also, why
would you create the second query? Doesn't it show the exact same data as
the first query?

I might be missing something here...
 
It might be a crosstab, but I couldn't get the crosstab to give me the
results I needed for the report; but then again, I haven't spent much time
working with crosstabs (maybe a couple hours).

I could get a query to give me the list of people and their info but not the
totals. And the totals would work but not with the other info I needed. So
after experimenting for a while, I was able to get the two queries to work.

In the totals subquery, I limited the records to one year (form parameter)
and the main query (not a totals query) pulled in the employee data and other
required data from related tables.

It does seem faster that the crosstab, but I only had about a thousand
records as test data (and the crosstab didn't work for me).


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)




Duane Hookom said:
I'm not sure why you would create the first query since it is basically the
same as a crosstab. Do you think it is faster or more efficient? Also, why
would you create the second query? Doesn't it show the exact same data as
the first query?

I might be missing something here...

--
Duane Hookom
MS Access MVP


SteveS said:
Pierre,

I had a similar requirement once - maybe this will be what you are looking
for...

Assumptions:

sales is currency or numeric
monthno is integer (ie. Jan=1, Feb=2, Mar=3, etc.)

There is no "year" limiter. If the recordset has 5 years of data for product
"AA123", the sum of sales is for the 5 years (but a year parameter could be
added).


Create a new query. In SQL view paste the following:

SELECT tblData.ProdNo, Sum(IIf([MonthNo]=1,[Sales],0)) AS Jan,
Sum(IIf([MonthNo]=2,[Sales],0)) AS Feb, Sum(IIf([MonthNo]=3,[Sales],0)) AS
Mar, Sum(IIf([MonthNo]=4,[Sales],0)) AS Apr, Sum(IIf([MonthNo]=5,[Sales],0))
AS May, Sum(IIf([MonthNo]=6,[Sales],0)) AS Jun,
Sum(IIf([MonthNo]=7,[Sales],0)) AS Jul, Sum(IIf([MonthNo]=8,[Sales],0)) AS
Aug, Sum(IIf([MonthNo]=9,[Sales],0)) AS Sep, Sum(IIf([MonthNo]=10,[Sales],0))
AS Oct, Sum(IIf([MonthNo]=11,[Sales],0)) AS Nov,
Sum(IIf([MonthNo]=12,[Sales],0)) AS [Dec]
FROM tblData
GROUP BY tblData.ProdNo;

Save it as subTest1 (if it works, you can rename everything later)


Create another query. In SQL view, paste the following:

SELECT tblData.ProdNo, subTest1.Jan, subTest1.Feb, subTest1.Mar,
subTest1.Apr, subTest1.May, subTest1.Jun, subTest1.Jul, subTest1.Aug,
subTest1.Sep, subTest1.Oct, subTest1.Nov, subTest1.Dec
FROM subTest1 INNER JOIN tblData ON subTest1.ProdNo = tblData.ProdNo
GROUP BY tblData.ProdNo, subTest1.Jan, subTest1.Feb, subTest1.Mar,
subTest1.Apr, subTest1.May, subTest1.Jun, subTest1.Jul, subTest1.Aug,
subTest1.Sep, subTest1.Oct, subTest1.Nov, subTest1.Dec;

Save it as Test1

When run, the left column is prodno, then columns for Jan - Dec.

Months with no sales for the produce will be 0 (zero).
Click to expand...
Click to expand...
 
Back
Top