Query bug

[Gr3g0ry]

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

 

[isladogs]

Try replacing

Round([Amount]/[StartAmount],2) AS Paid

with

Round(([Amount]/[StartAmount]),2) AS Paid

Adding brackets ensures the calculation is done then rounded to 2 d.p.

BTW the square brackets are unnecessary

 

[Gr3g0ry]

@ PatHartman & @ ridders, i tried both suggestions but none worked.

the error i get now is Syntax Error (missing operator in .....)
im stumped. Did any of you open the database to see my dilemma?

the query is called qryPartnerSummaryB . this is the one i need to work.

this is the last thing i need solved in my experiment.

 

[plog]

I opened the database you posted and all 3 queries work for me (qryPartnerSummaryA, qryPartnerSummaryB, and the SQL you posted). Can you post the SQL that is causing the issue?

Further, that's an awful lot of items in the GROUP BY. Seems this could be better done with a subquery or two. I mean, qryPartnerSummaryA produces 3 results, qryPartnerSummaryB produces 7 results. None of the 3 tables that comprise those queries has exactly 3/7 rows. Seems odd that you would want 3/7 rows in the results of the query.

 

[JHB]

Like plog I ran your query (qryPartnerSummaryB) without problem even if I added the Round function.
But in your calculation it isn't anything to round, so ...!
Did you expect your calculation would show with 2 decimals?
If yes, then use the CCur function instead of the Round function.

[B]CCur([Amount]/[StartAmount]) AS Paid[/B]

If it is not that, then you've to explain what exactly your problem is.

 

[JHB]

Did you get your problem solved?

 

[Gr3g0ry]

no sorry. i didnt. i went camping for a few days. now im back and im back where i left my issue

 

[Gr3g0ry]

qryPartnerSummaryB should look like qryPartnerSummaryA with all records in one row except with the additional field at the end which is supposed to return a figure for each record. notice in qryPartnerSummaryB i have multiple records with the same partnerID. i want one record per partnerId, like in qryPartnerSummaryA

 

[plog]

I really think you need to tell us what you are hoping to achieve. Don't reference any existing queries, just tell us in generic terms what you hope this new query will tell you.

This really comes down to this being a GROUP BY query and you including so many fields in the GROUP BY. In a GROUP BY query the rows are unique based on what fields you have in the GROUP BY.

So if you have this:

SELECT SalesPerson, SUM(SalesAmount) FROM TableSales GROUP BY SalesPerson

You are going to get just one record for every SalesPerson value--because thats what you put in the GROUP BY.

When you start adding more fields to the GROUP BY the resulting rows become less unique (for lack of a better term) because there exist more values in whatever you add to the GROUP BY. This query for example:

SELECT SalesPerson, SalesDate, SUM(SalesAmount) FROM TableSales GROUP BY SalesPerson, SalesDate;

Will return a record for every SalesPerson/SalesDate permutation you have in your table because you added both those fields to the GROUP BY. This is most likely the cause of your issue--you've jammed so many things into the GROUP BY the records are unique, but unique in the manner you want.

So, tell us what you hope to achieve without referencing any prior work of yours.

 

[Gr3g0ry]

ok. i have a table called Partner which holds all info for a partner: partnerid, startdate, enddate, startamount etc.

i have a table called payments which has three fields, partnerid, amount and paymentdate.

i want to pull all information for partner as well as sumofamount, countofPartnerid from my payments table. i also want about 2 calculated fields in my query based on fields from my tables.

 

[JHB]

Try it now, database attached:
You have a backslash and not a division sign in your calculation: [Amount]\[StartAmount]