View Single Post
Old April 15th, 2005, 03:02 PM
CongoGrey CongoGrey is offline
Registered User
Join Date: Apr 2005
Location: Boston, MA, USA.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Count, sum, count a value, return records

I am trying to count the number of times the same name
occurs in five different fields.

I want to sum the counts of fields 2 - 5,[Other1, Other2, etc.]
and display them in a report side by side with the sum
of the occurences of each name in the first field [LeadName].

The Name fields actually contain a number ("Long Interger")
that is related to an ID number in another "look-up"

A problem I am having:

1) The LeadName qry/qryctab/rpt works fine - all names in the list
display with their associated count sums sorted by month.

    Oct Nov Dec TotalLead
Smith 1 2 3
Jones 1 4 5

However, when I try to add Other1, which also sorts and
displays correctly as a stand-alone, I find it has a different
number of names because not all names had values in the "other"
fields. When I join "Other1" query with the "LeadName" query, only
names with matching name values are returned. ex: QryLeadName number of records is 33, QryOther1 number of records is 5. When I try to make a multiple value field query, only 5 records (those appearing in both QryOther1 and QryLeadName) are returned.

How do I display all the names in the Name list - even thoses with
no records? There are no "null" or "zero-strings" in any fields.

ex: Other1 = 31 ("none" in look-up table).

Ideally, I would like to just have a single sum for fields 2 - 5

AprilTrip LeadName = 12 "Smith" Other1= 13 Other2=14 "Brown"
AprilTrip LeadName = 13 "Jones" Other = 12 Other2=31 "none"

    Smith 1/1 (Trip as lead = 1, Trip as Other = 1
    Jones 1/zero or blank (Trip as lead = 1, Trip as Other = 0

Right now "Jones" has no return because she has no associated record in the "Other" query. I'm getting so confused!