re arrange records in Reports for MS Access 2000

[var]

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[KARL DEWEY]

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

 

[var]

Hi Dewey,

first of all thanks a lot for your help

with little changes the code worked superbly,

what if i want to print 'Percentage of ac' in comparison of Total ac

thanks again

var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[KARL DEWEY]

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.Status) AS Total,
Sum(IIf([Status]="AC",1,0))/Count([Status])*100 AS [Percent AC]
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

Hi Dewey,

first of all thanks a lot for your help

with little changes the code worked superbly,

what if i want to print 'Percentage of ac' in comparison of Total ac

thanks again

var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[var]

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.Status) AS Total,
Sum(IIf([Status]="AC",1,0))/Count([Status])*100 AS [Percent AC]
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

Hi Dewey,

first of all thanks a lot for your help

with little changes the code worked superbly,

what if i want to print 'Percentage of ac' in comparison of Total ac

thanks again

var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

:

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[KARL DEWEY]

Post some of your data so I can check my query with it.

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.Status) AS Total,
Sum(IIf([Status]="AC",1,0))/Count([Status])*100 AS [Percent AC]
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

Hi Dewey,

first of all thanks a lot for your help

with little changes the code worked superbly,

what if i want to print 'Percentage of ac' in comparison of Total ac

thanks again

var


KARL DEWEY wrote:
Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

:

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[KARL DEWEY]

I do not understand the part about "dates" as you never mentioned dates before.

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var

Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.Status) AS Total,
Sum(IIf([Status]="AC",1,0))/Count([Status])*100 AS [Percent AC]
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

Hi Dewey,

first of all thanks a lot for your help

with little changes the code worked superbly,

what if i want to print 'Percentage of ac' in comparison of Total ac

thanks again

var


KARL DEWEY wrote:
Try this --
TRANSFORM Count(VAR_Table.Status) AS CountOfStatus
SELECT VAR_Table.Name, Count(VAR_Table.ActionDate) AS Total
FROM VAR_Table
GROUP BY VAR_Table.Name
PIVOT "Number of " & [Status];

:

......here what i need to work is my table has fields called

name Status date
v ac -.
vj rj -.
k ac -.
k rj -.


i am able write a query within a time period ('from' date to 'to' date)
and get the report, but i need to re aarange the output with titled

name number of ac number of rj Total
v 1 0 1
(1+0)
k 1 1 2

how to count those details from 'Status' Itext format) field of the
table and display in report in a statistical form

do i need to write some expressions in Report Form?....any suggestions
pls help me

var

 

[var]

HI,

here is the complete picture..

Table:

name Status date
v ac 5/28/06
vj rj 6/20/06
k ac 4/15/06
k rj 3/20/06


Form:

From______

To_______

OK(command button) Cancel(command button)

(when the user enter the dates, it should pull up the records between
those dates and should show the record as formated below)


Final Report:

name number of ac number of rj Total % of ac
% of rj
v 1 0 1
100 0
(1+0)
k 1 1 2
50 50


Your suggested script is running but it is not showing the actual
records and why my form pop ups 'Enter Parameter Value'......

i really apprecitae for spending your good time.....for me

var

I do not understand the part about "dates" as you never mentioned dates before.

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var

 

[KARL DEWEY]

in my query i did declared the paramets [From] and [To] as DateTime
Did you also declare them in the Crosstab query? Crosstab queries require
them to be declaried even if not in the crosstab but in a feeder query.

What is the data (1+0) you show in the second line of results?

HI,

here is the complete picture..

Table:

name Status date
v ac 5/28/06
vj rj 6/20/06
k ac 4/15/06
k rj 3/20/06


Form:

From______

To_______

OK(command button) Cancel(command button)

(when the user enter the dates, it should pull up the records between
those dates and should show the record as formated below)


Final Report:

name number of ac number of rj Total % of ac
% of rj
v 1 0 1
100 0
(1+0)
k 1 1 2
50 50


Your suggested script is running but it is not showing the actual
records and why my form pop ups 'Enter Parameter Value'......

i really apprecitae for spending your good time.....for me

var

I do not understand the part about "dates" as you never mentioned dates before.

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var

 

[var]

Hi


"Did you also declare them in the Crosstab query? Crosstab queries
require
them to be declaried even if not in the crosstab but in a feeder
query"

I am not sure what this mean.......can you explain me a little
more....

but i tried my best to delcare in 'format' as shortdate in Form, in my
SQL query as PARAMETERS [From] DateTime, [To] DateTime;....and using
where condition .... WHERE ([From]<=tblInspection.Date) And
([To]>=tblInspection.Date) to pul up the records From, To

(1+0).........just an example, i was showing that Total

Here is the final report should look like:


name number of ac number of rj Total % of ac % of rj
v 1 0 1 100
0
k 1 1 2 50
50



how do i make my final output look like this..........

Var

in my query i did declared the paramets [From] and [To] as DateTime

Did you also declare them in the Crosstab query? Crosstab queries require
them to be declaried even if not in the crosstab but in a feeder query.

What is the data (1+0) you show in the second line of results?

HI,

here is the complete picture..

Table:

name Status date
v ac 5/28/06
vj rj 6/20/06
k ac 4/15/06
k rj 3/20/06


Form:

From______

To_______

OK(command button) Cancel(command button)

(when the user enter the dates, it should pull up the records between
those dates and should show the record as formated below)


Final Report:

name number of ac number of rj Total % of ac
% of rj
v 1 0 1
100 0
(1+0)
k 1 1 2
50 50


Your suggested script is running but it is not showing the actual
records and why my form pop ups 'Enter Parameter Value'......

i really apprecitae for spending your good time.....for me

var

I do not understand the part about "dates" as you never mentioned dates before.

:

Hi,

i tried your's script but it is not showing the actual percentage,
instead showing 100 for all fields in the colums of 'Percent AC'....i
am not sure where iam doing wrong?

and one more thing is when i entered the dates in the Form to
run....again it showing the pop up 'Enter Parameter Value' ....asking
to re enter the input dates.......why it is asking again?

in my query i did declared the paramets [From] and [To] as DateTime

i am really new to this work....give me suggestions

Var