No me Fucniona el Count() Access

[SOFIA_ME_GUSTA]

Tengo esta consulta que funiona bien:

Código PHP:

  SELECT Claims.ClaimID, Claims.PatientID AS Patient, Claims.ProviderID AS Provider, Claims.InsurerID AS Insurer, Claims.DateOfService AS DOS, Claims.DateOfReceiptByMDabroad AS DOR, Repricing.ConvertedHospitalCharges AS Hosp_Charges, Repricing.HospitalChargesDiscountAmount AS Hosp_Dct_to_Insurer, Repricing.ConvertedProfessionalCharges AS Prof_Charges, Repricing.ProfessionalChargesDiscountAmount AS Prof_Dct_to_Ins, (Repricing.ConvertedTotalCharges-(Prof_Dct_to_Ins+Hosp_Dct_to_Insurer)) AS Due_By_Insurer, Claims.HospitalChargesDiscount AS Hosp_Pct_to_MD, Claims.ProfessionalChargesDiscount AS Prof_Pct_to_MD, SUM(IIf(ISNULL(ClaimsFollowUp.AmountReceived),0,ClaimsFollowUp.AmountReceived)) AS IP, Due_By_Insurer-IP AS Balance, (((Hosp_Charges-Hosp_Dct_to_Insurer)*100)/Due_By_Insurer) AS perc_hospital, (((Prof_Charges-Prof_Dct_to_Ins)*100)/Due_By_Insurer) AS perc_professional, ((Balance*perc_hospital)/100) AS Hosp_Balance, ((Balance*perc_professional)/100) AS Prof_Balance, (Hosp_Balance*Hosp_Pct_to_MD) AS Hosp_Fees_Outs, (Prof_Balance*Prof_Pct_to_MD) AS Prof_Fees_Outs, (Hosp_Fees_Outs+Prof_Fees_Outs) AS Total_Fees_Outstanding, Claims.ClaimStatus, Claims.Responsible, Claims.ClaimType

FROM (Claims INNER JOIN Repricing ON Claims.ClaimID=Repricing.RepricingID) LEFT JOIN ClaimsFollowUp ON Repricing.RepricingID=ClaimsFollowUp.RepricingID

WHERE (Claims.ClaimStatus)<>"9. Case Closed" AND DateOfReceiptByMDabroad BETWEEN date() AND date()-30 AND ClaimType="Billing"

GROUP BY Claims.ClaimStatus, Claims.ClaimID, Repricing.ConvertedTotalCharges, Repricing.ConvertedProfessionalCharges, Repricing.ConvertedHospitalCharges, Claims.HospitalChargesDiscount, Claims.ProfessionalChargesDiscount, Repricing.ProfessionalChargesDiscountAmount, Repricing.HospitalChargesDiscountAmount, Claims.DateOfService, Claims.DateOfReceiptByMDabroad, Claims.Responsible, Claims.InsurerID, Claims.ProviderID, Claims.PatientID, Claims.ClaimType; 
   

Y cuando le coloco un Simple Count(*) No me lo hace, es decir sale 1 en esa columna:

Código PHP:

  SELECT  COUNT(*) AS Total, Claims.ClaimID, Claims.PatientID AS Patient, Claims.ProviderID AS Provider, Claims.InsurerID AS Insurer, Claims.DateOfService AS DOS, Claims.DateOfReceiptByMDabroad AS DOR, Repricing.ConvertedHospitalCharges AS Hosp_Charges, Repricing.HospitalChargesDiscountAmount AS Hosp_Dct_to_Insurer, Repricing.ConvertedProfessionalCharges AS Prof_Charges, Repricing.ProfessionalChargesDiscountAmount AS Prof_Dct_to_Ins, (Repricing.ConvertedTotalCharges-(Prof_Dct_to_Ins+Hosp_Dct_to_Insurer)) AS Due_By_Insurer, Claims.HospitalChargesDiscount AS Hosp_Pct_to_MD, Claims.ProfessionalChargesDiscount AS Prof_Pct_to_MD, SUM(IIf(ISNULL(ClaimsFollowUp.AmountReceived),0,ClaimsFollowUp.AmountReceived)) AS IP, Due_By_Insurer-IP AS Balance, (((Hosp_Charges-Hosp_Dct_to_Insurer)*100)/Due_By_Insurer) AS perc_hospital, (((Prof_Charges-Prof_Dct_to_Ins)*100)/Due_By_Insurer) AS perc_professional, ((Balance*perc_hospital)/100) AS Hosp_Balance, ((Balance*perc_professional)/100) AS Prof_Balance, (Hosp_Balance*Hosp_Pct_to_MD) AS Hosp_Fees_Outs, (Prof_Balance*Prof_Pct_to_MD) AS Prof_Fees_Outs, (Hosp_Fees_Outs+Prof_Fees_Outs) AS Total_Fees_Outstanding, Claims.ClaimStatus, Claims.Responsible, Claims.ClaimType

FROM (Claims INNER JOIN Repricing ON Claims.ClaimID=Repricing.RepricingID) LEFT JOIN ClaimsFollowUp ON Repricing.RepricingID=ClaimsFollowUp.RepricingID

WHERE (Claims.ClaimStatus)<>"9. Case Closed" AND DateOfReceiptByMDabroad BETWEEN date() AND date()-30 AND ClaimType="Billing"

GROUP BY Claims.ClaimStatus, Claims.ClaimID, Repricing.ConvertedTotalCharges, Repricing.ConvertedProfessionalCharges, Repricing.ConvertedHospitalCharges, Claims.HospitalChargesDiscount, Claims.ProfessionalChargesDiscount, Repricing.ProfessionalChargesDiscountAmount, Repricing.HospitalChargesDiscountAmount, Claims.DateOfService, Claims.DateOfReceiptByMDabroad, Claims.Responsible, Claims.InsurerID, Claims.ProviderID, Claims.PatientID, Claims.ClaimType; 
   

Por qué? Como lo corrijo? Se puede o no ?

[Linterns]

El contador lo debes poner a la altura del SUm que tienes ya que recuerda que primero van los datos agrupados y luego los contadores y sumas.


tiene que tener una relacion de Group By con los primeros Select y luego hacer los diferentes SUM, COUNT, etc

[Linterns]

por cierto se me olvidaba, el Count es mejor que lo hagas de un determinado registro de una tabla secundaria al iguan que has hecho el SUM

[SOFIA_ME_GUSTA]

Fijate donde lo estoy poniendo y no funciona, me sigue saliendo 1:



SELECT Claims.ClaimID, Claims.PatientID AS Patient, Claims.ProviderID AS Provider, Claims.InsurerID AS Insurer, Claims.DateOfService AS DOS, Claims.DateOfReceiptByMDabroad AS DOR, Repricing.ConvertedHospitalCharges AS Hosp_Charges, Repricing.HospitalChargesDiscountAmount AS Hosp_Dct_to_Insurer, Repricing.ConvertedProfessionalCharges AS Prof_Charges, Repricing.ProfessionalChargesDiscountAmount AS Prof_Dct_to_Ins,
(Repricing.ConvertedTotalCharges-(Prof_Dct_to_Ins+Hosp_Dct_to_Insurer)) AS Due_By_Insurer, Claims.HospitalChargesDiscount AS Hosp_Pct_to_MD, Claims.ProfessionalChargesDiscount AS Prof_Pct_to_MD,
SUM(IIf(ISNULL(ClaimsFollowUp.AmountReceived),0,Cl aimsFollowUp.AmountReceived)) AS IP, COUNT(Claims.ClaimID) as Cantidad, Due_By_Insurer-IP AS Balance, (((Hosp_Charges-Hosp_Dct_to_Insurer)*100)/Due_By_Insurer) AS perc_hospital, (((Prof_Charges-Prof_Dct_to_Ins)*100)/Due_By_Insurer) AS perc_professional, ((Balance*perc_hospital)/100) AS Hosp_Balance, ((Balance*perc_professional)/100) AS Prof_Balance, (Hosp_Balance*Hosp_Pct_to_MD) AS Hosp_Fees_Outs, (Prof_Balance*Prof_Pct_to_MD) AS Prof_Fees_Outs, (Hosp_Fees_Outs+Prof_Fees_Outs) AS Total_Fees_Outstanding, Claims.ClaimStatus, Claims.Responsible, Claims.ClaimType
FROM (Claims INNER JOIN Repricing ON Claims.ClaimID=Repricing.RepricingID) LEFT JOIN ClaimsFollowUp ON Repricing.RepricingID=ClaimsFollowUp.RepricingID
WHERE (Claims.ClaimStatus)<>"9. Case Closed" AND DateOfReceiptByMDabroad BETWEEN date() AND date()-30 AND ClaimType="Billing"
GROUP BY Claims.ClaimStatus, Claims.ClaimID, Repricing.ConvertedTotalCharges, Repricing.ConvertedProfessionalCharges, Repricing.ConvertedHospitalCharges, Claims.HospitalChargesDiscount, Claims.ProfessionalChargesDiscount, Repricing.ProfessionalChargesDiscountAmount, Repricing.HospitalChargesDiscountAmount, Claims.DateOfService, Claims.DateOfReceiptByMDabroad, Claims.Responsible, Claims.InsurerID, Claims.ProviderID, Claims.PatientID, Claims.ClaimType;

[jpogsistem]

Alguna Idea ??? de Ninguna Manera lo he podido hacer........