Populate Access Table with Same number of records for each integer 1

[rradelet]

I have a simple Access 2000 table that contains one
numeric long integer field with 0 decimal places.

I am trying to populate the table so the count of the number of records for each integer between 1 and 500 is the same as that integer (1 row of 1, 2 rows of 2's, 3 rows of 3's etc):

Row 1 1
Row 2 2
Row 3 2
Row 4 3
Row 5 3
Row 6 3
Row 7 4
Row 8 4
Row 9 4
Row 10 4
Row 11 5
Row 12 5
Row 13 5
Row 14 5
Row 15 5
.
.
.
Row 125249 500
Row 125250 500


Can anyone help out with how this can be accomplished.

 

[dhookom]

You can start with a table of numbers 0-9
[tt][blue]
tblNums0_9
[num]
0
1
2
3
4
5
6
7
8
9
[/blue][/tt]
Then create a query [qsel500]

SELECT [tblNum0_9]![Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100 AS B500
FROM tblNum0_9, tblNum0_9 AS tblNum0_9_1, tblNum0_9 AS tblNum0_9_2
WHERE ((([tblNum0_9]![Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100)<=500));

Then create another query to get your final values:

SELECT qsel500_1.Nums
FROM qsel500, qsel500 AS qsel500_1
WHERE (((qsel500.Nums) Between 1 And [qsel500_1].[Nums]) AND ((qsel500_1.Nums)>0));

Duane
Hook'D on Access
MS Access MVP

 

[rradelet]

This first query works, but when I run the second query, I get "Enter Parameter Value" prompts:

1: qsel500_1.Nums

2: qsel500.Nums

 

[dhookom]

You need to trouble-shoot my typos. Try the first query SQL of:

SELECT [tblNum0_9]![Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100 AS Nums
FROM tblNum0_9, tblNum0_9 AS tblNum0_9_1, tblNum0_9 AS tblNum0_9_2
WHERE ((([tblNum0_9]![Num]+[tblNum0_9_1].[Num]*10+[tblNum0_9_2].[Num]*100)<=500));

Duane
Hook'D on Access
MS Access MVP

 

[rradelet]

It now works perfectly. Thanks so much. I am still kind of a novice with Access queries, so you have saved me hours of copying and pasting.