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.

Public Function DaysOfCurrentMonth()
 
    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
 

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]

@FishVal

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_rapport!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_rapport!combo10 and Forms!monthly_rapport!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.

@dugong

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

[dugong]

@FishVal
Actually qtsj.dbf is one of linked table from a foxpro software (if i've not mistaken) that we have in the office.I wanted to create a simple report generator based from the original one as it has bugs.

-Using the command,qtsj.rq is a string but indeed it's a date i just didnt realise it's treated as a string.So do i need to copy the original table and reformat the field?

-I use those commands merely as a 'makeup' to be able to match the date format in a calendar form i've found on the net.

the format from qtsj.rq is yyyymmdd while in the calendar form is dd-mm-yyyy

Is there any easy way to solve this problem?

[FishVal]

Hello, dugong.

If you want to use values from [qtsj] as dates - e.g. compare with dates from the form, you have no option but to convert them to date values, otherwise comparisson operations (particularly BETWEEN ... AND ... you use in your query) will not work as expected.

To achieve this I suggest you to parse string value to year, month, day parts (as you've already done), convert them to number values using Val() function and use thus obtained values as DateSerial() function arguments to obtain date value.

Regards,
Fish

[dugong]

@FishVal
Thanks for your reply.

But i'm kinda stuck right now , here's my linked dbf files that come from the original software. (reborn v0.3)

QTSJ.dbf is hardware generated data (points no,clocking date and time) while RSDA.dbf is user generated data (points no,points name,group and order no)

Based on your example in crosstab query, i must put an autonumber field in the table. But this feature isn't available in make-table query.So how does i count the clocking sums for each day?

My real plan is to create a report not only showing clocking sums per points number for each day.But also the clocking time for each day!

Screenshot below (monthly_report)

[FishVal]

@dugong
Hello, dugong.

Autonumber field was used as example only.
You may use any as long as it is unique within grouped range.
I guess [points no] will be suitable.
Or you could use Count(*) to count all records within grouped range.

[dugong]

@FishVal
Thanks for your reply.I've followed your suggestion and of course it works!

PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
TRANSFORM Count(t_2.gh) AS cnt1
SELECT t_2.gh
FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
GROUP BY t_2.gh
PIVOT Format([dteDate],"dd-mmm-yyyy");
 

But now my problem is to insert 'qtsj.hs1' and 'qtsj.ms1' (or time) into the crosstab query according to points and sorted by date.

How is this possible since i must use transform function in the query? Obviously i don't want to make any mathematical operation for time..


Thanks

[FishVal]

Hello, dugong.

... insert 'qtsj.hs1' and 'qtsj.ms1' (or time) into the crosstab query according to points and sorted by date ...

What does it mean?

[dugong]

@FishVal
Erm see screenshot above or download the screenshot (monthly_report.zip) if it's too small.

It will be something like this:

------------------------------------------------------------------------------

Points No | Sum | 01-Jan-08 ...............31-Jan-08

0xxxx1 ___ 2 _____14.30 ___________and so on
_________________14.45


0xxxx2 ___ 3 ____15.00____________and so on
________________ 15.05
_________________15.10

------------------------------------------------------------------------------------

It's like a monthly summary @ report with each points details pivoted by date and data inside is the corresponding time clocked for that date.


regards

[FishVal]

Screenshots are great but don't make much sense for developer.
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[dugong]

@FishVal
Sorry about the incovenient.Here's the MetaData:

Original qtsj.dbf

gh - text
rq - text
hs1 - number
ms1 - number 
wn - text     (hardware serial number)
 

t_2 table (coming from make-table query)

gh - text
year - text
month - text
day - text
zdate - time/date  (formated , 2-12-2008)
rtime - text  (eg: 8:40)
 

regards

[FishVal]

Ok. It makes much more sense now.

So, I guess you need to join [t_2] and [qtsj] on [gh] field and use the resulting dataset in the query instead of [t_2].
However, it isn't clear to me what type of relationship is supposed to be between [t_2] and [qtsj].
Is that one-to-one or one-to-many. Also, I'm not sure whether it should be an outer or inner join.

Regards,
Fish

[dugong]

@FishVal
t_2 is created to copy the data in qtsj and to format the date since qtsj is a linked table.so i use t_2 as qtsj replacement.

the one that must be related later is rsda.dbf with qtsj.dbf...or with my current data it would be rsda.dbf with t_2

(since i've replaced and reformated qtsj with t_2)

If it's possible please take a glance on my uploaded data (reborn v0.3).

am still stuck with the time problem,any ideas?

[FishVal]

Ok.

I imported rsda.dbf to Access database, for qtsj.dbf it says "External table is not in expected format".

Hmm..

What could I say. Neither content, no field names make much sense.
So, how these two datasets are supposed to be treated together?

Kind regards,
Fish.

[dugong]

@FishVal
To be able to link the qtsj table you must use the odbc driver.

The data above is merely an example,here with my post i attach a real clocking data from one of our clients.The rsda.dbf table come from the user who manually key-in the required information.

Here's what rsda.dbf real data look like:

The required field are:

1.Number
(Obviously we must know the points serial number first before we can key-in the information)

2.Name
(Put a desired name which is normaly the location name where's the points is fixed)

3.Group Number
(If there's too many points,we can group the points into 10 per group.This group is actually used in search function)

4.Order
(The order of the points in the report)

*The description given are based on the original software.My goal is to create a simple monthly report generator based on the original software using linked qtsj.dbf and rsda.dbf tables in the original software folder.

Here's an example of the report that i wanted to create:

As you can see,there's Name field which actually come from rsda.dbf table and the order is also sorted from there.


Until this post,i haven't touched the rsda.dbf table part yet..but by following your guidance i've been able to create a month pivot table with sum for each day which will be used to create the report.

Am only lacking clocking time for each date....

(and of course the points name also which will be added later,until now i've only used the points serial number)

I think rsda.dbf and qtsj.dbf must be JOINED (Where rsda.dbf's points serial number = qtsj.dbf point's serial number ) to be able to create the complete report.


regards

[dugong]

Ok,for the odbc driver i've used this one:

DSN=Visual FoxPro Tables

Some tutorial on how the original software works:

1.User key-in the points serial number,and then put a related name into the points number,group number and the order. (this will create the rsda.dbf table)

2.Guards go to each points which fixed in e.g real estate area to do clocking.Say that there's 10 points,so there will be 10 different points serial number and names.The time differences to go to each points location will create 10 different clocking time.

3.What clocking do in the sense of hardware:it will record the points serial number with time and date.

4.So that when someone download the data to the original software.It will create a qtsj.dbf file.

5.When the user goes to view report,the software will match the points serial number with an existing one in rsda.dbf.If there's no match,the software can only show the points serial number with date and time. (which is logic)

6.If there's a match,the report will show user the points number (with name given in rsda.dbf) clocking time pivoted by days in a month.


Hope this description helps.

here's the real data that i've screen-shooted above:

RapidShare: Easy Filehosting

[dugong]

Ok,i've found out something.

I've changed the transform statement from my current code :

PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
TRANSFORM Count(t_2.gh) AS cnt1
SELECT t_2.gh
FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
GROUP BY t_2.gh
PIVOT Format([dteDate],"dd-mm-yyyy");
 

To this one:

PARAMETERS [Forms]![monthly_rapport]![combo10] Value, [Forms]![monthly_rapport]![combo8] Value;
TRANSFORM FIRST(t_2.rdate) AS cnt1
SELECT t_2.gh
FROM t_2 RIGHT JOIN qry_test_t_2 ON t_2.zdate=qry_test_t_2.dteDate
GROUP BY t_2.gh
PIVOT Format([dteDate],"dd-mm-yyyy");
 

Result:

But of course i can only 'pull' one time from this query for each date..

Any idea how to list all the time for the whole day?


Regards

[FishVal]

Hello, dugong.

Sorry for delay.
What you are looking for could be done with custom aggregate function.
Here I will attach database sample implementing such.

Regards,
Fish

[dugong]

@FishVal
Many thanks Fishval!

I'll definitely look into it and show how far can i go with this one.

[dugong]

Ok.Here's one issue:

Public Function Concat(lngID As Variant) As Variant
 
    If IsNull(lngID) Then Exit Function
 
    Dim rs As New ADODB.Recordset
 
    With rs
 
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open "SELECT t1.* FROM tblValues AS t1 INNER JOIN" & _
            " tblValues AS t2 ON t1.lngRowNum=t2.lngRowNum AND t1.txtColName=t2.txtColName" & _
            " WHERE t2.keyValueID=" & lngID & ";"
 
        While Not .EOF
            Concat = Concat & !txtValue & ";"
            .MoveNext
        Wend
 
        .Close
 
    End With
 
    Set rs = Nothing
 
End Function
 

The KeyValueID is keep haunting me,my table is not using autonumber.

I only got gh (lngRowNum) , zdate (txtColName) and rtime (txtValue) but there's no KeyValueID.

How can i modify this module to suits my table?


Regards

[FishVal]

Hello, dugong.

[keyValueID] in the example is just a primary key.
It could be of any type (not only Autonumber), as long as it is unique in the table.
Do you have primary key or unique field in your table?

Regards,
Fish

[dugong]

@FishVal
Actually i've tried to change the original module to this one:

Option Compare Database
 
Public Function Concat(lngID As Variant) As Variant
 
    If IsNull(lngID) Then Exit Function
 
    Dim rs As New ADODB.Recordset
 
    With rs
 
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open "SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
            " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
            " WHERE t2.gh=" & lngID & ";"
 
        While Not .EOF
            Concat = Concat & !rtime & ";"
            .MoveNext
        Wend
 
        .Close
 
    End With
 
    Set rs = Nothing
 
End Function
 

My unique field is gh (points serial number) but it just don't work...was shown some error (exponentiel if i've not mistaken)

Here's my table:

gh - text
year - text
month - text
day - text
zdate - time/date  (formated , 2-12-2008)
rtime - text  (eg: 8:40)
 

Thanks again for your help!


regards

[dugong]

My query code:

SELECT t_2.* , concat (t_2.gh) AS rdatelist
FROM t_2 ;
 

The error message is:
"Data type mismatch in criteria expression"

Does this mean gh is not formated correctly or what?


regards

[FishVal]

Quotes (') and Double-Quotes (") - Where and When to use them.

Regards,
Fish

[dugong]

@FishVal
Hi FishVal,

Could you tell me whether i'm in the right direction or not? Are (') and (") the only problem in my code?

I've already checked the link given several times and i've tried to compare my code one-by-one, it seems all fine.I'm just confused..


regards

[FishVal]

Hello, dugong.

Check code of Concat() function.

.Open "SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
            " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
            " WHERE t2.gh=" & lngID & ";"
 

Value of lngID variable has to be enclosed in quotes as it is compared with Text type field.
" WHERE t2.gh='" & lngID & "';"

Regards,
Fish.

[dugong]

@FishVal

Ok.I've changed the code and re-run the query but i get this error:

Syntax error (missing operator) in query expression 't2.gh='

I've tried again with " "& IngID & " ";" but i was shown

Syntax error


did we missed something else on the code?


regards

[FishVal]

There is a rule of thumb when dealing with SQL expressions generated and used in code.
Get runtime value and try to execute it in query editor.

Regards,
Fish.

[dugong]

@FishVal
I'm pretty new with sql.Could someone tell me how to do this? What runtime value required and what query editor is recommended?


Thanks

[FishVal]

Hello, dugong.

What goes after ".Open" in code is being evaluated at run time to a string containing SQL expression.
VBA is of no help as for debugging SQL expressions being used in code.
So, if you need to clarify what is wrong with your SQL expression you need to obtain somehow the real value passed to Open method and debug it in query editor (aka query design view).

• Make thefollowing changes to the code of Concat() function
  

  Option Compare Database
   
  Public Function Concat(lngID As Variant) As Variant
   
      If IsNull(lngID) Then Exit Function
   
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
   
      strSQL="SELECT t1.* FROM t_2 AS t1 INNER JOIN" & _
              " t_2 AS t2 ON t1.gh=t2.gh AND t1.zdate=t2.zdate" & _
              " WHERE t2.gh='" & lngID & "';"
      Debug.Print strSQL
   
      With rs
   
          .ActiveConnection = CurrentProject.Connection
          .CursorType = adOpenForwardOnly
          .LockType = adLockReadOnly
          .Open strSQL
   
          While Not .EOF
              Concat = Concat & !rtime & ";"
              .MoveNext
          Wend
   
          .Close
   
      End With
   
      Set rs = Nothing
   
  End Function
   

• When the code fails, go to VBA window -> Immediate pane. If Immediate pane is not visible press Ctrl-G
• Where you will find runtime value of SQL expression used as parameter of Open method.
• Copypaste it to query design SQL view.

Regards,
Fish