Get count of most recent records

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

AND........

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.

What gives?






dbfromnewjerseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
there are duplicate values on the DateField.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
When you use the "TOP #" predicate in your query, it will return # number of records, AND ALL THOSE THAT MATCH THE CRITERIA ESTABLISHED IN YOUR ORDER BY CLAUSE.

If you really only want 100 records, you will need to add another field to the Order By clause.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
To further explain ... from Help:

"Typically, you use the TopValues property setting together with sorted fields. The field you want to display top values for should be the leftmost field that has the Sort box selected in the query design grid. An ascending sort returns the bottommost records, and a descending sort returns the topmost records. If you specify that a specific number of records be returned, all records with values that match the value in the last record are also returned."

For example, suppose a set of employees has the following sales totals.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King
60,000 Suyama
50,000 Buchanan


If you set the TopValues property to 3 with a descending sort on the Sales field, Microsoft Access returns the following four records.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King

mx
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dbfromnewjerseyAuthor Commented:
Just a quick reply... haven't tested it yet.  I tried adding an additional field to my order by (another field that's part of my key) and it still didn't correct the situation.  Should I include all 4 fields of my compound key in the ORDER BY?  
Rey Obrero (Capricorn1)Commented:
<Should I include all 4 fields of my compound key in the ORDER BY?  >

that would be a good idea
dbfromnewjerseyAuthor Commented:
Thanks. Love the sarcasm too.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.