If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Am I forcing the 0's correctly?
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 |
#2
|
|||
|
|||
Am I forcing the 0's correctly?
On 6 Feb 2007 09:47:38 -0800, "Dos Equis"
wrote: 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] |
#3
|
|||
|
|||
Am I forcing the 0's correctly?
On Feb 6, 2:35 pm, John W. Vinson
wrote: On 6 Feb 2007 09:47:38 -0800, "Dos Equis" wrote: 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 a 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 |
#4
|
|||
|
|||
Am I forcing the 0's correctly?
On 6 Feb 2007 16:29:48 -0800, "Dos Equis"
wrote: 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] |
#5
|
|||
|
|||
Am I forcing the 0's correctly?
On Feb 6, 10:42 pm, John W. Vinson
wrote: On 6 Feb 2007 16:29:48 -0800, "DosEquis" wrote: 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] 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 |
#6
|
|||
|
|||
Am I forcing the 0's correctly?
On Feb 6, 10:42 pm, John W. Vinson
wrote: On 6 Feb 2007 16:29:48 -0800, "DosEquis" wrote: 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] 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 |
Thread Tools | |
Display Modes | |
|
|