Reprting Question - Null Fields

[Guest]

I have a form that is used to enter up to 10 names of people attending a
meeting. The names are picked from a lookup table.

I'm trying to create a report that shows how many people came to the
meeting. Not the names who came, just how many people came.

I tried using the ISNull command, but seem to be getting sketchy results. I
can't figure out why it's giving incocnsistent results, but am thinking it a
name was entered, and then erased, maybe the field isn't null any longer.

Is there a better way? How do I essitially count which fields have a name
in them, and then report that number?

Thanks for your help!!!

 

[John Vinson]

How do I essitially count which fields have a name
in them, and then report that number?

With a simple Totals query, if you have one name per record as in a
properly normalized table structure.

If you have a non-normalized structure with one name per field, it's a
whale of a lot more difficult. You'll need a nasty expression like

Iif(IsNull([Name1]), 0, 1) + Iif(SiNull([Name2]), 0, 1) +
Iif(IsNull([Name3]), 0, 1) + ...


John W. Vinson[MVP]

 

[Allen Browne]

The best way to do this will be to use a related table to record who
attended the meeting. The fields of this table will be:
MeetingID relates to Meeting.ID in the Meeting table.
PersonID relates to the PersonId of your Person table.

The form where you enter the meetings will have a subform.
The subform is bound to the table above.
It will have a combo box for selecting the people.
Enter as many lines as you need to record the people who attended the
meeting.

You can now create a query into the meeting table, to use for your report.
In the Field row of this query, enter a subquery to count the number of
people who attended. Something like this:
Attendees: (SELECT Count([PersonID]) FROM MeetingPerson
Where MeetingPerson.MeetingId = Meeting.MeetingId)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Thanks Allen, After reading more about this last night after I posted this, I
started to wonder if what you described is the best way.

On the form to enter the names, do I just have a bunch of combo boxes? Or
is there a way to populate one and then have another pop up, so I'm only
seeing however many I need + 1?

Thanks Again

The best way to do this will be to use a related table to record who
attended the meeting. The fields of this table will be:
MeetingID relates to Meeting.ID in the Meeting table.
PersonID relates to the PersonId of your Person table.

The form where you enter the meetings will have a subform.
The subform is bound to the table above.
It will have a combo box for selecting the people.
Enter as many lines as you need to record the people who attended the
meeting.

You can now create a query into the meeting table, to use for your report.
In the Field row of this query, enter a subquery to count the number of
people who attended. Something like this:
Attendees: (SELECT Count([PersonID]) FROM MeetingPerson
Where MeetingPerson.MeetingId = Meeting.MeetingId)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that is used to enter up to 10 names of people attending a
meeting. The names are picked from a lookup table.

I'm trying to create a report that shows how many people came to the
meeting. Not the names who came, just how many people came.

I tried using the ISNull command, but seem to be getting sketchy results.
I
can't figure out why it's giving incocnsistent results, but am thinking it
a
name was entered, and then erased, maybe the field isn't null any longer.

Is there a better way? How do I essitially count which fields have a name
in them, and then report that number?

Thanks for your help!!!

 

[Allen Browne]

Open the subform in design view, and set its Default View property to
Continuous (or datasheet).

Then when you fill in one row, another will appear beneath it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, After reading more about this last night after I posted
this, I
started to wonder if what you described is the best way.

On the form to enter the names, do I just have a bunch of combo boxes? Or
is there a way to populate one and then have another pop up, so I'm only
seeing however many I need + 1?

Thanks Again

The best way to do this will be to use a related table to record who
attended the meeting. The fields of this table will be:
MeetingID relates to Meeting.ID in the Meeting table.
PersonID relates to the PersonId of your Person table.

The form where you enter the meetings will have a subform.
The subform is bound to the table above.
It will have a combo box for selecting the people.
Enter as many lines as you need to record the people who attended the
meeting.

You can now create a query into the meeting table, to use for your
report.
In the Field row of this query, enter a subquery to count the number of
people who attended. Something like this:
Attendees: (SELECT Count([PersonID]) FROM MeetingPerson
Where MeetingPerson.MeetingId = Meeting.MeetingId)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I have a form that is used to enter up to 10 names of people attending a
meeting. The names are picked from a lookup table.

I'm trying to create a report that shows how many people came to the
meeting. Not the names who came, just how many people came.

I tried using the ISNull command, but seem to be getting sketchy
results.
I
can't figure out why it's giving incocnsistent results, but am thinking
it
a
name was entered, and then erased, maybe the field isn't null any
longer.

Is there a better way? How do I essitially count which fields have a
name
in them, and then report that number?

Thanks for your help!!!