Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query which shows results?

Status
Not open for further replies.

Hanss

Technical User
Feb 15, 2001
85
CH
Hi,

I am wondering if there is anyway to do this with an access query:

I have table1 with two fields and data:

Temp: Count:
15 2
20 3

I would like a query that generates the following results:

Results:
15
15
20
20
20

I would appreciate any direction!
 
Code not tested...

Code:
Dim sSQL As String
Dim i As Integer

With rst
    .Open "Select * From table1"
    Do While Not .EOF
        For i = 1 To !Count.Value
            If Len(sSQL) = 0 Then
                sSQL = "Select " & !Temp.Value & " As Results From table1 " & vbNewLine
            Else
                sSQL = sSQL & " UNION ALL " & vbNewLine
                sSQL = sSQL & "Select " & !Temp.Value & " From table1 " & vbNewLine
            End IF
        Next i
        .MoveNext
    Loop
    .Close
End With

Debug.Print sSQL

The SQL you should be getting:[tt]

Select 15 As Results From table1
UNION ALL
Select 15 From table1
UNION ALL
Select 20 From table1
UNION ALL
Select 20 From table1
UNION ALL
Select 20 From table1
[/tt]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
I typically have a table of numbers to assist with tasks like this:

tblNums
Num
1
2
3
4
...

Use this table of numbers with your table in a query like:

SQL:
SELECT Temp
FROM table1, tblNums
WHERE Count>=[Num]
ORDER BY Temp;


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top