If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Decompose" & randomly resample records?
Hi, all. This is one of those "Is this even possible?!?" posts, so
please bear with me. Picture an Access recordset with the following nicely normalized information: EventID Bug BugCount 1 Blue 3 1 Red 2 1 Green 2 1 Yellow 1 2 Red 2 2 Yellow 2 .... What I wish to do is to "decompose" or "deconstruct" (not sure of the verb) the counts for each bug type for each event into an actual bug list for each event like so: Event Bug BugCount 1 Blue 1 1 Blue 1 1 Blue 1 1 Red 1 1 Red 1 1 Green 1 1 Green 1 1 Yellow 1 .... My intent is to somehow randomly resample the bug list for each event until the total bug count for each event reaches a certain pre-determined size (e.g when Sum([BugCount]) = 100, or 250, or 500 individuals). The ultimate goal is to compare taxonomic compositions of subsamples of different sizes to see if there are any significant differences. Unfortunately, I am completely clueless when it comes to writing code, which I'm fairly sure is needed here. Can anyone point me in the right direction? tia, LeAnne |
#2
|
|||
|
|||
"Decompose" & randomly resample records?
Hi,
Sure. If you have a table, Iotas, one field, its primarykey, iota, and records from 1 to, say, 999, then: SELECT a.eventID, a.bug, 1 As decomposed FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota will do just fine. You can also try: SELECT a.eventID, a.bug, b.iota FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota if you want 1, 2, 3, ... rather than all ones, for the last field. To pick up 5 random records, over the whole set of generated records, someone can use: SELECT TOP 5 a.eventID, a.bug, b.iota FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota ORDER BY Rnd(b.iota) and an initial record having its bugCount = 50 will be 50 more time luckier than a record having its bugCount=1. Note that a record having its bugCount=50 can, in theory, be picked 5 times too! Hoping it may help, Vanderghast, Access MVP "LeAnne" wrote in message ... Hi, all. This is one of those "Is this even possible?!?" posts, so please bear with me. Picture an Access recordset with the following nicely normalized information: EventID Bug BugCount 1 Blue 3 1 Red 2 1 Green 2 1 Yellow 1 2 Red 2 2 Yellow 2 ... What I wish to do is to "decompose" or "deconstruct" (not sure of the verb) the counts for each bug type for each event into an actual bug list for each event like so: Event Bug BugCount 1 Blue 1 1 Blue 1 1 Blue 1 1 Red 1 1 Red 1 1 Green 1 1 Green 1 1 Yellow 1 ... My intent is to somehow randomly resample the bug list for each event until the total bug count for each event reaches a certain pre-determined size (e.g when Sum([BugCount]) = 100, or 250, or 500 individuals). The ultimate goal is to compare taxonomic compositions of subsamples of different sizes to see if there are any significant differences. Unfortunately, I am completely clueless when it comes to writing code, which I'm fairly sure is needed here. Can anyone point me in the right direction? tia, LeAnne |
#3
|
|||
|
|||
"Decompose" & randomly resample records?
Hi Michael,
Thanks for replying. I am eager to try your solution. However, and I apologise for being clueless, but...what on earth are "iotas"? Access Help provides no clues. Thanks again, LeAnne Michel Walsh wrote: Hi, Sure. If you have a table, Iotas, one field, its primarykey, iota, and records from 1 to, say, 999, then: SELECT a.eventID, a.bug, 1 As decomposed FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota will do just fine. You can also try: SELECT a.eventID, a.bug, b.iota FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota if you want 1, 2, 3, ... rather than all ones, for the last field. To pick up 5 random records, over the whole set of generated records, someone can use: SELECT TOP 5 a.eventID, a.bug, b.iota FROM myTable As a INNER JOIN iotas As b ON a.BugCount = b.iota ORDER BY Rnd(b.iota) and an initial record having its bugCount = 50 will be 50 more time luckier than a record having its bugCount=1. Note that a record having its bugCount=50 can, in theory, be picked 5 times too! Hoping it may help, Vanderghast, Access MVP "LeAnne" wrote in message ... Hi, all. This is one of those "Is this even possible?!?" posts, so please bear with me. Picture an Access recordset with the following nicely normalized information: EventID Bug BugCount 1 Blue 3 1 Red 2 1 Green 2 1 Yellow 1 2 Red 2 2 Yellow 2 ... What I wish to do is to "decompose" or "deconstruct" (not sure of the verb) the counts for each bug type for each event into an actual bug list for each event like so: Event Bug BugCount 1 Blue 1 1 Blue 1 1 Blue 1 1 Red 1 1 Red 1 1 Green 1 1 Green 1 1 Yellow 1 ... My intent is to somehow randomly resample the bug list for each event until the total bug count for each event reaches a certain pre-determined size (e.g when Sum([BugCount]) = 100, or 250, or 500 individuals). The ultimate goal is to compare taxonomic compositions of subsamples of different sizes to see if there are any significant differences. Unfortunately, I am completely clueless when it comes to writing code, which I'm fairly sure is needed here. Can anyone point me in the right direction? tia, LeAnne |
#4
|
|||
|
|||
"Decompose" & randomly resample records?
On Fri, 28 Jul 2006 10:19:05 -0400, LeAnne wrote:
Hi Michael, Thanks for replying. I am eager to try your solution. However, and I apologise for being clueless, but...what on earth are "iotas"? Access Help provides no clues. He's suggesting that you create a new table named [Iotas] with one field, [Iota]. The name is from the Greek letter, and it's a traditional name in algebra for a sequential integer. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filtering records on a form using multiple combo boxes | Kevin Kraemer | Using Forms | 15 | February 8th, 2010 10:44 PM |
Looping through records | Abes | General Discussion | 0 | December 12th, 2005 12:59 AM |
Appending ONLY new records to a table | Ofer | Running & Setting Up Queries | 0 | April 27th, 2005 11:13 PM |
count number of records | Joe_Access | General Discussion | 1 | January 13th, 2005 06:27 PM |
Filling a dataset with randomly chosen records | Jenny Yellman | Running & Setting Up Queries | 1 | August 28th, 2004 01:31 PM |