Query Giving Wrong Results

[csharrock69]

Dear all,

Please may I summon your assistance in getting to the bottom of an access query-related problem!

There are two queries in the attached example database which act as a means of calculating when an item (called a TPA) expires and can no longer be used. Once a TPA is used in a cycle (a cycle is about 18 months long), it must remain in use until the end of that cycle. A TPA expires after its cumulative “EFPD” (Effective Full Power Days) value reaches 4380 so if it exceeds this value during a cycle, we say that its expiry date is the cycle before it reaches this value. I hope that made sense- please excuse the terminology.

There are two queries. The first is qryTPAExpiry which should find the cycles each TPA is used in and sum the EFPD values for each cycle used until it reaches 4380, this is reflected in the EFPD field criteria as <(12*365). This is the query that appears to be going wrong.

The second query simply picks the highest cycle value from the previous query, which is the expiry date. This second query appears to be working correctly.

So…. Initially the query works fine; if you put in test data to make sure the EFPD limit of 4380 is exceeded, it will indeed return the correct cycle number; ie the cycle number before it reached that value. It seems the problems begin when a ‘Cycle Used In’ value reaches 10 or above…. Why?!

As an example, TPA number 9 is used in cycles 11, 12 and 13. Looking at the tblCycleEFPDs table, we can see that these cycles are 567, 4556 and 3 EFPDs long respectively. So, to agree with the EFPD limit mentioned at the start, the returned value should be that TPA number 9 expires in cycle 11 (since 567+4556 > 4380). Unfortunately the query returns cycle 13, which violates the limit above. If you run the query yourself, which is much easier to see than me explaining it here, you’ll see some strange results. It seems to ignore an EFPD value if that value individually goes over the limit, skipping then to the next cycle. This is undesirable. I’m thinking its something to do with the criteria? A picture of the output can be seen in the attachments.

I may be being a bit thick and overlooking something basic, but access and programming doesn’t come naturally to me and I’ve been going round in circles trying to diagnose why this is doing this? Please help!

Does anyone have any ideas how to solve this or suggestions for another way around this problem?

Many thanks,

Chris.

 

[Galaxiom]

Queries don't ever give wrong results. Please don't mistake your misunderstanding of the syntax for Access getting it wrong.

I would suggest you post the SQL text of the query. Only some posters will download the sample.

 

[csharrock69]

I'm not, but on re-reading my post I can see why you may have interpreted it in that way. It should read "Query Giving Undesirable Results". Regardless, thanks for your suggestion, the SQL for the two queries is:

qryTPAExpiry:

SELECT tblTPACyclesUsedIn.ID, tblTPACyclesUsedIn.[Cycle 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, tblTPACyclesUsedIn.[Cycle Used]
HAVING (((Sum(tblCycleEFPDs.[EFPD Value]))<(12*365)));

qryTPAFinalCycle:

SELECT qryTPAExpiry.ID, Max(qryTPAExpiry.[Cycle Used]) AS [MaxOfCycle Used], Sum(qryTPAExpiry.[SumOfEFPD Value]) AS [SumOfSumOfEFPD Value]
FROM qryTPAExpiry
GROUP BY qryTPAExpiry.ID;