Wrox Programmer Forums Query Help
 |
 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

September 11th, 2003, 11:54 AM
 kev_79 Authorized User Join Date: Jun 2003 Posts: 18 Thanks: 0 Thanked 0 Times in 0 Posts
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

September 17th, 2003, 01:50 AM
 Steven Friend of Wrox Join Date: Jun 2003 Posts: 308 Thanks: 0 Thanked 0 Times in 0 Posts

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. [email protected] 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