• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Get record count

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
dbfromnewjersey
Asked:
dbfromnewjersey
  • 3
  • 2
2 Solutions
 
SharathData EngineerCommented:
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
 
dbfromnewjerseyAuthor Commented:
Yes but how do I get them both to appear on one report?
0
 
dbfromnewjerseyAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
GRayLCommented:
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
 
dbfromnewjerseyAuthor Commented:
Thank you very much
0
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now