460,036 Members | 960 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

# Frequency Distribution

 P: n/a I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Nov 13 '05 #1
9 Replies

 P: n/a Air coded: SELECT IIf(Annual_Turnover < 5000000, "Up to 5 million", IIf(Annual_Turnover < 10000000, "5 to 10 million", IIf(Annual_Turnover < 20000000, "10 to 20 million", "More than 20 million"))) AS Annual_Turnover, COUNT(*) AS Count_ FROM YourTable GROUP BY IIf(Annual_Turnover < 5000000, "Up to 5 million", IIf(Annual_Turnover < 1000000, "5 to 10 million", IIf(Annual_Turnover < 2000000, "10 to 20 million", "More than 20 million"))) HTH Yannick "RFJ" wrote in message news:Fr***************@newsfe4-gui.ntli.net... I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Nov 13 '05 #2

 P: n/a Tx guys for the help - problem solved Rob "RFJ" wrote in message news:Fr***************@newsfe4-gui.ntli.net... I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Nov 13 '05 #3

 P: n/a "RFJ" wrote in message news:... Tx guys for the help - problem solved Rob "RFJ" wrote in message news:Fr***************@newsfe4-gui.ntli.net... I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Although your problem has already been solved, I'll write an Access 97 program to create a histogram pdf. You can replace my calculations with a nice SQL statement if you'd like. I'll email you a zipped mdb if the code gets longer than a few pages. It'll show the min, max and count of the data, then let you select the min, max and number of class intervals for the histogram. James A. Fortune Nov 13 '05 #4

 P: n/a James, I would appreciate you C/C me on this. I'd like to see how you do it. Steve PC Datasheet re******@pcdatasheet.com "James Fortune" wrote in message news:a6**************************@posting.google.c om... "RFJ" wrote in message news:... Tx guys for the help - problem solved Rob "RFJ" wrote in message news:Fr***************@newsfe4-gui.ntli.net... I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Although your problem has already been solved, I'll write an Access 97 program to create a histogram pdf. You can replace my calculations with a nice SQL statement if you'd like. I'll email you a zipped mdb if the code gets longer than a few pages. It'll show the min, max and count of the data, then let you select the min, max and number of class intervals for the histogram. James A. Fortune Nov 13 '05 #5

 P: n/a "PC Datasheet" wrote in message news:... James, I would appreciate you C/C me on this. I'd like to see how you do it. Steve PC Datasheet re******@pcdatasheet.com I cc'd you. The program still needs a little work on putting more numbers in for the class frequencies and class intervals, but I think it'll be a useful program that also demonstrates a little of what can be done by outputting pdf from Access. Note that I have the highest bar using about 90% of the available graph height so autogenerated y-axis numbers will be slightly tougher. The program used the same module code as before (except I swapped cropbox and mediabox numbers to get landscape mode) plus about five pages of code behind the form. I've also done programs that output pdf files with multiple pages, images, pdf info, report detail capability and image compression that display database information quite nicely and compactly. I envision web pages accessing pdf files that that can be automatically regenerated by Access using the latest data and the latest pictures. James A. Fortune Nov 13 '05 #6

 P: n/a Tx James - look forward to getting the code. Rob "James Fortune" wrote in message news:a6**************************@posting.google.c om... "RFJ" wrote in message news:... Tx guys for the help - problem solved Rob "RFJ" wrote in message news:Fr***************@newsfe4-gui.ntli.net... I've got a list of annual turnovers for about fifty companies and I want to summarise them by bands. A simple version would be : eg: Annual_Turnover Count Up to 5million 5 5 to 10 million 37 10 to 20 million 8 As a relative beginner in Access (XP) this is stretching me. Can SKS point me in the right direction. TIA Rob Although your problem has already been solved, I'll write an Access 97 program to create a histogram pdf. You can replace my calculations with a nice SQL statement if you'd like. I'll email you a zipped mdb if the code gets longer than a few pages. It'll show the min, max and count of the data, then let you select the min, max and number of class intervals for the histogram. James A. Fortune Nov 13 '05 #7

 P: n/a "RFJ" wrote in message news:... Tx James - look forward to getting the code. Rob I made some changes to the code but the email to you bounced. I don't want to post five pages of code. Does anyone have a nice ftp site I could use to make a zipped Access 97 mdb file available? If not, I'll talk to Keith Peterson at www.simtel.net about putting it there. BTW, Keith and I used to work for Academic Computer Services at Oakland U. when oak.oakland.edu was a mirror for simtel. This program is a lot of fun. I plan to keep improving the code. Also, is anyone interested in a cumulative frequency distribution (a.k.a., ogive). Since the histogram information is already there it would be pretty easy to add it as a second page of the pdf file. James A. Fortune Nov 13 '05 #8

 P: n/a ja******@oakland.edu (James Fortune) wrote in message news:... talk to Keith Peterson at www.simtel.net about putting it there. BTW, Oops. That's Petersen. He hasn't posted to an internet group since Nov 2000. He might be retired. I emailed him at an address from that post. I remember once when the email program on vela.acs.oakland.edu went down temporarily. "No problem." he said, "Just use smtp." Keith was one of several talented people that gravitated to O.U. when it was gaining a strong internet presence. I was the one who suggested to Bill Watt that O.U. get on the internet when I discovered that the only practical way to send data from my 5 1/4" floppy to Boston was via the USPS since Kermit at 300 baud was the only other option available. When O.U. got a great connection to the internet, Keith saw an opportunity to make SimTel (probably the world's largest software repository) available at much faster connection speeds than before. O.U. also had a huge repository of SCO UNIX software. James A. Fortune Nov 13 '05 #9

 P: n/a ja******@oakland.edu (James Fortune) wrote in message news:... ja******@oakland.edu (James Fortune) wrote in message news:... talk to Keith Peterson at www.simtel.net about putting it there. BTW, Oops. That's Petersen. He hasn't posted to an internet group since Nov 2000. He might be retired. I emailed him at an address from that post. I remember once when the email program on vela.acs.oakland.edu went down temporarily. "No problem." he said, "Just use smtp." Keith was one of several talented people that gravitated to O.U. when it was gaining a strong internet presence. I was the one who suggested to Bill Watt that O.U. get on the internet when I discovered that the only practical way to send data from my 5 1/4" floppy to Boston was via the USPS since Kermit at 300 baud was the only other option available. When O.U. got a great connection to the internet, Keith saw an opportunity to make SimTel (probably the world's largest software repository) available at much faster connection speeds than before. O.U. also had a huge repository of SCO UNIX software. James A. Fortune Here is a sample output from Histogram.mdb. It's a work in progress. I'm not sure if the newsreader will strip out what's necessary to view it or not. '------------begin output.pdf-------------------- %PDF-1.4%ßÌÑÀRÝ 1 0 obj << /Type /Catalog /Outlines 2 0 R /Pages 3 0 R endobj 2 0 obj << /Type /Outlines /Count 0 endobj 3 0 obj << /Type /Pages /Kids [4 0 R] /Count 1 endobj 4 0 obj << /Type /Page /Parent 3 0 R /MediaBox [0 0 792 612] /CropBox [0 0 792 612] /Rotate 0 /Contents [ 5 0 R] /Resources << /ProcSet 6 0 R /Font 7 0 R endobj 5 0 obj << /Length 752 >> stream %Title BT /F1 20 Tf 276 554 Td (Histogram: tbl2003.TOTAAMT) Tj ET 144 144 576 396 re s %Y-Axis Label BT /F1 14 Tf 52 370 Td (Class) Tj ET BT /F1 14 Tf 32 350 Td (Frequency) Tj ET %X-Axis Label BT /F1 14 Tf 369 72 Td (Class Intervals) Tj ET q 1 0.75 0.5 rg 192 144 48 158.22 re B Q q 0.5 0.75 1 rg 240 144 48 338.82 re B Q q 1 0.75 0.5 rg 288 144 48 356.4 re B Q q 0.5 0.75 1 rg 336 144 48 287.68 re B Q q 1 0.75 0.5 rg 384 144 48 180.6 re B Q q 0.5 0.75 1 rg 432 144 48 132.65 re B Q q 1 0.75 0.5 rg 480 144 48 83.11 re B Q q 0.5 0.75 1 rg 528 144 48 59.13 re B Q q 1 0.75 0.5 rg 576 144 48 46.35 re B Q q 0.5 0.75 1 rg 624 144 48 28.77 re B Q %Xmin number BT /F1 12 Tf 177.6 120 Td (0.00) Tj ET %Xmax number BT /F1 12 Tf 643.2 120 Td (30000.00) Tj ET endstream endobj 6 0 obj << /Procset [/PDF /Text] >> endobj 7 0 obj << /F1 8 0 R endobj 8 0 obj << /BaseFont/Courier/Type/Font/Name/F1 /Encoding /WinAnsiEncoding /Subtype/Type1 endobj xref 0 9 0000000000 65535 f 0000000017 00000 n 0000000095 00000 n 0000000150 00000 n 0000000220 00000 n 0000000422 00000 n 0000001228 00000 n 0000001274 00000 n 0000001311 00000 n trailer << /Size 9 /Root 1 0 R startxref 1427 %%EOF '------------end output.pdf-------------------- James A. Fortune Nov 13 '05 #10

### This discussion thread is closed

Replies have been disabled for this discussion.