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
Solved

Get record count

Posted on 2011-03-24
6
346 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 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