Access TRANSFORM (Crosstab) Queries and Missing Rows

[dugong]

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

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]

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

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]

.....

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?

....

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]

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.

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]

Hello, dugong.



What does it mean?

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

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

[dugong]

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

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

Sorry about the incovenient.Her e'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]

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

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]

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.

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