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"
table.
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.
ex:
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
tbl:
AprilTrip LeadName = 12 "Smith" Other1= 13 Other2=14 "Brown"
AprilTrip LeadName = 13 "Jones" Other = 12 Other2=31 "none"
rpt:
Apr
Lead/Other
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!
|
