Query returns different results.

[drogerson]

I have a database set up, and the end query basically gives me three results for my data.

Last Value, Average Value & Max Value.

For whatever reason, the last value does not seem to work correctly. It only seems to include the original records, and no records which have since been added (through a forms add record procedure).

Have I missed something:-

SELECT Table1.NAME, Last(Table1.Value) AS LAST, Max(Table1.Value) AS [MAX], Avg(Table1.Value) AS [AVG], Round((([LAST]+[AVG]+[MAX])/3),3) AS Value
FROM Table1
GROUP BY Table1.NAME;

 

[boblarson]

The LAST (and also the FIRST) do not do what they would seem to be there to do. It just returns a random record. That is documented behavior. If you want the last record entered, you can either use DLAST which will return the last record entered and if you include criteria in that DLAST function it will return the last record entered that fit that criteria (not the last record physically added). Or, if it is sequential, you can get it with MAX or DMAX.

One thing though - it would appear that you have used an ACCESS RESERVED WORD for a field name (VALUE) and that is not good. You should not do that as it can cause bad things to happen at various times, depending on what you are doing. And value is one that would be potentially horrendous (Name is the most serious one).

 

[drogerson]

Thank you for your reply.
The records individually have dates assigned to them, so I naturally assumed it would use this to grab the most recent record (thedata table is date ordered). I will try DLAST tomorrow.

With regards to the VALUE etc, they aren't my field names etc. I subbed them to make things less complicated to make things easier to read.

 

[boblarson]

I subbed them to make things less complicated to make things easier to read.

Believe it or not, when people do that, it becomes much harder to help sometimes because sometimes the actual name is the cause of the problem. When posting problems - use the real names.

 

[Brianwarnock]

As the records have dates assigned then you can use min and max on the date field.

Brian