Badly Behaved Query!

[csharrock69]

*SOLVED* Badly Behaved Query!

Dear all,

Please could I call upon your expert assistance in solving this query related problem?

Attached is an example database with the query in question.

The plan is to determine when a component (called a TPA) reaches its “use by” date. This happens to be after it has been used for 12*365 = 4380days. What would be really great to know is the last cycle that component can be used BEFORE it reaches the 4380 days of use. Some cycles are longer than others so one component may last 3 cycles while another lasts 10 cycles.

The query is currently correctly summing the EFPD values however component number 1 has an EFPD value over the limit but the query won’t display that the last cycle it can be used before it reaches its expiry! Doh!

Here is the SQL:


SELECT tblTPACyclesUsedIn.ID, Max(tblTPACyclesUsedIn.[Cycle Used]) AS [MaxOfCycle Used], Sum(tblCycleEFPDs.[EFPD Value]) AS [SumOfEFPD Value]
FROM tblTPAs INNER JOIN (tblCycleEFPDs INNER JOIN tblTPACyclesUsedIn ON tblCycleEFPDs.[Cycle Number]=tblTPACyclesUsedIn.[Cycle Used]) ON tblTPAs.ID=tblTPACyclesUsedIn.ID
GROUP BY tblTPACyclesUsedIn.ID
HAVING (((Sum(tblCycleEFPDs.[EFPD Value]))<(12*365)));


Please does anyone have any ideas on how to get this to work?

Thanks in advance!

Chris.

 

[PaulO]

The reason "1" doesn't get returned is your use of the "Max" control in the Query ... change this to "Group" and "1" then returns multiple values

You could then create another query with the original query as it's source table, and sum the values in the original query?

Obviously you know what you're using the Max control ... I don't ... but at least we know what the "bug" is!

 

[csharrock69]

Thank you so much PaulO! This has been bugging me for a while and I never realised it was this simple!

The second query is the one that actually answers the question in my first post.

For anyone who may find this useful, the solution has been implemented into the attachement.

Thanks again.

Chris.

 

[PaulO]

No worries ... you'll have many greater challenges ahead I reckon but if you love Access it will love you back!