[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Get record count

Posted on 2011-03-24
6
Medium Priority
?
369 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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