i have a query that works great. till i add the highlighted section. This field is a calculated field using another calculated field in a calculation.
SELECT CUSTOMER.TRN, CUSTOMER.Active, CUSTOMER.Fname, CUSTOMER.Lname, PARTNER.PartnerId, PARTNER.Type, PARTNER.StartDate, PARTNER.StartAmount, PARTNER.PaymentMethod, PARTNER.Installments, PARTNER.Active, PARTNER.Comment, DateAdd("m",6,[StartDate]) AS MaturityDate, IIf([PaymentMethod]="Daily",[StartAmount]*6*4*6,IIf([PaymentMethod]="Weekly",[StartAmount]*4*6,IIf([PaymentMethod]="Fortnightly",[StartAmount]*2*6,IIf([PaymentMethod]="Monthly",[StartAmount]*6)))) AS MaturityAmount, PARTNER.Active, Sum(PAYMENTS.Amount) AS SumOfAmount, Max(PAYMENTS.PaymentDate) AS MaxOfPaymentDate, Count(PAYMENTS.PartnerId) AS CountOfPartnerId, Round([Amount]/[StartAmount],2) AS Paid
FROM (CUSTOMER INNER JOIN PARTNER ON CUSTOMER.TRN = PARTNER.TRN) INNER JOIN PAYMENTS ON PARTNER.PartnerId = PAYMENTS.PartnerId
GROUP BY CUSTOMER.TRN, CUSTOMER.Active, CUSTOMER.Fname, CUSTOMER.Lname, PARTNER.PartnerId, PARTNER.Type, PARTNER.StartDate, PARTNER.StartAmount, PARTNER.PaymentMethod, PARTNER.Installments, PARTNER.Active, PARTNER.Comment, DateAdd("m",6,[StartDate]), IIf([PaymentMethod]="Daily",[StartAmount]*6*4*6,IIf([PaymentMethod]="Weekly",[StartAmount]*4*6,IIf([PaymentMethod]="Fortnightly",[StartAmount]*2*6,IIf([PaymentMethod]="Monthly",[StartAmount]*6)))), PARTNER.Active, Round([Amount]/[StartAmount],2)
HAVING (((CUSTOMER.Active)=Yes) AND ((PARTNER.Active)=Yes));
cant i get this all in one row ?
please take a look at my queries. qryPartnerSummaryA and qryPartnerSummaryA
SELECT CUSTOMER.TRN, CUSTOMER.Active, CUSTOMER.Fname, CUSTOMER.Lname, PARTNER.PartnerId, PARTNER.Type, PARTNER.StartDate, PARTNER.StartAmount, PARTNER.PaymentMethod, PARTNER.Installments, PARTNER.Active, PARTNER.Comment, DateAdd("m",6,[StartDate]) AS MaturityDate, IIf([PaymentMethod]="Daily",[StartAmount]*6*4*6,IIf([PaymentMethod]="Weekly",[StartAmount]*4*6,IIf([PaymentMethod]="Fortnightly",[StartAmount]*2*6,IIf([PaymentMethod]="Monthly",[StartAmount]*6)))) AS MaturityAmount, PARTNER.Active, Sum(PAYMENTS.Amount) AS SumOfAmount, Max(PAYMENTS.PaymentDate) AS MaxOfPaymentDate, Count(PAYMENTS.PartnerId) AS CountOfPartnerId, Round([Amount]/[StartAmount],2) AS Paid
FROM (CUSTOMER INNER JOIN PARTNER ON CUSTOMER.TRN = PARTNER.TRN) INNER JOIN PAYMENTS ON PARTNER.PartnerId = PAYMENTS.PartnerId
GROUP BY CUSTOMER.TRN, CUSTOMER.Active, CUSTOMER.Fname, CUSTOMER.Lname, PARTNER.PartnerId, PARTNER.Type, PARTNER.StartDate, PARTNER.StartAmount, PARTNER.PaymentMethod, PARTNER.Installments, PARTNER.Active, PARTNER.Comment, DateAdd("m",6,[StartDate]), IIf([PaymentMethod]="Daily",[StartAmount]*6*4*6,IIf([PaymentMethod]="Weekly",[StartAmount]*4*6,IIf([PaymentMethod]="Fortnightly",[StartAmount]*2*6,IIf([PaymentMethod]="Monthly",[StartAmount]*6)))), PARTNER.Active, Round([Amount]/[StartAmount],2)
HAVING (((CUSTOMER.Active)=Yes) AND ((PARTNER.Active)=Yes));
cant i get this all in one row ?
please take a look at my queries. qryPartnerSummaryA and qryPartnerSummaryA