FpwL1399,
In order to do the last 30 entries, you would have to first decide on an order to sort your data. If you want to sort the data by
date, that is fine.
But what happens if there are 20 records or each date? I will get all of yesterdays records (provided no entries for today has been
made yet) and then only half of the records for the day before yesterday. Now if you don't care which 10 of those 20 records are
used then you will be okay. But if you want a specific 10, you will have to find another field to sort on. On the other hand, if
you have a date field and a time field (or a field with date and time combined together in one), then you can sort by date and time
together and grab the last 30 that way.
I think this might work:
SELECT TOP 30 *
FROM TableName
ORDER BY DateField DESC
Now this may not work also. This might return the first 30 records in the table, unsorted, and then sort those 30 records in
descending order. If that is the case, then you might try this:
First create a subquery of the table sorted in descending order (maybe name it qrySubQuery or qryLast30Records_SubQuery). The SQL
syntax will be something like this:
SELECT *
FROM TableName
ORDER BY DateField DESC;
Then you can create your query based on that subquery (instead of basing it on the table directly--name it what ever is meaningful
to you). The syntax might be something like this:
SELECT TOP 30 *
FROM qrySubQuery (or qryLast30Records_SubQuery or what ever name you used before)
ORDER BY DateField;
***CAUTION: These SQL statements were manually typed in. I did not copy-n-paste. Hopefully they are correct.
If you are asking "Where do I enter these SQL statements at?", you need to enter them in SQL view of the query. The query has
Design view, Datasheet view (results of the query), and SQL view. The SQL view is also a design view, but instead of being
GUI-esque, it is more like a command line where you can type in (or paste in) the statements. When ever you design a query in the
Design view, you can switch to SQL view and Access will automatically generate the SQL statements that are equivalent to your
graphical design. In order to go to SQL view, click "View" on the Menu Bar and then select SQL view.
If these SQL statements are written correctly, you can paste them into SQL view (but changing the table and field names accordingly)
and then switch to Design view and Access will automatically construct the graphical design (as long as the syntax is correct).
You'll also notice that I used the TOP clause in the SQL statements. There is no BOTTOM clause or MIDDLE clause. So in order to
get the last 30 records, you first need to sort your data in descending order so the last 30 records you want will actually be the
first 30 records in the results of your query, and then you can grab the TOP 30 and sort them any way you want. And if you want a
certain number of records from the middle, it gets even more complicated with 3 or 4 + levels of subqueries.
If you are wondering how to use the TOP clause in Design view, on the tool bar, there is a drop-down combo box. There are set
values in there of 5, 25, 100, 5%, 25%, and All. These translate to the TOP 5 records, TOP 25 records, TOP 100, TOP 5 PERCENT, and
TOP 25 PERCENT. You can also type in whatever value that you want in this combo box. If you want the first 30 records, then type
30. If you want the first third of the whole recordset, then type 33%.
I hope this helps,
Conan Kelly
