Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 11th, 2003, 11:54 AM
Authorized User
 
Join Date: Jun 2003
Location: Conway, AR, USA.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kev_79
Default Query Help

This should be a simple one for someone. I need to count the number of records in a field that equal 16 in one field (as an id #) and then in another table, count the records that have 16 in an id field. I can't figure out how to do this. In the end, I am wanting to divide the amount from one table by the hole for a percentage.

Any help is appreciated.

Kevin
  #2 (permalink)  
Old September 17th, 2003, 01:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This will give you the count of records where id=16
Code:
SELECT Count(id)
FROM Table1
HAVING id=16;
To combine the two tables, and get a percentage, there's quite a few different methods you could use - one example - which is possibly the worst, is:
Code:
SELECT  ([T2]/[T1])*100 As Percentage
FROM (SELECT Count(id) AS T1 from Table1 having id=16) As Tbl1, 
SELECT Count(id) AS T2 from Table2 having id=16) AS Tbl2;

Just as a note on calculating the percentages - you may prefer to multiply rather than divide, as dividing is a more complex command at machine level.
Of course, in a simple calculation it will only result in the answer taking a nanosecond or two longer, and the majority of compilers change division to multiplication, but mentioning it does pad out this reply which would otherwise just be me posting the code

I am a loud man with a very large hat. This means I am in charge


Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.