Am I forcing the 0's correctly?

[Dos Equis]

Hi all,

Using this code:

SELECT tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast] AS
Name,Cint(NZ(Count(tbl_Complaint.Complaint),0)) AS [Num of chargable
complaints]
FROM (tbl_Carrier INNER JOIN (tbl_Area INNER JOIN tbl_SubscribersData
ON tbl_Area.AreaID = tbl_SubscribersData.AreaID) ON tbl_Carrier.CID =
tbl_Area.CID) INNER JOIN tbl_Complaint ON tbl_SubscribersData.SubID =
tbl_Complaint.SubID
WHERE (((tbl_Complaint.CompDate)>=Date()-7))
GROUP BY tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast],
tbl_Complaint.Repeat
HAVING (((tbl_Complaint.Repeat)=True))
ORDER BY [CNameFirst] & " " & [CNameLast];

I end up with a total of 1 record:

3, Robert M, 1

I should have 5 records:

1, Gina R, 0
2, Terry T, 0
3, Robert M, 1
4, Richard G, 0
5, Bruce G, 0

I was thinking that forcing 0's would return the array above rather
than the single record I can get without the Nz.
Please tell me what I'm doing wrong.

Byron

 

[John W. Vinson]

Hi all,

Using this code:

SELECT tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast] AS
Name,Cint(NZ(Count(tbl_Complaint.Complaint),0)) AS [Num of chargable
complaints]
FROM (tbl_Carrier INNER JOIN (tbl_Area INNER JOIN tbl_SubscribersData
ON tbl_Area.AreaID = tbl_SubscribersData.AreaID) ON tbl_Carrier.CID =
tbl_Area.CID) INNER JOIN tbl_Complaint ON tbl_SubscribersData.SubID =
tbl_Complaint.SubID
WHERE (((tbl_Complaint.CompDate)>=Date()-7))
GROUP BY tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast],
tbl_Complaint.Repeat
HAVING (((tbl_Complaint.Repeat)=True))
ORDER BY [CNameFirst] & " " & [CNameLast];

I end up with a total of 1 record:

3, Robert M, 1

I should have 5 records:

1, Gina R, 0
2, Terry T, 0
3, Robert M, 1
4, Richard G, 0
5, Bruce G, 0

I was thinking that forcing 0's would return the array above rather
than the single record I can get without the Nz.
Please tell me what I'm doing wrong.

Byron

Change the INNER join - which will return only those records from
tbl_SubscribersData which have a match in tbl_Complaint - to a LEFT
join. You'll also need to do something about the criteria on
tbl_Complaint: if there is nothing in the Complaint table for a
carrier, then the criterion tbl_Complaint.Repeat = True or
tbl_Complaint.CompDate > Date()-7 will fail, since there is nothing in
the field. You may want to create a query qryRecentComplaints with
those criteria, and Left Join this query to your other tables (rather
than joining the Complaint table itself).

John W. Vinson [MVP]

 

[Dos Equis]

Hi all,

Using this code:

SELECT tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast] AS
Name,Cint(NZ(Count(tbl_Complaint.Complaint),0)) AS [Num of chargable
complaints]
FROM (tbl_Carrier INNER JOIN (tbl_Area INNER JOIN tbl_SubscribersData
ON tbl_Area.AreaID = tbl_SubscribersData.AreaID) ON tbl_Carrier.CID =
tbl_Area.CID) INNER JOIN tbl_Complaint ON tbl_SubscribersData.SubID =
tbl_Complaint.SubID
WHERE (((tbl_Complaint.CompDate)>=Date()-7))
GROUP BY tbl_Carrier.CID, [CNameFirst] & " " & [CNameLast],
tbl_Complaint.Repeat
HAVING (((tbl_Complaint.Repeat)=True))
ORDER BY [CNameFirst] & " " & [CNameLast];

I end up with a total of 1 record:

3, Robert M, 1

I should have 5 records:

1, Gina R, 0
2, Terry T, 0
3, Robert M, 1
4, Richard G, 0
5, Bruce G, 0

I was thinking that forcing 0's would return the array above rather
than the single record I can get without the Nz.
Please tell me what I'm doing wrong.

Byron

Change the INNER join - which will return only those records from
tbl_SubscribersData which have a match in tbl_Complaint - to a LEFT
join. You'll also need to do something about the criteria on
tbl_Complaint: if there is nothing in the Complaint table for a
carrier, then the criterion tbl_Complaint.Repeat = True or
tbl_Complaint.CompDate > Date()-7 will fail, since there is nothing in
the field. You may want to create a query qryRecentComplaints with
those criteria, and Left Join this query to your other tables (rather
than joining the Complaint table itself).

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John,

I looked at your suggestion and created a new query to do this. So
far this is where I am. It seems to work with the left join for
tbl_Carrier and a Right Join for Tbl_Area. This removes any carriers
not currently assigned an area. New problem: When I count the number
of complaints per carrier I get abnormal results. Here is the code:

SELECT tbl_Carrier.CID, qry_AllComplaints.Carrier,
Count(qry_AllComplaints.ComplaintID) AS Complaints
FROM (tbl_Carrier LEFT JOIN qry_AllComplaints ON tbl_Carrier.CID =
qry_AllComplaints.CID) RIGHT JOIN tbl_Area ON tbl_Carrier.CID =
tbl_Area.CID
GROUP BY tbl_Carrier.CID, qry_AllComplaints.Carrier
HAVING (((tbl_Carrier.CID)<>4 And (tbl_Carrier.CID)<>8));

and the results are:
Carrier ID, Carrier, Complaints
2, Davis, 22
3, Gina, 312
12, Angi, 6
17, Walter, 14
19, Tom, 1
21, Keegan, 4
23, Richard, 24
24, Jimmie, 4
25, Robert, 7

I only have 77 complaints on the table so I assume it is adding
complaint ID numbers instead of counting them.
I excluded carriers 4 and 8 as they are "Mail" and "None" so I can
mail to subscribers or have an area being prepared without assigning a
carrier until I am ready.

Any help is appreciated.

Byron

 

[John W. Vinson]

I only have 77 complaints on the table so I assume it is adding
complaint ID numbers instead of counting them.

No; the problem is that when you're joining multiple tables, you're
getting EVERY POSSIBLE COMBINATION of records in all the tables.

SELECT tbl_Carrier.CID, qry_AllComplaints.Carrier,
Count(qry_AllComplaints.ComplaintID) AS Complaints
FROM (tbl_Carrier LEFT JOIN qry_AllComplaints ON tbl_Carrier.CID =
qry_AllComplaints.CID) RIGHT JOIN tbl_Area ON tbl_Carrier.CID =
tbl_Area.CID
GROUP BY tbl_Carrier.CID, qry_AllComplaints.Carrier
HAVING (((tbl_Carrier.CID)<>4 And (tbl_Carrier.CID)<>8));

I have no idea how Areas, Carriers and Complaints are related; but if
one carrier can service three areas, and that carrier has five
complaints, then this query will return fifteen records - every
combination of the three tables.

Could you take a step back and describe how the tables are related,
and just what you're trying to determine? Since you're not including
anything about the area in your selection, do you need tbl_Area in the
query at all?

Note that you should certainly change the HAVING clause to a WHERE
clause: WHERE filters the records first, before any counting or
totalling; HAVING does all the calculations and then discards the
records which don't fit. Not only is this much less efficient but it
can give different results!

John W. Vinson [MVP]

 

[Dos Equis]

No; the problem is that when you're joining multiple tables, you're
getting EVERY POSSIBLE COMBINATION of records in all the tables.

SELECT tbl_Carrier.CID, qry_AllComplaints.Carrier,
Count(qry_AllComplaints.ComplaintID) AS Complaints
FROM (tbl_Carrier LEFT JOIN qry_AllComplaints ON tbl_Carrier.CID =
qry_AllComplaints.CID) RIGHT JOIN tbl_Area ON tbl_Carrier.CID =
tbl_Area.CID
GROUP BY tbl_Carrier.CID, qry_AllComplaints.Carrier
HAVING (((tbl_Carrier.CID)<>4 And (tbl_Carrier.CID)<>8));

I have no idea how Areas, Carriers and Complaints are related; but if
one carrier can service three areas, and that carrier has five
complaints, then this query will return fifteen records - every
combination of the three tables.

Could you take a step back and describe how the tables are related,
and just what you're trying to determine? Since you're not including
anything about the area in your selection, do you need tbl_Area in the
query at all?

Note that you should certainly change the HAVING clause to a WHERE
clause: WHERE filters the records first, before any counting or
totalling; HAVING does all the calculations and then discards the
records which don't fit. Not only is this much less efficient but it
can give different results!

John W. Vinson [MVP]

John,

tbl_Area contains all data about an area: The name, area ID, carrier
and number of homes assigned along with hyperlinks to maps and special
notes needed.
tbl_Carrier contains all data about my carriers: Name, Carrier ID,
date hired, phone numbers and pay rates.
tbl_Complaints contains data about every complaint recieved:
Complaint ID, Subscriber ID, Date of Complaint, Description and
whether or not is a repeat(pay penalties).

As for the Area table, I don't have a direct link from carrier to
complaint so the area table being connected to both is used to gap the
chasm.
I am trying to determine the number of complaints per carrier which
are repeats so that I may apply a $5 penalty per incedent. I can
count the number of complaints and I can find the number of chargable
complaints per week, I just can't attach it to a pay request so that I
have all carriers represented even if they don't have complaints.
So, what I am attempting to do is generate a report which states that
Carrier 1 is paid $## per paper, delivering ## papers for a total
of ???. In addition, they are paid $## per subscriber delivering ##
Subscribers totaling ???. Carrier 1 had ## repeat complaints at a rate
of $5.00 per incedent reducing their pay by ??? and creating a pay
check for the sum of $###.##.

This is repeated for each carrier and will be adjustable for special
sections or inserts if applicable. Also, their pay may be affected if
the quality of delivery is substandard. I've got all this in a
spreadsheet, but it takes 4 spreadsheets to accomplish my goal and I'd
rather just put it in a database that can be run on the network and
updated by the receptionist and the controller.

If you'd like, I could e-mail you a copy for review, it's definatly
amatuer work, but hopefully I'm getting better.

Thanks for your help,

Byron

 

[Dos Equis]

No; the problem is that when you're joining multiple tables, you're
getting EVERY POSSIBLE COMBINATION of records in all the tables.

SELECT tbl_Carrier.CID, qry_AllComplaints.Carrier,
Count(qry_AllComplaints.ComplaintID) AS Complaints
FROM (tbl_Carrier LEFT JOIN qry_AllComplaints ON tbl_Carrier.CID =
qry_AllComplaints.CID) RIGHT JOIN tbl_Area ON tbl_Carrier.CID =
tbl_Area.CID
GROUP BY tbl_Carrier.CID, qry_AllComplaints.Carrier
HAVING (((tbl_Carrier.CID)<>4 And (tbl_Carrier.CID)<>8));

I have no idea how Areas, Carriers and Complaints are related; but if
one carrier can service three areas, and that carrier has five
complaints, then this query will return fifteen records - every
combination of the three tables.

Could you take a step back and describe how the tables are related,
and just what you're trying to determine? Since you're not including
anything about the area in your selection, do you need tbl_Area in the
query at all?

Note that you should certainly change the HAVING clause to a WHERE
clause: WHERE filters the records first, before any counting or
totalling; HAVING does all the calculations and then discards the
records which don't fit. Not only is this much less efficient but it
can give different results!

John W. Vinson [MVP]

Replied to this Wed. I think, but didn't show up. Ok, Carriers and
Complaints are the two tables containing the data I need to work with,
Areas is the table that they are connected through. I tried very hard
not to have redundant data so almost everything is conected through
something else.

I am trying to determine the number of complaints for each carrier
since the last delivery. If any of these complaints is a repeat, they
are charged for it and I need to reduce their pay by a set amount per
incedent.

So, in rpt_PayRequest, I should have:

Carrier PayRate NumOfHomes SubTotal
PayRate NumOfSubscribers SubTotal
PenaltyRate Complaints SubTotal

TotalPay

I've already managed the NumOfHomes and NumOfSubscribers so complaints
will finish the data I need to make the report.

Let me know if you need more info.

Byron