FIRST & LAST Queries

[dr4ke]

Morning all,

I have been using a Database for around 9 months, and I've just realised it doesn't work properly (much to my dismay). I have a query that runs a FIRST and LAST function to compare the same data for two months. When looking today to compare the data between 03/01/2012 and 24/09/2012 I've realised it doesn't work; the data is sorted to show the date as 'Oldest to Newest' but the FIRST and LAST query don't seem to acknowledge that filter and don't pull the correct information off.

Am I missing something? Unfortunately it's difficult to send the report itself because there is sensitive data within.

[JoeM]

I would recommend using MIN and MAX with dates or numbers instead of FIRST and LAST.
Also note that if you are using these Aggregate Functions to return the earliest and latest days, but you actually want some "amount" field associated with those dates, you cannot do all that in one query (or at least not within nesting a subquery). You need one query to just return the dates you want. You would then need to link that query back to your original table (including your date in the join) to return the "amount" field assoicated with that date.

If you are still having trouble figuring it out, post some "dummy" date here, along with your queries so we can see exactly what you are doing.

[dr4ke]

Thank you for your response. I am going to make a wild assumption (and hope I don't make an arse out of myself) - I'd replace FIRST with MIN and LAST with MAX? So... If I had the below dates MIN would return '13/09/2012' and MAX would return 19/09/2012. Will it factor in the year as well? So if I had 13/09/2011 and 13/09/2012; would it see these in the right order?


13/09/2012
15/09/2012
19/09/2012

I believe the incorporation of Aggregate and Non-Aggregate functions is also a problem with the FIRST and LAST functions. I managed to find a workaround so hopefully this will work with the MIN and MAX; once i've confirmed my understanding is correct :-)

Thanks again for the help, look forward to your response.

[JoeM]

Will it factor in the year as well? So if I had 13/09/2011 and 13/09/2012; would it see these in the right order?

Yes, provided your fields are actually are of Date type (and not Text).
You should be able to check/confirm that pretty easily. Just create a new query, and only add this date field. Change it to an Aggregate Query, and select "MIN" on the Totals row and view the results. Does it appear to be returning the correct value?
Then try the same thing with "MAX".

[dr4ke]

Thank you for your replies. I can't seem to get it working

I've provided some dummy data. As you can see in the table provided, the same aggregate/volume names are entered everyday but the usage totals change. What I am aiming to do is compare the data to get the difference... As you say I can't use Aggregate Functions and Non-Aggregate Functions so i'm not sure how to get this I used to get it with a FIRST and LAST function....

Please help :-)

[JoeM]

I am unable to download files from my current location. If you can post screen prints, or excepts of the data, and the SQL code of your query, I can see it today. Otherwise, I probably won't be able to download your file until I am at home tonight or this weekend.

[dr4ke]

Hi - below is the table information. So what I am looking to do is create a query where the 'First Date' (I.e. 27/09/2012) is displayed along with the data (Used/Total/Aggregate/Volume) and the 'Last Date' (I.e. 28/09/2012) is displayed along with the data (Used/Total/Aggregate/Volume) and then write a query that compares the two (e.g. For Aggregate 1, Volume AA, the difference between 27/09/2012 - 28/09/2012 is 100).

The MIN and MAX dates should then be able to be decided based on a 'Form' drop down date. I did this when using a FIRST and LAST query with the code below.

 WHERE ((AggregateCapacity.Date) Between (Forms!FrontPage!SelectStart) And (Forms!FrontPage!SelectEnd))

ID	Aggregate	Volume	Date	Used	Total
4	1	AA	27/09/2012	500	1000
5	1	BB	27/09/2012	600	1000
6	1	CC	27/09/2012	300	1000
7	2	AA	27/09/2012	100	1000
8	2	BB	27/09/2012	200	1000
9	1	AA	28/09/2012	600	1000
10	1	BB	28/09/2012	100	1000
11	1	CC	28/09/2012	400	1000
12	2	AA	28/09/2012	100	1000
13	2	BB	28/09/2012	500	1000

[JoeM]

Can you post your latest/greatest complete SQL code, instead of just your criteria?

Thanks

[June7]

You want to calculate with data of sequential records. For one method, review example for Get Value in Another Record at http://allenbrowne.com/subquery-01.html

Consider:
SELECT Table1.*, Query1.Used, Query1.Date, Query1.DateMinus1
FROM Table1 INNER JOIN (SELECT Table1.*, [Date]-1 AS DateMinus1 FROM Table1) AS Query1 ON (Table1.Volume = Query1.Volume) AND (Table1.Aggregate = Query1.Aggregate) AND (Table1.Date = Query1.DateMinus1);


BTW, Date is a reserved word in Access/VBA. Advise not using reserved words as names.

[dr4ke]

June7 - Thank you very much for the comment. I don't believe I have used the method you suggested, but it did prompt me into another way of thinking and I have been able to get the result I was after.

It does, at first glance, appear to be slightly messier/longer; but it's simpler and is a way I was able to write and understand fully. For those who are interested, I've produced my methodology below.

Query 1 (AggregateQueryMin)

 SELECT AggregateCapacity.SANName, AggregateCapacity.AggregateName, MIN(AggregateCapacity.Date) AS FirstDate
FROM AggregateCapacity
WHERE ((AggregateCapacity.Date) Between (Forms!FrontPage!SelectStart) And (Forms!FrontPage!SelectEnd))
GROUP BY AggregateCapacity.SANName, AggregateCapacity.AggregateName;

Query 2 (AggregateQueryMINDetail)

 SELECT AggregateCapacity.Used, AggregateCapacity.Total, AggregateQueryMIN.SANName, AggregateQueryMIN.AggregateName, AggregateQueryMIN.FirstDate
FROM AggregateCapacity INNER JOIN AggregateQueryMIN ON (AggregateCapacity.Date=AggregateQueryMin.FirstDate) AND (AggregateCapacity.AggregateName=AggregateQueryMin.AggregateName) AND (AggregateCapacity.SANName=AggregateQueryMIN.SANName);

I've then done these same queries, but with 'MAX' replacing 'MIN' - in both Queries and Query names.

Query 3 (AggregateComparisons)

 SELECT AggregateQueryMINDetail.SANName, AggregateQueryMinDetail.AggregateName, AggregateQueryMinDetail.Used AS FirstUsed, AggregateQueryMinDetail.Total As FirstTotal, AggregateQueryMAXDetail.Used AS LastUsed, AggregateQueryMAXDetail.Total AS LastTotal
FROM AggregateQueryMINDetail
INNER JOIN AggregateQueryMAXDetail
ON AggregateQueryMINDetail.SANName=AggregateQueryMaxDetail.SanName AND AggregateQueryMINDetail.AggregateName=AggregateQueryMAXDetail.AggregateName;

[dr4ke]

Just to follow this up, this works like a dream and does exactly what I was looking for.

Thanks to all who assisted in hepling me reach the desired outcome. I hope somebody finds my code helpful at some point....