Solved

Get record count

Posted on 2011-03-24
6
341 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
  • 3
  • 2
6 Comments
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 100 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 44

Accepted Solution

by:
GRayL earned 150 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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