How to set up a report to count the number of variables by date o.

[Guest]

I need to set up a report to count the number of variables by date order.
Can anyone give me examples on how to do this? I am using Access 97. The
inpurt fields are: Name; Date; LunchTye (This is the field I need to count .
The variables are H or S). If I do a query it puts a number in each field.
I need the report to show the variable by the name with a total at the end of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.

 

[AlCamp]

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

 

[Guest]

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields in the
query. I getting an error msg. "Syntax error (comma) in query expression .
I have checked to make sure that the field name is spelled correctly. I can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin

 

[Duane Hookom]

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields in
the
query. I getting an error msg. "Syntax error (comma) in query expression
.
I have checked to make sure that the field name is spelled correctly. I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin

 

[Guest]

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access 97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields in
the
query. I getting an error msg. "Syntax error (comma) in query expression
.
I have checked to make sure that the field name is spelled correctly. I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin

That was just a typo on my part. I did double check to make sure that I did not have an extra space in the query.

Thanks

Robin

 

[Duane Hookom]

Paste your entire SQL view in a reply.

--
Duane Hookom
MS Access MVP
--

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--

:

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access
97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need
to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at
the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields
in
the
query. I getting an error msg. "Syntax error (comma) in query
expression
.
I have checked to make sure that the field name is spelled correctly.
I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin

That was just a typo on my part. I did double check to make sure that I
did not have an extra space in the query.

Thanks

Robin

 

[AlCamp]

I agreed with Duane, the only possible problem with your code was what
appeared to be a space betweem IIF and the left parens "(".
Otherwise the HCount: IIf ([LunchType]="H",1,0) should be OK.
As Duane requested... we'll need to see the whole SQL string.


Al Camp

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--

:

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access
97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need
to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at
the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields
in
the
query. I getting an error msg. "Syntax error (comma) in query
expression
.
I have checked to make sure that the field name is spelled correctly.
I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin

That was just a typo on my part. I did double check to make sure that I
did not have an extra space in the query.

Thanks

Robin

 

[Guest]

Paste your entire SQL view in a reply.

--
Duane Hookom
MS Access MVP
--

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--



:

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access
97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need
to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at
the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields
in
the
query. I getting an error msg. "Syntax error (comma) in query
expression
.
I have checked to make sure that the field name is spelled correctly.
I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin


That was just a typo on my part. I did double check to make sure that I
did not have an extra space in the query.

Thanks

Robin

I finally found what the problem was. I had two tables that I was conbining with the same field name. Once I deleted one of the fields from the table. It worked fine.

I have one more problem on the layout of the report. I would like the
report to print like this:

Student Name 8/25 8/26 8/27 8/28 8/29
Johnny H H S
Kim H H S

Total H 2 2
Total S 1 1

I am having trouble printing the dates across the page with the name only
printing once. Could you please assist me with this report layout.

Thanks

Robin

 

[Guest]

I agreed with Duane, the only possible problem with your code was what
appeared to be a space betweem IIF and the left parens "(".
Otherwise the HCount: IIf ([LunchType]="H",1,0) should be OK.
As Duane requested... we'll need to see the whole SQL string.


Al Camp

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--



:

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access
97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need
to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at
the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields
in
the
query. I getting an error msg. "Syntax error (comma) in query
expression
.
I have checked to make sure that the field name is spelled correctly.
I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin


That was just a typo on my part. I did double check to make sure that I
did not have an extra space in the query.

Thanks

Robin

---Al & Duane,

I finally found what the problem was. I had two tables that I was conbining
with the same field name. Once I deleted one of the fields from the table.
It worked fine.

I have one more problem on the layout of the report. I would like the
report to print like this:

Student Name 8/25 8/26 8/27 8/28 8/29
Johnny H H S
Kim H H S

Total H 2 2
Total S 1 1

I am having trouble printing the dates across the page with the name only
printing once. Could you please assist me with this report layout.

Thanks

Robin

 

[Duane Hookom]

Below is a "canned" response for a crosstab report with month headings. You
should be able to modify it for days rather than months:
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

--
Duane Hookom
MS Access MVP

Paste your entire SQL view in a reply.

--
Duane Hookom
MS Access MVP
--

:

Not sure if this is just a typo but you have an extra space after IIf.
HCount: IIf ([LunchType]="H",1,0)


--
Duane Hookom
MS Access MVP
--



:

Robin,
I take it that you have your report grouped properly on your date
values... just the way you want it.
I'd create 2 new fields in the query like this...
HCount : IIF(LunchTye = "H", 1, 0)
and
SCount : IIF(LunchTye = "S", 1, 0)
Now, on your date group footer, just sum those fields.
= "H Count = " & Sum(HCount)
hth
Al Camp

I need to set up a report to count the number of variables by date
order.
Can anyone give me examples on how to do this? I am using Access
97.
The
inpurt fields are: Name; Date; LunchTye (This is the field I need
to
count .
The variables are H or S). If I do a query it puts a number in each
field.
I need the report to show the variable by the name with a total at
the
end
of
the report totalling how many H and S for that date. Simialr to a
spreadsheet in excel.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.782 / Virus Database: 528 - Release Date: 10/22/2004



Al,

Thanks for your help. I did as you requested and created the fields
in
the
query. I getting an error msg. "Syntax error (comma) in query
expression
.
I have checked to make sure that the field name is spelled correctly.
I
can
not see any problems. Any suggestions. This is what I have typed in:
HCount: IIf ([LunchType]="H",1,0)

Robin


That was just a typo on my part. I did double check to make sure that I
did not have an extra space in the query.

Thanks

Robin

I finally found what the problem was. I had two tables that I was

conbining with the same field name. Once I deleted one of the fields from
the table. It worked fine.