I have a table that has the fields SpeciesID and Frequency (both Long 
Integer). Example Data:
SpeciesID       Frequency
1                         3
2                         1
3                         4

I would like somehow to see the resulting data:
SpeciesID
1
1
1
2
3
3
3
3

In other words, have SpeciesID represented in the data set as many times as 
it has frequency in the original table.

Any ideas?

On Sun, 28 Feb 2010 21:19:01 -0800, Clddleopard
<Clddleopard@discussions.microsoft.com> wrote:

>I have a table that has the fields SpeciesID and Frequency (both Long 
>Integer). Example Data:
>SpeciesID       Frequency
>1                         3
>2                         1
>3                         4
>
>I would like somehow to see the resulting data:
>SpeciesID
>1
>1
>1
>2
>3
>3
>3
>3
>
>In other words, have SpeciesID represented in the data set as many times as 
>it has frequency in the original table.
>
>Any ideas?

You can do this with the help of a little auxiliary table. I'll usually add a
table named Num, with one Long Integer field N, filled with values from 0
through 10000 or so (use Excel... Insert... Series and copy and paste is a
quick way to fill it).

A query

SELECT SpeciesID
FROM yourtable INNER JOIN Num
ON Num.N < yourtable.Frequency

will give you the result you describe.
-- 

             John W. Vinson [MVP]

Thanks John, that worked great, though I did end up having to write it as 
SELECT SpeciesID
FROM yourtable INNER JOIN Num
ON Num.N < =yourtable.Frequency
to get the correct result.
Thanks again!
"John W. Vinson" wrote:

> On Sun, 28 Feb 2010 21:19:01 -0800, Clddleopard
> <Clddleopard@discussions.microsoft.com> wrote:
> 
> >I have a table that has the fields SpeciesID and Frequency (both Long 
> >Integer). Example Data:
> >SpeciesID       Frequency
> >1                         3
> >2                         1
> >3                         4
> >
> >I would like somehow to see the resulting data:
> >SpeciesID
> >1
> >1
> >1
> >2
> >3
> >3
> >3
> >3
> >
> >In other words, have SpeciesID represented in the data set as many times as 
> >it has frequency in the original table.
> >
> >Any ideas?
> 
> You can do this with the help of a little auxiliary table. I'll usually add a
> table named Num, with one Long Integer field N, filled with values from 0
> through 10000 or so (use Excel... Insert... Series and copy and paste is a
> quick way to fill it).
> 
> A query
> 
> SELECT SpeciesID
> FROM yourtable INNER JOIN Num
> ON Num.N < yourtable.Frequency
> 
> will give you the result you describe.
> -- 
> 
>              John W. Vinson [MVP]
> .
>