Query to show 2 records from each unique entry in a field

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hello All

I want to produce a query showing TWO records of each unique entry in
a particular field.

I used the Group By function to give me one record from each unique
entry in a field but now need to create a sample database/query
showing 2 of each entry.

Can anyone tell me how to do this please?

My original query is:
SELECT First(Customers.ID) AS FirstOfID, Customers.OfferType
FROM Customers
GROUP BY Customers.OfferType;


many thanks

Richard
 
Richard said:
I want to produce a query showing TWO records of each unique entry in
a particular field.

I used the Group By function to give me one record from each unique
entry in a field but now need to create a sample database/query
showing 2 of each entry.

Can anyone tell me how to do this please?

My original query is:
SELECT First(Customers.ID) AS FirstOfID, Customers.OfferType
FROM Customers
GROUP BY Customers.OfferType
Click to expand...


Try this if you want the two IDs on the same record:

SELECT Min(ID) AS FirstOfID, Max(ID) AS LastOfID,OfferType
FROM Customers
GROUP BY OfferType

or, if you want two separate records for the IDs:

SELECT Min(ID) AS FirstOfID,OfferType
FROM Customers
GROUP BY OfferType
UNION
SELECT Max(ID) AS LastOfID,OfferType
FROM Customers
GROUP BY OfferType

First and Last may be used instead of Min and Max, but the
IDs they return may appear to be selected randomly.
 
You did not show sample data like this --
Now --
Customers.ID Customers.OfferType
1 R
1 T
2 Q
2 S

Wanted --
1 R
1 R
1 T
1 T
2 Q
2 Q
2 S
2 S

If the above is what you want then create a table named MyTable with field
Something having two records.

Use this query --
SELECT First(Customers.ID) AS FirstOfID, Customers.OfferType
FROM Customers, MyTable
GROUP BY Customers.OfferType;
 
Back
Top