Total records/groups

  • Thread starter Thread starter Jos Huijnen
  • Start date Start date
J

Jos Huijnen

I want to know how many "workplaces" there are and have that amount put in
the header of the report.

The report is build from two tables. The first table has a column which has
the name/code of the workplace (like WS4483). The second table is a
hardware list. Each device which is used on a specific workplace also has
that workplace name/code. In this second table that column has duplicate
entries (because a computer, monitor, printer all can have the same
workplace, so WS4483 can be used more than once). Each device has a
company-label with a code which is the HWCode.


The report looks like this....

Workplacename Username HWCode HWDescription
---------------------------------------------------
WS4483 John Doe
L-899 Compaq Deskpro EP
L-632 Eizo FlexScan F520
L-463 HP DesignJet 980
WS4497 Peter Pan
L-492 Compaq Deskpro EXD
L-211 Eizo FlexScan F520


If I use =count(*) all records are counted, which results in a total amount
of 5. But if I use =count([Workplacenaam]) this will also result in a total
of 5.

Workplacename and Username is from one table. HWCode and HWDescription
comes from a second table, which also has the field Workplacename, because
that is the field which links/connects those two tables.

As in the above example I want to know how many workplaces there are.
Correct would be 2 (not 5). So the record count should be not simple all
records, but those records (based on the field Workplacename) with a unique
entry.

As I see it now, There are 3 records with "WS4483" and 2 records with
"WS4497". What I need/want is a total count (in the header) of the
workplaces. This means that "WS4483" should only be counted 1 time (and the
same for "WS4497"). That would result in a total of 2, which is the correct
amount of workplaces.

Any help appreciated how to accomplish this.

I have tried to use a ab|-field in the groupheader section to count the
"workplaces". And it started nicely at 1, then 2, 3, 4, etc. etc. The last
one also was correct. Thought to use that field (well the last one) as
basis and in the reportheader make a field which "reads" the content of the
previous mentioned. That would result in the correct amount of workplaces.
But that didn't work.
 
You can do this easily in the report footer by adding a text box to the
workplace header section:
Name: txtCountWorkPlace
Control Source: =1
Running Sum: Over All
Add a text box to the Report Footer Section:
Control Source: =txtCountWorkPlace

To get this into the Report Header Section, you may need to create a
separate totals query that counts the unique workplaces. Add this query to
your report's record source. You can then add the column to your report.


--
Duane Hookom
MS Access MVP


Jos Huijnen said:
I want to know how many "workplaces" there are and have that amount put in
the header of the report.

The report is build from two tables. The first table has a column which
has
the name/code of the workplace (like WS4483). The second table is a
hardware list. Each device which is used on a specific workplace also has
that workplace name/code. In this second table that column has duplicate
entries (because a computer, monitor, printer all can have the same
workplace, so WS4483 can be used more than once). Each device has a
company-label with a code which is the HWCode.


The report looks like this....

Workplacename Username HWCode HWDescription
---------------------------------------------------
WS4483 John Doe
L-899 Compaq Deskpro EP
L-632 Eizo FlexScan F520
L-463 HP DesignJet 980
WS4497 Peter Pan
L-492 Compaq Deskpro EXD
L-211 Eizo FlexScan F520


If I use =count(*) all records are counted, which results in a total
amount
of 5. But if I use =count([Workplacenaam]) this will also result in a
total
of 5.

Workplacename and Username is from one table. HWCode and HWDescription
comes from a second table, which also has the field Workplacename, because
that is the field which links/connects those two tables.

As in the above example I want to know how many workplaces there are.
Correct would be 2 (not 5). So the record count should be not simple all
records, but those records (based on the field Workplacename) with a
unique
entry.

As I see it now, There are 3 records with "WS4483" and 2 records with
"WS4497". What I need/want is a total count (in the header) of the
workplaces. This means that "WS4483" should only be counted 1 time (and
the
same for "WS4497"). That would result in a total of 2, which is the
correct
amount of workplaces.

Any help appreciated how to accomplish this.

I have tried to use a ab|-field in the groupheader section to count the
"workplaces". And it started nicely at 1, then 2, 3, 4, etc. etc. The last
one also was correct. Thought to use that field (well the last one) as
basis and in the reportheader make a field which "reads" the content of
the
previous mentioned. That would result in the correct amount of workplaces.
But that didn't work.
Click to expand...
 
Thanks...

The easiest way was indeed using the footer section, but I was asked to
list the total amount of workplaces in the header.

Thought there was an easy way to do this just in the report. I have build
now a query to count those records and add that to the report. This works
great now. And it even gives more possibilities to use in the fututere
which can be easely implemented.
 
Back
Top