Dynamic Column Headings based on crosstab report

  • Thread starter Thread starter Deepak
  • Start date Start date
D

Deepak

Hi
I have table containing all data. Table design is as follows
Field Type
1 Dtd Date/Time
2 StoreName Number; linked with other table storename having store
name details
3 Receipt Qty Number
4 Consumed Qty Number

In all these stores, everyday some material comes (in tonnes), which is
entered in receipt and some material is consumed which is entered in consumed
qty. the material is same for both

So I have e.g,
date Store R C
1-Jan-8 ABC 100 80
1-Jan-8 DEF 60 70
2 Jan-8 ABC 90 90
2 Jan-8 DEF 80 70
enteries in my table. I have as many as 15 stores like this........for which
data needs to be entered daily.

Now i want to generate a report for a particular month like this

Store R/C/S 1 2 3 4 ...... upto
last day of month
ABC R 100 90
C 90 90
S S is stock, on that day.........which can be
calculated from R & C figured and put here

DEF R 60 80
C 70 70
S
I know that i have to use crosstab and vba to generate the report. I have
read several articles on net but none to my help. If you guys could help me
out, i shall be highly thankful to you
 
Try a union query then a crosstab --
Deepak_1 --
SELECT Deepak.Dtd, Deepak.StoreName, "R" AS TransTypr, Deepak.[Receipt Qty]
AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "C" AS TransTypr,
Deepak.[Consumed Qty] AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "S" AS TransTypr, Deepak.[Receipt
Qty]-Deepak.[Consumed Qty] AS QTY
FROM Deepak;


TRANSFORM Sum(Deepak_1.[QTY]) AS SumOfQTY
SELECT Deepak_1.[StoreName], Deepak_1.[TransTypr]
FROM Deepak_1
GROUP BY Deepak_1.[StoreName], Deepak_1.[TransTypr]
PIVOT Format([Dtd],"d");
 
Hello Karl
thanks for helping me out.
As u have said i have first created union query and then crosstab. My Sql
for crosstab is:

PARAMETERS [Forms]![DAILY TREND]![trend_date] DateTime;
TRANSFORM Sum([Daily Trend].QTY) AS SumOfQTY
SELECT [Daily Trend].[Store Name], [Daily Trend].TransTypr, Sum([Daily
Trend].QTY) AS [Total Of QTY]
FROM [Daily Trend]
WHERE ((([Daily Trend].Dtd) Between DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date]),1) And
DateSerial(Year([Forms]![DAILY TREND]![trend_date]),Month([Forms]![DAILY
TREND]![trend_date]),Day(DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date])+1,0)))))
GROUP BY [Daily Trend].[StoreName], [Daily Trend].TransTypr
PIVOT Format([Dtd],"Short Date");

I have created a form DAILY TREND, when user selects a date from it, its
passed onto crosstab query which sorts out the data between First and Last of
month of date selected.

Now My problem is that when i create report based on crosstab, the number of
columns dont change as do the days in month.

I found article on microsoft site:
http://support.microsoft.com/kb/328320
But this only prints only the first record in the report, although there are
44 more records in crosstab query.

I checked the article and found that in Page Header Format section there is
no loop only if statement. I put there Do While but then it prints only the
last record.

Pl help me out, as i am novice in VBA. Any help will be greatly appreciated.


KARL DEWEY said:
Try a union query then a crosstab --
Deepak_1 --
SELECT Deepak.Dtd, Deepak.StoreName, "R" AS TransTypr, Deepak.[Receipt Qty]
AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "C" AS TransTypr,
Deepak.[Consumed Qty] AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "S" AS TransTypr, Deepak.[Receipt
Qty]-Deepak.[Consumed Qty] AS QTY
FROM Deepak;


TRANSFORM Sum(Deepak_1.[QTY]) AS SumOfQTY
SELECT Deepak_1.[StoreName], Deepak_1.[TransTypr]
FROM Deepak_1
GROUP BY Deepak_1.[StoreName], Deepak_1.[TransTypr]
PIVOT Format([Dtd],"d");

--
KARL DEWEY
Build a little - Test a little


Deepak said:
Hi
I have table containing all data. Table design is as follows
Field Type
1 Dtd Date/Time
2 StoreName Number; linked with other table storename having store
name details
3 Receipt Qty Number
4 Consumed Qty Number

In all these stores, everyday some material comes (in tonnes), which is
entered in receipt and some material is consumed which is entered in consumed
qty. the material is same for both

So I have e.g,
date Store R C
1-Jan-8 ABC 100 80
1-Jan-8 DEF 60 70
2 Jan-8 ABC 90 90
2 Jan-8 DEF 80 70
enteries in my table. I have as many as 15 stores like this........for which
data needs to be entered daily.

Now i want to generate a report for a particular month like this

Store R/C/S 1 2 3 4 ...... upto
last day of month
ABC R 100 90
C 90 90
S S is stock, on that day.........which can be
calculated from R & C figured and put here

DEF R 60 80
C 70 70
S
I know that i have to use crosstab and vba to generate the report. I have
read several articles on net but none to my help. If you guys could help me
out, i shall be highly thankful to you
Click to expand...
Click to expand...
 
As per my previous reply "There is a solution for dynamic
monthly crosstab reports at http://www.tek-tips.com/faqs.cfm?fid=5466."

If your interval is day rather than month, you should be able to figure out
how to implement this. If not, come back with questions.

--
Duane Hookom
Microsoft Access MVP


Deepak said:
Hello Karl
thanks for helping me out.
As u have said i have first created union query and then crosstab. My Sql
for crosstab is:

PARAMETERS [Forms]![DAILY TREND]![trend_date] DateTime;
TRANSFORM Sum([Daily Trend].QTY) AS SumOfQTY
SELECT [Daily Trend].[Store Name], [Daily Trend].TransTypr, Sum([Daily
Trend].QTY) AS [Total Of QTY]
FROM [Daily Trend]
WHERE ((([Daily Trend].Dtd) Between DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date]),1) And
DateSerial(Year([Forms]![DAILY TREND]![trend_date]),Month([Forms]![DAILY
TREND]![trend_date]),Day(DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date])+1,0)))))
GROUP BY [Daily Trend].[StoreName], [Daily Trend].TransTypr
PIVOT Format([Dtd],"Short Date");

I have created a form DAILY TREND, when user selects a date from it, its
passed onto crosstab query which sorts out the data between First and Last of
month of date selected.

Now My problem is that when i create report based on crosstab, the number of
columns dont change as do the days in month.

I found article on microsoft site:
http://support.microsoft.com/kb/328320
But this only prints only the first record in the report, although there are
44 more records in crosstab query.

I checked the article and found that in Page Header Format section there is
no loop only if statement. I put there Do While but then it prints only the
last record.

Pl help me out, as i am novice in VBA. Any help will be greatly appreciated.


KARL DEWEY said:
Try a union query then a crosstab --
Deepak_1 --
SELECT Deepak.Dtd, Deepak.StoreName, "R" AS TransTypr, Deepak.[Receipt Qty]
AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "C" AS TransTypr,
Deepak.[Consumed Qty] AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "S" AS TransTypr, Deepak.[Receipt
Qty]-Deepak.[Consumed Qty] AS QTY
FROM Deepak;


TRANSFORM Sum(Deepak_1.[QTY]) AS SumOfQTY
SELECT Deepak_1.[StoreName], Deepak_1.[TransTypr]
FROM Deepak_1
GROUP BY Deepak_1.[StoreName], Deepak_1.[TransTypr]
PIVOT Format([Dtd],"d");

--
KARL DEWEY
Build a little - Test a little


Deepak said:
Hi
I have table containing all data. Table design is as follows
Field Type
1 Dtd Date/Time
2 StoreName Number; linked with other table storename having store
name details
3 Receipt Qty Number
4 Consumed Qty Number

In all these stores, everyday some material comes (in tonnes), which is
entered in receipt and some material is consumed which is entered in consumed
qty. the material is same for both

So I have e.g,
date Store R C
1-Jan-8 ABC 100 80
1-Jan-8 DEF 60 70
2 Jan-8 ABC 90 90
2 Jan-8 DEF 80 70
enteries in my table. I have as many as 15 stores like this........for which
data needs to be entered daily.

Now i want to generate a report for a particular month like this

Store R/C/S 1 2 3 4 ...... upto
last day of month
ABC R 100 90
C 90 90
S S is stock, on that day.........which can be
calculated from R & C figured and put here

DEF R 60 80
C 70 70
S
I know that i have to use crosstab and vba to generate the report. I have
read several articles on net but none to my help. If you guys could help me
out, i shall be highly thankful to you
Click to expand...
Click to expand...
Click to expand...
 
If you use this --
PIVOT Format([Dtd],"d") IN("1", "2", "3", "4", "5", "6", ......, "28", "29",
"30", "31");
The column names will remain the same.
--
KARL DEWEY
Build a little - Test a little


Deepak said:
Hello Karl
thanks for helping me out.
As u have said i have first created union query and then crosstab. My Sql
for crosstab is:

PARAMETERS [Forms]![DAILY TREND]![trend_date] DateTime;
TRANSFORM Sum([Daily Trend].QTY) AS SumOfQTY
SELECT [Daily Trend].[Store Name], [Daily Trend].TransTypr, Sum([Daily
Trend].QTY) AS [Total Of QTY]
FROM [Daily Trend]
WHERE ((([Daily Trend].Dtd) Between DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date]),1) And
DateSerial(Year([Forms]![DAILY TREND]![trend_date]),Month([Forms]![DAILY
TREND]![trend_date]),Day(DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date])+1,0)))))
GROUP BY [Daily Trend].[StoreName], [Daily Trend].TransTypr
PIVOT Format([Dtd],"Short Date");

I have created a form DAILY TREND, when user selects a date from it, its
passed onto crosstab query which sorts out the data between First and Last of
month of date selected.

Now My problem is that when i create report based on crosstab, the number of
columns dont change as do the days in month.

I found article on microsoft site:
http://support.microsoft.com/kb/328320
But this only prints only the first record in the report, although there are
44 more records in crosstab query.

I checked the article and found that in Page Header Format section there is
no loop only if statement. I put there Do While but then it prints only the
last record.

Pl help me out, as i am novice in VBA. Any help will be greatly appreciated.


KARL DEWEY said:
Try a union query then a crosstab --
Deepak_1 --
SELECT Deepak.Dtd, Deepak.StoreName, "R" AS TransTypr, Deepak.[Receipt Qty]
AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "C" AS TransTypr,
Deepak.[Consumed Qty] AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "S" AS TransTypr, Deepak.[Receipt
Qty]-Deepak.[Consumed Qty] AS QTY
FROM Deepak;


TRANSFORM Sum(Deepak_1.[QTY]) AS SumOfQTY
SELECT Deepak_1.[StoreName], Deepak_1.[TransTypr]
FROM Deepak_1
GROUP BY Deepak_1.[StoreName], Deepak_1.[TransTypr]
PIVOT Format([Dtd],"d");

--
KARL DEWEY
Build a little - Test a little


Deepak said:
Hi
I have table containing all data. Table design is as follows
Field Type
1 Dtd Date/Time
2 StoreName Number; linked with other table storename having store
name details
3 Receipt Qty Number
4 Consumed Qty Number

In all these stores, everyday some material comes (in tonnes), which is
entered in receipt and some material is consumed which is entered in consumed
qty. the material is same for both

So I have e.g,
date Store R C
1-Jan-8 ABC 100 80
1-Jan-8 DEF 60 70
2 Jan-8 ABC 90 90
2 Jan-8 DEF 80 70
enteries in my table. I have as many as 15 stores like this........for which
data needs to be entered daily.

Now i want to generate a report for a particular month like this

Store R/C/S 1 2 3 4 ...... upto
last day of month
ABC R 100 90
C 90 90
S S is stock, on that day.........which can be
calculated from R & C figured and put here

DEF R 60 80
C 70 70
S
I know that i have to use crosstab and vba to generate the report. I have
read several articles on net but none to my help. If you guys could help me
out, i shall be highly thankful to you
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl
I changed from PIVOT Format([Dtd],"d") to PIVOT Format([Dtd],"d") IN("1",
"2", "3", "4", "5", "6", ......, "28", "29", "30", "31"); as u suggested.
But when i parameter dialog box i input a date from september......i see
again 31 months in the query.

My problem is that i have got the desired format in the query as i want in
the report
e.g.

Store R/C/S 1 2 3 4 5 6 7
8 9 ....... 28 or 29 or 30 or 31

ABC R 10 20 30 ... .....
C 10 30 40 ....
S 0 -10 -10 ..........
DEF sIMILARLY
I am not able to build the report from the crosstab query. I have got the
results i want in query but not in report

Pl advise me how to build report from this crosstab query.........so that
when user chooses a date in september, in the report it should show 1 to 30
days(i.e. no of days in Sept)....... with data filled upto date choosen and
the rest blank days.

Likewise if the user chooses date in Feb......... report should show (1 to
28 or 29 for leap year)....... with data filled upto date choosen and the
rest blank days.

I have got the query which is showing the data from First of month to date
chosen but i am not able to put the result in report.

I need ur help for the report.

Thanks for all your help KARL

Will be waiting for your response

Deepak


KARL DEWEY said:
If you use this --
PIVOT Format([Dtd],"d") IN("1", "2", "3", "4", "5", "6", ......, "28", "29",
"30", "31");
The column names will remain the same.
--
KARL DEWEY
Build a little - Test a little


Deepak said:
Hello Karl
thanks for helping me out.
As u have said i have first created union query and then crosstab. My Sql
for crosstab is:

PARAMETERS [Forms]![DAILY TREND]![trend_date] DateTime;
TRANSFORM Sum([Daily Trend].QTY) AS SumOfQTY
SELECT [Daily Trend].[Store Name], [Daily Trend].TransTypr, Sum([Daily
Trend].QTY) AS [Total Of QTY]
FROM [Daily Trend]
WHERE ((([Daily Trend].Dtd) Between DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date]),1) And
DateSerial(Year([Forms]![DAILY TREND]![trend_date]),Month([Forms]![DAILY
TREND]![trend_date]),Day(DateSerial(Year([Forms]![DAILY
TREND]![trend_date]),Month([Forms]![DAILY TREND]![trend_date])+1,0)))))
GROUP BY [Daily Trend].[StoreName], [Daily Trend].TransTypr
PIVOT Format([Dtd],"Short Date");

I have created a form DAILY TREND, when user selects a date from it, its
passed onto crosstab query which sorts out the data between First and Last of
month of date selected.

Now My problem is that when i create report based on crosstab, the number of
columns dont change as do the days in month.

I found article on microsoft site:
http://support.microsoft.com/kb/328320
But this only prints only the first record in the report, although there are
44 more records in crosstab query.

I checked the article and found that in Page Header Format section there is
no loop only if statement. I put there Do While but then it prints only the
last record.

Pl help me out, as i am novice in VBA. Any help will be greatly appreciated.


KARL DEWEY said:
Try a union query then a crosstab --
Deepak_1 --
SELECT Deepak.Dtd, Deepak.StoreName, "R" AS TransTypr, Deepak.[Receipt Qty]
AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "C" AS TransTypr,
Deepak.[Consumed Qty] AS QTY
FROM Deepak
UNION SELECT Deepak.Dtd, Deepak.StoreName, "S" AS TransTypr, Deepak.[Receipt
Qty]-Deepak.[Consumed Qty] AS QTY
FROM Deepak;


TRANSFORM Sum(Deepak_1.[QTY]) AS SumOfQTY
SELECT Deepak_1.[StoreName], Deepak_1.[TransTypr]
FROM Deepak_1
GROUP BY Deepak_1.[StoreName], Deepak_1.[TransTypr]
PIVOT Format([Dtd],"d");

--
KARL DEWEY
Build a little - Test a little


:

Hi
I have table containing all data. Table design is as follows
Field Type
1 Dtd Date/Time
2 StoreName Number; linked with other table storename having store
name details
3 Receipt Qty Number
4 Consumed Qty Number

In all these stores, everyday some material comes (in tonnes), which is
entered in receipt and some material is consumed which is entered in consumed
qty. the material is same for both

So I have e.g,
date Store R C
1-Jan-8 ABC 100 80
1-Jan-8 DEF 60 70
2 Jan-8 ABC 90 90
2 Jan-8 DEF 80 70
enteries in my table. I have as many as 15 stores like this........for which
data needs to be entered daily.

Now i want to generate a report for a particular month like this

Store R/C/S 1 2 3 4 ...... upto
last day of month
ABC R 100 90
C 90 90
S S is stock, on that day.........which can be
calculated from R & C figured and put here

DEF R 60 80
C 70 70
S
I know that i have to use crosstab and vba to generate the report. I have
read several articles on net but none to my help. If you guys could help me
out, i shall be highly thankful to you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top