?
Solved

Get record count

Posted on 2011-03-24
6
Medium Priority
?
362 Views
Last Modified: 2012-06-21
OK. This one is kind of crazy.  I currently have a UNION query made up of 5 tables.
I use the query to produce a report of all records within a date range. Each record is one line on my report.

I need to modify it. What I need to do is get a count of the total records in a population.
The population would be all records within the date range I specify.  Let's say I specify a date range of 1/1/2008 thru 12/31/2009 and that returns 100,000 records.  I need to be able to display that count (100,000) on my report but not print the 100,000 records.

Of the population, I need to print the most current 500 records.

Like I indicated above, I currently have an existing report I use that prints all records within the date range I specify.  What I'm trying to do just display the count (in a textbox or whatever) of the total records that are returned within that date range but only print the most current 500 of them (my tables have a Date Field to identify what is most current)



0
Comment
Question by:dbfromnewjersey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 35208410
1) Create your UNION query and name it as Query1.

2) To get the count of records over a date range, you can try this.
select count(*) from Query1 where Datefield between date1 and date2

3) To get the top 500 records,try this.
select top 500 * from Query1 order by Datefield desc
0
 

Author Comment

by:dbfromnewjersey
ID: 35209523
Yes but how do I get them both to appear on one report?
0
 

Author Comment

by:dbfromnewjersey
ID: 35209594
In other words, I need the report to display the detail data from this query:

select top 500 * from Query1 order by Datefield desc

and I need a textbox on the report to display the number from this query:

select count(*) from Query1 where Datefield between date1 and date2

How do I do that?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 44

Accepted Solution

by:
GRayL earned 600 total points
ID: 35209987
use the DCount function.  In the control source property of the textbox

=DCount("*","Query1")

for the report you can use the query:

select top 500 * from Query1 order by Datefield desc
0
 

Author Comment

by:dbfromnewjersey
ID: 35235184
Thank you very much
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35235258
Thanks, glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question