UtterAccess Discussion Forums > Return Multiple Rows Based On Field Value

Full Version: Return Multiple Rows Based On Field Value

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

Vongalin

May 28 2012, 10:08 AM

Hello all. I am trying to return multiple rows based on the number stored in a field.

Here is some sample data :
Sample Table
CardName, CardText, CardAmount

Sample Data
First Card, This is the first card, 1
Second Card, This is the second card, 2
Third Card, This is the third card, 3

Query Results
First Card, This is the first card
Second Card, This is the second card
Second Card, This is the second card
Third Card, This is the third card
Third Card, This is the third card
Third Card, This is the third card

Is this possible in Access ? All I could find w/my Google-Fu was T-SQL and syntax that isn't in Access.

Thank you for any help that you can provide.

Doug Steele

May 28 2012, 10:33 AM

First, create a subquery that returns all of the unique values of CardAmount:

CODE

SELECT DISTINCT CardAmount
FROM SampleTable

Now, use that subquery in a Cartesian Join with your table:

CODE

SELECT SampleTable.CardName, SampleTable.CardText, SampleTable.CardAmount
FROM SampleTable, (SELECT DISTINCT CardAmount FROM SampleTable) AS Subq
WHERE SampleTable.CardAmount >= Subq.CardAmount
ORDER BY 1, 2, 3

Vongalin

May 28 2012, 10:58 AM

Worked perfectly - learned something new today.

Thank you very much for your help and quick response.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.