Can I use Select Top of every group in a Query?

Z

zionsaal

I have a transaction table with a field named PersonID
I want to select all the fields of the table with only the last 10
trans of each person sorted DESC by date
can I use the select top here?
thanks in advance
 
Z

zionsaal

I have a transaction table with a field named PersonID
I want to select all the fields of the table with only the last 10
trans of each person sorted DESC by date
can I use the select top here?
thanks in advance
Click to expand...

I want than to have one query with all the 10 trans (2 fields) in one
field in one record for each person

is access able to do this?
 
G

Guest

Post a sample of your data with table and field names and what you expect the
results to be.
 
J

John Spencer

Using Duane Hookom's Concatenate function the SQL might look like:
(Google for Duane Hookom Concatenate to find the VBA function)


With Duane's Concatenate function

SELECT Persons.PersonID,
Concatenate("SELECT TOP 10 P.TransDate FROM TransDateTable as P
WHERE P.PersonID =" & Persons.PersonID & "ORDER BY p.TransDate
Desc",";") as AllDates
FROM Persons




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Z

zionsaal

Using Duane Hookom's Concatenate function the SQL might look like:
(Google for Duane Hookom Concatenate to find the VBA function)

With Duane's Concatenate function

SELECT Persons.PersonID,
Concatenate("SELECT TOP 10 P.TransDate FROM TransDateTable as P
WHERE P.PersonID =" & Persons.PersonID & "ORDER BY p.TransDate
Desc",";") as AllDates
FROM Persons

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================







- Show quoted text -
Click to expand...

give me please the code
thanks
 
J

John Spencer

It will be slow.

The only thing that might help is to make sure you have indexes on
transdateTable.TransDate and TransdateTable.PersonID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query out transactions Continued.. 4
Group By error message 2
Top N Records for a Group by 1
Help Me Fix This Querry 1
Stuck with GROUP BY query 3
Top X from joined table 1
...cannot use a calculated field in a Where clause... 2
top N per employee 1

Top