Cross tab query solution

I

Irshad Alam

I have table Named OutStationTab having the data as below field

EName - Text Field
OsDate - Date Field
OsAmt - Numeric field

I want to make a cross tab query in which
RowHeading- EName
Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy")
Value- OsAmt


Note the data is entered from 15th Date of a month to 14th date of next
month. One Employee take 1 outstation charges only in a day.

I am able to make the crosstab query and its working perfect. Like I filter
the query (From 15-02-2010 to 14-03-2010), then made the CrossTab

query. It works perfect.
It does not display the date on colum in which no one is on outstation.
BUT my requirement is that to display like all the day like - 15/02 16/02
17/02 till.....14/03 . It should NOT miss any day inbetween the

period.

Please advise method to do above, so that all the date inbetween the start
and end date should display.

And also how to handle report. As the month changes and date count/column
also changes (like decrease/increase)

Regards

Irshad
 
T

Tom van Stiphout

On Sat, 27 Mar 2010 05:19:01 -0700, Irshad Alam

The typical solution is to set the ColumnHeadings property of the
query.
You may also be able to join with a table that does have all dates.

-Tom.
Microsoft Access MVP
 
I

Irshad Alam

Dear sir,

Thanks for your advise.

I tried the method shown on the website. But not fully sucessful.

My Sql View is below :

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]);

As I posted test data from 1-Feb2010 to 10-Feb-2010, It viewed ok as shown
on web.

But the problem arises, as said on the web I change the Query properties
colum heading of that field as below :

"D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2",........."D28/2"

All the data of amount disappears, only the rows remain ok. Below is the sql
view after puting the colum heading:

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]) In
("D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2","D10/2");


Please advise

Regards

Irshad


Duane Hookom said:
If this is for a report (or maybe even a form) I would use the solution
found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need to change
all the date intervals from quarter to day. Use a text box on a form to
enter either the beginning or ending date.

--
Duane Hookom
MS Access MVP


Irshad Alam said:
I have table Named OutStationTab having the data as below field

EName - Text Field
OsDate - Date Field
OsAmt - Numeric field

I want to make a cross tab query in which
RowHeading- EName
Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy")
Value- OsAmt


Note the data is entered from 15th Date of a month to 14th date of next
month. One Employee take 1 outstation charges only in a day.

I am able to make the crosstab query and its working perfect. Like I
filter
the query (From 15-02-2010 to 14-03-2010), then made the CrossTab

query. It works perfect.
It does not display the date on colum in which no one is on outstation.
BUT my requirement is that to display like all the day like - 15/02 16/02
17/02 till.....14/03 . It should NOT miss any day inbetween the

period.

Please advise method to do above, so that all the date inbetween the start
and end date should display.

And also how to handle report. As the month changes and date count/column
also changes (like decrease/increase)

Regards

Irshad
Click to expand...
Click to expand...
 
D

Duane Hookom

Remove the Column Headings property so you can actually compare what the
crosstab generates vs what you have entered into the Column Headings
property. I'm not sure how you got 2 numbers with a "/" from "D" and an
integer.


--
Duane Hookom
MS Access MVP


Irshad Alam said:
Dear sir,

Thanks for your advise.

I tried the method shown on the website. But not fully sucessful.

My Sql View is below :

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS
TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]);

As I posted test data from 1-Feb2010 to 10-Feb-2010, It viewed ok as
shown
on web.

But the problem arises, as said on the web I change the Query properties
colum heading of that field as below :

"D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2",........."D28/2"

All the data of amount disappears, only the rows remain ok. Below is the
sql
view after puting the colum heading:

TRANSFORM Max(Query1.EMoney) AS MaxOfEMoney
SELECT Query1.ENo, Query1.Ename, Query1.Ecode, Sum(Query1.EMoney) AS
TotalAmt
FROM Query1
GROUP BY Query1.ENo, Query1.Ename, Query1.Ecode
ORDER BY "D" & DateDiff("d",[EDate],[FromDate])
PIVOT "D" & DateDiff("d",[EDate],[FromDate]) In
("D1/2","D2/2","D3/2","D4/2","D5/2","D6/2","D7/2","D8/2","D9/2","D10/2");


Please advise

Regards

Irshad


Duane Hookom said:
If this is for a report (or maybe even a form) I would use the solution
found at http://www.tek-tips.com/faqs.cfm?fid=5466. You would need to
change
all the date intervals from quarter to day. Use a text box on a form to
enter either the beginning or ending date.

--
Duane Hookom
MS Access MVP


Irshad Alam said:
I have table Named OutStationTab having the data as below field

EName - Text Field
OsDate - Date Field
OsAmt - Numeric field

I want to make a cross tab query in which
RowHeading- EName
Column Heading- Expr1: Format([OsDate],"dd-mm-yyyy")
Value- OsAmt


Note the data is entered from 15th Date of a month to 14th date of next
month. One Employee take 1 outstation charges only in a day.

I am able to make the crosstab query and its working perfect. Like I
filter
the query (From 15-02-2010 to 14-03-2010), then made the CrossTab

query. It works perfect.
It does not display the date on colum in which no one is on outstation.
BUT my requirement is that to display like all the day like - 15/02
16/02
17/02 till.....14/03 . It should NOT miss any day inbetween the

period.

Please advise method to do above, so that all the date inbetween the
start
and end date should display.

And also how to handle report. As the month changes and date
count/column
also changes (like decrease/increase)

Regards

Irshad
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CalcWorkDays error 1
Counts number of entries per day in query 4
Crosstab Query by Week with Dates as Headers 5
Using Crosstab column headings for conditional formatting in report 0
Format a date field in a query 3
Crosstab query column alignment 6
Date query 3
Creating a "grid" of data 3

Top