Help with Crosstab query and report

G

Guest

Hi, All
I need help with report (MS Access 2003)
I have table with a 6 columns:
Date| Name| Id | Unit | Time | Code
data looks like this:
10/19/06 |myname |354| 04 | 8:30 | SignOn
10/19/06 |myname |354| 04 | 12:30 | Breakstart
10/19/06 |myname |354| 04 | 13:00 | Breakend
10/19/06 |myname |354| 04 | 14:30 | Statusstart
10/19/06 |myname |354| 04 | 14:45 | Statusend
10/19/06 |myname |354| 04 | 15:30 | Breakstart
10/19/06 |myname |354| 04 | 15:45 | Breakend
10/19/06 |myname |354| 04 | 18:00 | Breakstart
10/19/06 |myname |354| 04 | 18:15 | Breakend
10/19/06 |myname |354| 04 | 19:00 | SignOff
10/19/06 |myname |354| 04 | 19:01 | Download

I have to create report which looks like crosstab where for each name and Id
i can show fields like SignOn (time), Break1start(Time) Break1end(time)
Break2start(Time) Break2end(Time) Staus1start(Time) Status1end(Time)
Status2start(Time)
Status2end(Time) SignOff(Time) Download(Time) and grouped by Date.

Please, advace
What should i do with this table to produce such report?
elena
 
G

Guest

TRANSFORM First(tblElena.Time) AS FirstOfTime
SELECT tblElena.Name, tblElena.Id, tblElena.Date
FROM tblElena
GROUP BY tblElena.Name, tblElena.Id, tblElena.Date
PIVOT tblElena.Code In
("SignOn","Break1start","Break1end","Break2start","Break2end","Staus1start","Status1end","Status2start","Status2end","SignOff","Download");

However is "Staus1start(Time)" a typo? If so, you need to fix the sql above.
 
G

Guest

I used three queries. The first produces a numbering (rank) for the multiple
activities such as break start. The second is a 'make-table' combining the
code and numbering into 'activity.' The third is a crosstab query for
display.
NOTE - The codes should be changed to 'Break start' (with a space) so
it will display before 'Breakend.' You also could add an "IN" function in
the SQL of the crosstab. Additionaly you might change the combining order
like numbering and code so that all first events are displayed before the
second ones.

elena_1 ---
SELECT T.Unit, T.Id, T.Name, T.Date, T.Time, T.Code, (SELECT COUNT(*)
FROM [elena] T1
WHERE T1.Code = T.Code
AND T1.Time <= T.Time) AS Rank
FROM elena AS T
ORDER BY T.Unit, T.Id, T.Name, T.Date, T.Code, T.Time;

SELECT elena_1.Unit, elena_1.Id, elena_1.Name, elena_1.Date, elena_1.Time,
Code: 
 & "  " & [Rank] AS Activity INTO elena_3
FROM elena_1;

TRANSFORM First(elena_3.Time) AS FirstOfTime
SELECT elena_3.Unit, elena_3.Id, elena_3.Name
FROM elena_3
GROUP BY elena_3.Unit, elena_3.Id, elena_3.Name
PIVOT elena_3.Activity;
 
G

Guest

Thank you, Karl
I am testing your queries right now,
first query runs with no problem, when i run second query, i received
message from MS Access "Are you sure you want to run make-table query, which
will modify data in your table" I clicked YES and it display message like
"the existing query elena_3 will be deleted before you run query"

I am stuck, what should i do?
Thank you in advance

KARL DEWEY said:
I used three queries. The first produces a numbering (rank) for the multiple
activities such as break start. The second is a 'make-table' combining the
code and numbering into 'activity.' The third is a crosstab query for
display.
NOTE - The codes should be changed to 'Break start' (with a space) so
it will display before 'Breakend.' You also could add an "IN" function in
the SQL of the crosstab. Additionaly you might change the combining order
like numbering and code so that all first events are displayed before the
second ones.

elena_1 ---
SELECT T.Unit, T.Id, T.Name, T.Date, T.Time, T.Code, (SELECT COUNT(*)
FROM [elena] T1
WHERE T1.Code = T.Code
AND T1.Time <= T.Time) AS Rank
FROM elena AS T
ORDER BY T.Unit, T.Id, T.Name, T.Date, T.Code, T.Time;

SELECT elena_1.Unit, elena_1.Id, elena_1.Name, elena_1.Date, elena_1.Time,
Code: 
 & "  " & [Rank] AS Activity INTO elena_3
FROM elena_1;

TRANSFORM First(elena_3.Time) AS FirstOfTime
SELECT elena_3.Unit, elena_3.Id, elena_3.Name
FROM elena_3
GROUP BY elena_3.Unit, elena_3.Id, elena_3.Name
PIVOT elena_3.Activity;


[QUOTE="elena"]
Hi, All
I need help with report (MS Access 2003)
I have table with a 6 columns:
Date| Name| Id | Unit | Time | Code
data looks like this:
10/19/06 |myname |354| 04 | 8:30 | SignOn
10/19/06 |myname |354| 04 | 12:30 | Breakstart
10/19/06 |myname |354| 04 | 13:00 | Breakend
10/19/06 |myname |354| 04 | 14:30 | Statusstart
10/19/06 |myname |354| 04 | 14:45 | Statusend
10/19/06 |myname |354| 04 | 15:30 | Breakstart
10/19/06 |myname |354| 04 | 15:45 | Breakend
10/19/06 |myname |354| 04 | 18:00 | Breakstart
10/19/06 |myname |354| 04 | 18:15 | Breakend
10/19/06 |myname |354| 04 | 19:00 | SignOff
10/19/06 |myname |354| 04 | 19:01 | Download

I have to create report which looks like crosstab where for each name and Id
i can show fields like SignOn (time), Break1start(Time) Break1end(time)
Break2start(Time) Break2end(Time) Staus1start(Time) Status1end(Time)
Status2start(Time)
Status2end(Time) SignOff(Time) Download(Time) and grouped by Date.

Please, advace
What should i do with this table to produce such report?
elena
[/QUOTE][/QUOTE]
Click to expand...
 
G

Guest

A make table query will alway over write an existing table. If you run it a
second time then it will over write the one created the first time.

elena said:
Thank you, Karl
I am testing your queries right now,
first query runs with no problem, when i run second query, i received
message from MS Access "Are you sure you want to run make-table query, which
will modify data in your table" I clicked YES and it display message like
"the existing query elena_3 will be deleted before you run query"

I am stuck, what should i do?
Thank you in advance

KARL DEWEY said:
I used three queries. The first produces a numbering (rank) for the multiple
activities such as break start. The second is a 'make-table' combining the
code and numbering into 'activity.' The third is a crosstab query for
display.
NOTE - The codes should be changed to 'Break start' (with a space) so
it will display before 'Breakend.' You also could add an "IN" function in
the SQL of the crosstab. Additionaly you might change the combining order
like numbering and code so that all first events are displayed before the
second ones.

elena_1 ---
SELECT T.Unit, T.Id, T.Name, T.Date, T.Time, T.Code, (SELECT COUNT(*)
FROM [elena] T1
WHERE T1.Code = T.Code
AND T1.Time <= T.Time) AS Rank
FROM elena AS T
ORDER BY T.Unit, T.Id, T.Name, T.Date, T.Code, T.Time;

SELECT elena_1.Unit, elena_1.Id, elena_1.Name, elena_1.Date, elena_1.Time,
Code: 
 & "  " & [Rank] AS Activity INTO elena_3
FROM elena_1;

TRANSFORM First(elena_3.Time) AS FirstOfTime
SELECT elena_3.Unit, elena_3.Id, elena_3.Name
FROM elena_3
GROUP BY elena_3.Unit, elena_3.Id, elena_3.Name
PIVOT elena_3.Activity;


[QUOTE="elena"]
Hi, All
I need help with report (MS Access 2003)
I have table with a 6 columns:
Date| Name| Id | Unit | Time | Code
data looks like this:
10/19/06 |myname |354| 04 | 8:30 | SignOn
10/19/06 |myname |354| 04 | 12:30 | Breakstart
10/19/06 |myname |354| 04 | 13:00 | Breakend
10/19/06 |myname |354| 04 | 14:30 | Statusstart
10/19/06 |myname |354| 04 | 14:45 | Statusend
10/19/06 |myname |354| 04 | 15:30 | Breakstart
10/19/06 |myname |354| 04 | 15:45 | Breakend
10/19/06 |myname |354| 04 | 18:00 | Breakstart
10/19/06 |myname |354| 04 | 18:15 | Breakend
10/19/06 |myname |354| 04 | 19:00 | SignOff
10/19/06 |myname |354| 04 | 19:01 | Download

I have to create report which looks like crosstab where for each name and Id
i can show fields like SignOn (time), Break1start(Time) Break1end(time)
Break2start(Time) Break2end(Time) Staus1start(Time) Status1end(Time)
Status2start(Time)
Status2end(Time) SignOff(Time) Download(Time) and grouped by Date.

Please, advace
What should i do with this table to produce such report?
elena
[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
G

Guest

Thank you,

KARL DEWEY said:
A make table query will alway over write an existing table. If you run it a
second time then it will over write the one created the first time.

elena said:
Thank you, Karl
I am testing your queries right now,
first query runs with no problem, when i run second query, i received
message from MS Access "Are you sure you want to run make-table query, which
will modify data in your table" I clicked YES and it display message like
"the existing query elena_3 will be deleted before you run query"

I am stuck, what should i do?
Thank you in advance

KARL DEWEY said:
I used three queries. The first produces a numbering (rank) for the multiple
activities such as break start. The second is a 'make-table' combining the
code and numbering into 'activity.' The third is a crosstab query for
display.
NOTE - The codes should be changed to 'Break start' (with a space) so
it will display before 'Breakend.' You also could add an "IN" function in
the SQL of the crosstab. Additionaly you might change the combining order
like numbering and code so that all first events are displayed before the
second ones.

elena_1 ---
SELECT T.Unit, T.Id, T.Name, T.Date, T.Time, T.Code, (SELECT COUNT(*)
FROM [elena] T1
WHERE T1.Code = T.Code
AND T1.Time <= T.Time) AS Rank
FROM elena AS T
ORDER BY T.Unit, T.Id, T.Name, T.Date, T.Code, T.Time;

SELECT elena_1.Unit, elena_1.Id, elena_1.Name, elena_1.Date, elena_1.Time,
Code: 
 & "  " & [Rank] AS Activity INTO elena_3
FROM elena_1;

TRANSFORM First(elena_3.Time) AS FirstOfTime
SELECT elena_3.Unit, elena_3.Id, elena_3.Name
FROM elena_3
GROUP BY elena_3.Unit, elena_3.Id, elena_3.Name
PIVOT elena_3.Activity;


:

Hi, All
I need help with report (MS Access 2003)
I have table with a 6 columns:
Date| Name| Id | Unit | Time | Code
data looks like this:
10/19/06 |myname |354| 04 | 8:30 | SignOn
10/19/06 |myname |354| 04 | 12:30 | Breakstart
10/19/06 |myname |354| 04 | 13:00 | Breakend
10/19/06 |myname |354| 04 | 14:30 | Statusstart
10/19/06 |myname |354| 04 | 14:45 | Statusend
10/19/06 |myname |354| 04 | 15:30 | Breakstart
10/19/06 |myname |354| 04 | 15:45 | Breakend
10/19/06 |myname |354| 04 | 18:00 | Breakstart
10/19/06 |myname |354| 04 | 18:15 | Breakend
10/19/06 |myname |354| 04 | 19:00 | SignOff
10/19/06 |myname |354| 04 | 19:01 | Download

I have to create report which looks like crosstab where for each name and Id
i can show fields like SignOn (time), Break1start(Time) Break1end(time)
Break2start(Time) Break2end(Time) Staus1start(Time) Status1end(Time)
Status2start(Time)
Status2end(Time) SignOff(Time) Download(Time) and grouped by Date.

Please, advace
What should i do with this table to produce such report?
elena
[/QUOTE][/QUOTE][/QUOTE]
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

Crosstab query, help 5
Numbers from query concactinate when added in Report 1
Changing constants in a query to user input values 2
Subtraction formula 7
Crosstab Query based on Ordinal Values 6
Time query 9
OT: Selective HP drivers (apologies for length) 7
Pls help for Simple Calculation 1

Top