# Thread: Reverse a frequency table

1. Registered User
Join Date
May 2012
Posts
9

## Unanswered: Reverse a frequency table

In Access I want to output a list of data which are held in one column with frequency of occurrence in a second column. To simplify matters greatly, if the first column holds say A, B, C and the second column 1,2,3. I want to produce an output of A,B,B,C,C,C.

In other words I am looking for a technique to expand frequency tables via query. I can of course use VBA and recordsets but was looking for an elegant general purpose approach using SQL From what I have found so far by looking at expanding for example date ranges this sort of thing doesn't appear easy via query route but would be interested to know of any such solution.
Thanks,
John B

2. Moderator
Join Date
Mar 2009
Posts
5,442
Here's a solution.

1. Create a tally table (a tally table is a table with a single column of sequential numbers starting at 0 or 1 and going up to some number). If the Max in the frequency column is rather slow, you can use:
Code:
```SELECT (Select Count(*) from [MSysObjects] as a where a.[Name]<=[MSysObjects].[Name]) AS [Counter] INTO Tbl_Tally
FROM MSysObjects
ORDER BY [MSysObjects].[Name];```
This creates a table [Tbl_Tally] of 290 rows in my sample database, meaning that the Max([Frequency]) cannot be larger than 290. Other techniques exist if you need larger numbers (see: Create tally table of 10000 sequential numbers in MS-Access or Create tally table in MS-Access).
Note: You only need to perform this operation once.

2. Let's suppose that the table you describe is named [Tbl_Frequency] and contains 2 columns: [Item] ('a', 'b', 'c',...) and [Frequency] (1, 2, 3,...), you can use:
Code:
```SELECT Tbl_Frequency.Item, Tbl_Frequency.Frequency
FROM Tbl_Frequency, Tbl_Tally
WHERE (((Tbl_Tally.Counter)<=[Tbl_Frequency].[frequency]))
ORDER BY Tbl_Frequency.Item;```

3. Registered User
Join Date
May 2012
Posts
9
Originally Posted by Sinndho
Here's a solution.

1. Create a tally table (a tally table is a table with a single column of sequential numbers starting at 0 or 1 and going up to some number). If the Max in the frequency column is rather slow, you can use:
Code:
```SELECT (Select Count(*) from [MSysObjects] as a where a.[Name]<=[MSysObjects].[Name]) AS [Counter] INTO Tbl_Tally
FROM MSysObjects
ORDER BY [MSysObjects].[Name];```
This creates a table [Tbl_Tally] of 290 rows in my sample database, meaning that the Max([Frequency]) cannot be larger than 290. Other techniques exist if you need larger numbers (see: Create tally table of 10000 sequential numbers in MS-Access or Create tally table in MS-Access).
Note: You only need to perform this operation once.

2. Let's suppose that the table you describe is named [Tbl_Frequency] and contains 2 columns: [Item] ('a', 'b', 'c',...) and [Frequency] (1, 2, 3,...), you can use:
Code:
```SELECT Tbl_Frequency.Item, Tbl_Frequency.Frequency
FROM Tbl_Frequency, Tbl_Tally
WHERE (((Tbl_Tally.Counter)<=[Tbl_Frequency].[frequency]))
ORDER BY Tbl_Frequency.Item;```
Thanks. Thought it couldn't be done without an auxiliary table but a neat solution.
John B

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•