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

[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

 

[Marshall Barton]

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

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.

 

[KARL DEWEY]

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;