Query bug (1 Viewer)

Gr3g0ry

Registered User.
Local time
Today, 10:00
Joined
Oct 12, 2017
Messages
163
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
 

Attachments

  • CUPMS.accdb
    1.8 MB · Views: 149

isladogs

MVP / VIP
Local time
Today, 17:00
Joined
Jan 14, 2017
Messages
18,186
Try replacing
Code:
Round([Amount]/[StartAmount],2) AS Paid
with
Code:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2002
Messages
42,971
1. You have to be careful with division. You cannot divide by zero or null.

IIf(Nz([StartAmount],0) = 0,0, Round(([Amount]/[StartAmount]),2)) AS Paid

2. Your HAVING clause should be a WHERE clause. Where is applied BEFORE aggregation and HAVING is applied AFTER the data is aggregated. You are aggregating data where the Active fields may not be Yes. WHERE is used when the selection is based on the raw data. HAVING is used when the selection is based on an aggregated value such as OrderCount > 500.
 

Gr3g0ry

Registered User.
Local time
Today, 10:00
Joined
Oct 12, 2017
Messages
163
@ 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

Banishment Pending
Local time
Today, 12:00
Joined
May 11, 2011
Messages
11,611
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

Have been here a while
Local time
Today, 18:00
Joined
Jun 17, 2012
Messages
7,732
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.
Code:
[B]CCur([Amount]/[StartAmount]) AS Paid[/B]
If it is not that, then you've to explain what exactly your problem is.
 

JHB

Have been here a while
Local time
Today, 18:00
Joined
Jun 17, 2012
Messages
7,732
Did you get your problem solved?
 

Gr3g0ry

Registered User.
Local time
Today, 10:00
Joined
Oct 12, 2017
Messages
163
no sorry. i didnt. i went camping for a few days. now im back and im back where i left my issue
 

Gr3g0ry

Registered User.
Local time
Today, 10:00
Joined
Oct 12, 2017
Messages
163
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

Banishment Pending
Local time
Today, 12:00
Joined
May 11, 2011
Messages
11,611
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

Registered User.
Local time
Today, 10:00
Joined
Oct 12, 2017
Messages
163
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

Have been here a while
Local time
Today, 18:00
Joined
Jun 17, 2012
Messages
7,732
Try it now, database attached:
You have a backslash and not a division sign in your calculation: [Amount]\[StartAmount]
 

Attachments

  • CUPMS.zip
    685.5 KB · Views: 102

Users who are viewing this thread

Top Bottom