Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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;
    Attached Thumbnails Attached Thumbnails Tbl_Tally.JPG   Tbl_Frequency.JPG   Qry_DeAggregate.JPG  
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    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
  •