Counting records in a report

  • Thread starter Thread starter APH
  • Start date Start date
A

APH

Hi,

I produce a report, based on table which gives me the counts of records
sorted by Global area with individual country counts in the detail area -
this all works Ok

What I want to do is to add a further unbound field again each country which
will count the number of instances where [source] field = "Essential Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
 
Thanks Duane - that works but I have justr realised I need to add a second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Duane Hookom said:
Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Hi,

I produce a report, based on table which gives me the counts of records
sorted by Global area with individual country counts in the detail area -
this all works Ok

What I want to do is to add a further unbound field again each country which
will count the number of instances where [source] field = "Essential Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
 
Try:
=Sum(Abs([Record Source]="Essential Free" And [Multi Copies]=0))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Thanks Duane - that works but I have justr realised I need to add a second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Duane Hookom said:
Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Hi,

I produce a report, based on table which gives me the counts of records
sorted by Global area with individual country counts in the detail area -
this all works Ok

What I want to do is to add a further unbound field again each country which
will count the number of instances where [source] field = "Essential Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Duane

No I tried that and it didn't work - that was in fact my first try.

The field [Multi Copies} is a Yes/No field. When i run the code I am
selecting all records including two where [Multi Copies] = -1
A


Duane Hookom said:
Try:
=Sum(Abs([Record Source]="Essential Free" And [Multi Copies]=0))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Thanks Duane - that works but I have justr realised I need to add a second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Duane Hookom said:
Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Hi,

I produce a report, based on table which gives me the counts of records
sorted by Global area with individual country counts in the detail area -
this all works Ok

What I want to do is to add a further unbound field again each country
which
will count the number of instances where [source] field = "Essential Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
I am willing to bet that if you open your report's record source in design
view and set criteria for [Record Source] to "Essential Free" and [Multi
Copies] to 0, the number of records will exactly match the result of your
expression.

Is this text box in a group header or footer or the report header or footer?

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Duane

No I tried that and it didn't work - that was in fact my first try.

The field [Multi Copies} is a Yes/No field. When i run the code I am
selecting all records including two where [Multi Copies] = -1
A


Duane Hookom said:
Try:
=Sum(Abs([Record Source]="Essential Free" And [Multi Copies]=0))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Thanks Duane - that works but I have justr realised I need to add a second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Hi,

I produce a report, based on table which gives me the counts of records
sorted by Global area with individual country counts in the detail
area -
this all works Ok

What I want to do is to add a further unbound field again each country
which
will count the number of instances where [source] field = "Essential
Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Yes you are correct - I am trying to figure out why I can get two extra
records , which I have identified and cannot see any reason why they are
being selected.

yes the text box is in both a group and footer.

Alex

Duane Hookom said:
I am willing to bet that if you open your report's record source in design
view and set criteria for [Record Source] to "Essential Free" and [Multi
Copies] to 0, the number of records will exactly match the result of your
expression.

Is this text box in a group header or footer or the report header or footer?

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Duane

No I tried that and it didn't work - that was in fact my first try.

The field [Multi Copies} is a Yes/No field. When i run the code I am
selecting all records including two where [Multi Copies] = -1
A


Duane Hookom said:
Try:
=Sum(Abs([Record Source]="Essential Free" And [Multi Copies]=0))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks Duane - that works but I have justr realised I need to add a
second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Hi,

I produce a report, based on table which gives me the counts of
records
sorted by Global area with individual country counts in the detail
area -
this all works Ok

What I want to do is to add a further unbound field again each country
which
will count the number of instances where [source] field = "Essential
Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
You have the tables so you need to filter down the report until you isolate
the issue. Check for duplicate records on one side of a join that you
shouldn't have duplicates.

--
Duane Hookom
MS Access MVP


APH said:
Yes you are correct - I am trying to figure out why I can get two extra
records , which I have identified and cannot see any reason why they are
being selected.

yes the text box is in both a group and footer.

Alex

Duane Hookom said:
I am willing to bet that if you open your report's record source in design
view and set criteria for [Record Source] to "Essential Free" and [Multi
Copies] to 0, the number of records will exactly match the result of your
expression.

Is this text box in a group header or footer or the report header or footer?

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


APH said:
Duane

No I tried that and it didn't work - that was in fact my first try.

The field [Multi Copies} is a Yes/No field. When i run the code I am
selecting all records including two where [Multi Copies] = -1
A


Try:
=Sum(Abs([Record Source]="Essential Free" And [Multi Copies]=0))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thanks Duane - that works but I have justr realised I need to add a
second
parameter.

Is : ==Sum(Abs([Record Source]="Essential Free" )And ([Multi Copies]=0))
valid code (think not as it doesn't work:)

A

Try a control source of:
=Sum(Abs([Source] = "Essential Free"))

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Hi,

I produce a report, based on table which gives me the counts of
records
sorted by Global area with individual country counts in the detail
area -
this all works Ok

What I want to do is to add a further unbound field again each
country
which
will count the number of instances where [source] field = "Essential
Free"

i can seem to get the syntax right

can anyoner help please

Thanks

A
Click to expand...
Click to expand...
Click to expand...
 
Back
Top