Access TRANSFORM (Crosstab) Queries and Missing Rows

[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

[FishVal]

Hello, dugong.

There are at least two ways to solve the problem.

• The first is to use TRANSFORM .... PIVOT ... IN (<valueslist>) syntax. To automate the process of <valueslist> adding you will need to design VBA function returning list of sequential dates and put it to query via correspondent Querydef object.
• The second is to :
  • Generate dataset of sequential dates.
  • Outer join it with your table on date field.
  • Perform crosstab query.
  • Remove empty row generated from empty dates.
  
  An example of this is in attachment below.

Regards,
Fish

[dugong]

Hello, dugong.

There are at least two ways to solve the problem.

• The first is to use TRANSFORM .... PIVOT ... IN (<valueslist>) syntax. To automate the process of <valueslist> adding you will need to design VBA function returning list of sequential dates and put it to query via correspondent Querydef object.
• The second is to :
  • Generate dataset of sequential dates.
  • Outer join it with your table on date field.
  • Perform crosstab query.
  • Remove empty row generated from empty dates.
  
  An example of this is in attachment below.

Regards,
Fish

Thanks for your reply Fish.

So whether i pick solution no 1 or 2 , i still need to manually create an external table or key-in all possible months,days and years in mind.

How if my data reach a date beyond 2010 or 2020? I need to calculate the leap year to obtain 29 days february...

Is there any way i could pull the date from the pc date and time and create a table from it?


regards

[FishVal]

Hello, dugong.

Thanks for your reply Fish.

So whether i pick solution no 1 or 2 , i still need to manually create an external table or key-in all possible months,days and years in mind.
How if my data reach a date beyond 2010 or 2020? I need to calculate the leap year to obtain 29 days february...

Going with solution #2, I would create records in [tblYears] up to, let us say, 2100 releaving myself of responsibility for future crash.
No matter whether you go with solution #1 or #2 a leap year date is being processed normally.

Is there any way i could pull the date from the pc date and time and create a table from it?

Mmmm. Sure, there is a way. Just, out of curiosity, what are advatages of this way?

Regards,
Fish

[dugong]

Hello, dugong.



Going with solution #2, I would create records in [tblYears] up to, let us say, 2100 releaving myself of responsibility for future crash.
No matter whether you go with solution #1 or #2 a leap year date is being processed normally.



Mmmm. Sure, there is a way. Just, out of curiosity, what are advatages of this way?

Regards,
Fish

Hello Fish,

I wanted to pull the date from pc clock because i wanted to avoid creating date table scretching to years...

What i mean is,each time my little report generator (access query) started it will check the date first,compare it and then it will add the missing date (days,months and years) into the date table automaticly.

However problem will arise if the PC clock is inaccurate...


regards

[FishVal]

Hello Fish,

I wanted to pull the date from pc clock because i wanted to avoid creating date table scretching to years...

Hmm. I'm not sure what exactly you want to avoid by avoiding table with 100-150 records. BTW, [tblYears] could be replaced with a query returning distinct years from your table thus ensuring that years list is always relevant to database content. Or the list returned by the query could update [tblYears], thus caching it and avoiding performance penalty.

What i mean is,each time my little report generator (access query) started it will check the date first,compare it and then it will add the missing date (days,months and years) into the date table automaticly.

IMHO solution #1 - creation of delimited list of sequential dates, is the same but without that temporary table of dates you want (still without any obvious for me reason). It will require the same code, but different way to use obtained values - concatenating into delimited string vs. storing in temporary table.

However problem will arise if the PC clock is inaccurate...

:) Sure.

Kind regards,
Fish

[dugong]

Thank you for your replies,

I still need some help to calculate total days for each month.What i know is

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....

[FishVal]

Hello, dugong.

You don't need to know how many days in each month as well as you don't need to implement date logic manually. Below is a simple code iterating all days of current month. Pay attention on using of Date(), DateSerial(), Month() and Year() functions. VBA has many functions to opearate with date type variables.

[code=vb]
Public Function DaysOfCurrentMo nth()

Dim dte As Date

'get first day of current month
dte = DateSerial(Year (Date), Month(Date), 1)

'iterate while the date is still within current month
While Month(dte) = Month(Date)
Debug.Print dte
'increment date
dte = dte + 1
Wend

End Function
[/code]

Regards,
Fish

[dugong]

Thanks for your replies.

I got another question to ask:

How to insert a form into this query?

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

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

SELECT qryFlatCalendar.dteDate
FROM qryFlatCalendar
WHERE (qryFlatCalendar.dteDate) <= #31-01-(Forms!monthly_rapport!combo10)#;

Thanks again.

[FishVal]

Hello, dugong.

Use DateSerial() function.

DateSerial(31, 1, Forms!monthly_rapport!combo10)

[dugong]

Hello, dugong.

Use DateSerial() function.

DateSerial(31, 1, Forms!monthly_rapport!combo10)

Thanks.It works just fine!

But the format is (year , month , date )

regards

[dugong]

The form problem is still haunting me...

an error message appear when i tried to run t_Crosstab query

The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)

name in this case is Forms!monthly_r apport!combo10

TRANSFORM Count(t.keyID) AS cnt1
SELECT t.gh
FROM t RIGHT JOIN qry1Month ON t.dte=qry1Month.dteDate
GROUP BY t.gh
PIVOT Format([dteDate],"dd-mmm-yyyy");

[dugong]

Update:

I've found out the solution for the error.

I must put both

Forms!monthly_r apport!combo10 and Forms!monthly_r apport!combo8 to my cross-tab query parameter.



Thanks again.

[dugong]

Ok, another issue arise

I have this code:

SELECT qtsj.rq, Left(rq,4) AS [year], Mid(rq,5,2) AS [month], Right(rq,2) AS [day], [day] & "-" & [month] & "-" & [year] AS [date], Format([date],"dd-mm-yyyy") AS fdate
FROM qtsj;

So i want to search a date (fdate) using this query:

SELECT Query1.fdate
FROM Query1
WHERE Query1.fdate Between [Forms]![record_search]![Text0] And [Forms]![record_search]![Text1];

with the date format inserted in the form dd-mm-yy

e.g 01-10-2008

but my query seems don't work..

[FishVal]

Hello, dugong.

SELECT qtsj.rq, Left(rq,4) AS [year], Mid(rq,5,2) AS [month], Right(rq,2) AS [day], [day] & "-" & [month] & "-" & [year] AS [date], Format([date],"dd-mm-yyyy") AS fdate
FROM qtsj;

• What is the type of [rq] field? You treat it as String, but it could be Date as well. If you are not sure about type, then check it with the following query:
  

  SELECT TypeName(qtsj.rq) FROM qtsj;

• You use field alias names ([year], [month], [day], [date]) for further calculations in the same query where they are introduced. SQL syntax doesn't allow this.
• [date] field is certainly of String type, and could not be formatted in a way you try to do to obtain [fdate].
• You don't use Left() and Right() functions properly in context of expcted format of [rq] field.
• String type data returned by the query could not be compared in the second query in a proper way with form control values.