I have an existing union query that I have been using for years and it works fine. The reason I am using a union query is because there is too much data to keep in a single table, so I broke the data down into 4 "yearly" tables (2011, 2010, 2009, 2008). The primary key of each of the tables is a compound key made up of 4 fields. One of those fields is DATEFIELD. When I run the query, I select a date range (using a WHERE clause against the DATEFIELD field) and I ORDER BY the DATEFIELD.
What I need to do now is calculate a total of all records within the selected date range [I have this part working by using =DCount("*","MyUnionQuery")] on an Access Report
have only the most recent (based on DATEFIELD values) 100 records print on my Access report. I have attempted doing this by the following:
SELECT TOP 100 * FROM MyUnionQuery ORDER BY DATEFIELD;
but it is not giving me accurate counts. For example, using the code above, I'm getting 103 records returned. If I change it to something like:
SELECT TOP 15 * FROM MyUnionQuery ORDER BY DATEFIELD; then I get 16 records returned, etc
The results are always close to the number I'm trying to retrieve but are not accurate.