Access TRANSFORM (Crosstab) Queries and Missing Rows (1 Viewer)

[dugong]

Here is my query.

TRANSFORM Count(t_date.ftime) AS Sumftime
SELECT t_date.gh
FROM t_date
GROUP BY t_date.gh
PIVOT t_date.fdate;

What i'm doing is count the number of clocking per points number. My problem is if there is no clocking for one date, it will not show this date in the query.


So i will only have several dates in a month instead of full days and months.Is there a work around for this?


Thanks


p/s:i've attached my query result with this post

 

[DCrake]

To force the column headings to appear in a crosstab query you need to populate the columns headings property in the column column. Handy if you are grouping by month, hard work if doing it by date. If you are up on VBA you could code the column heading prior to running the query.

David

 

[dugong]

To force the column headings to appear in a crosstab query you need to populate the columns headings property in the column column. Handy if you are grouping by month, hard work if doing it by date. If you are up on VBA you could code the column heading prior to running the query.

David

Thanks for the reply david.

I need to sort the result by month and day.

e.g:

points | december | 01 | 02 | 03 ....until the end of the day
00xxx1 10 11 15
00xxx2 5 6 10
00xxx3 11 10 12



p/s:i know a good example frm here -->

http://ewbi.blogs.com/develops/2007/04/expanding_an_ac.html

but it's not very practical as i need to create the date manually


thanks again.

 

[Simon_MT]

The problem with crosstabs is columns are variable depending on the presence of data unless you can force the date in via an external table with all the dates you require and link into t_date and see what's there. Its a bit like going fishing.

Simon

 

[DCrake]

The easiest way is to run a procedure prior to calling the query to set the column headings for your crosstab.

Lets assume we have picked a particular month, say December.

We know the name of the month
We know that there will be a maximum number of 31 days in the month

Function GetDaysInMonth(AnyMonth As String) As String

Dim dtm as Date
Dim cHeadings As String

dtm = #01-" & AnyMonth & "-" Year(Date) & # ' First day of chosen month

For X = 1 To 32
   If Month(dtm) <> AnyMonth Then
      Exit For
   End If
   cHeadings = cHeadings & Chr(34) & Day(dtm) & Chr(34) & ","
   dtm = DateAdd("d",1,dtm) ' increment date by one day
Next

'So what we have now is "1","2","3",etc,"31",


'Now we drop the final comma
cHeadings = Left(cHeadings,Len(cHeadings)-1)


Next we encompass it in an In statement

cHeadings = " In(" & cHeadings & ")"

GetDaysInMonth = cHeadings

End Function

Finally what we need to do now is insert this into the sql statement for the query we are about to run.

So we call the following function with two arguments

a) then month
b) the name of the crosstab query

Function UpdateColumnHeadings(AnyMonth As String, AnyQuery As String)

Dim Headings as String

'Create the headings
Headings = GetDaysInMonth(AnyMonth)

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs(AnyQuery)

'Update the query sql
qryTest.SQL = Replace(qryTest.Sql,";",Headings & ";")

End Function

We have now tagged the column headings onto the end of the sql statement (After the PIVOT line)

To check it has worked design the query and look at the column headings property under the field supplying the days of the month.
now run the query.

You may need to tweek it but in essense this is how to do it.

Remember this is all aircode and is untested.

David

 

[dugong]

The problem with crosstabs is columns are variable depending on the presence of data unless you can force the date in via an external table with all the dates you require and link into t_date and see what's there. Its a bit like going fishing.

Simon

So the example given in the link is the only solution? I mean we need to create an external table with all the dates required.

My concern is , the table will be a long one as the incoming data would scretch up to years...imagine until 2020

.... also don't forget i still need to figure out total days for each month for each year....

Is there any build-in date table in access that follow the pc date and time?

 

[dugong]

Ok,i've found out a probable solution from another forum but i still need some help to calculate total days for each month.The file is included in the attachment...

Jan - 31
Feb - 28 or 29
Mar - 31
Apr - 30
May - 31
June - 30
July - 31
Aug - 31
Sept - 30
Oct - 31
Nov - 30
Dec - 31

Do i need to create a separate table for days or write an algorithm to calculate the different days in VBA?

My report will be based on month of year.Something like this :

Points | Jan 2008 | 01 | 02 | 03 | 04 | 05
00xxx1 10 11
00xxx2 11 10
00xxx3 10 10
00xxx4 12 10


and the same format for each month....the month will be replaced with a combo box or list box

 

[dugong]

I got another question to ask:

How to insert a form into this query?

[LIST=1]
[*] SELECT qryFlatCalendar.dteDate
[*] FROM qryFlatCalendar
[*] WHERE (((qryFlatCalendar.dteDate)<=#1/31/2008# And (qryFlatCalendar.dteDate)>=#1/1/2008#));
[*]
[/LIST]

I've tried this one... but not possible...

[LIST=1]
[*] SELECT qryFlatCalendar.dteDate
[*] FROM qryFlatCalendar
[*] WHERE (qryFlatCalendar.dteDate) <= #31-01-(Forms!monthly_rapport!combo10)#;
[*]
[/LIST]

I wanted the possibility to change the year or date using combo box, hopefully someone could guide me.



Thanks again.