"Decompose" & randomly resample records?

[LeAnne]

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

 

[Michel Walsh]

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]

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

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

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

 

[John Vinson]

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]