subquery for sequence

[TrollBro]

I'm not sure of the best way to phrase the questions, but I'll do my best.

To simplify, lets say I have a db with 50 tbls, some containing transaction histories for a customer's product orders and options. I have a qry that produces one record per order. so far so good. What I need to figure out is, for a given order with 0 to n product options (say tblOptions.ID, tblOptions.Name), how to distinguish the 1st, 2nd, 3rd and 4th option even if there are say 8 or 12 or any n options. Min and max work fine at the extremes, but I need to have the 1st 4 fields only in the resulting record to denote the options in sequence. I did figure something out that gave me the record before the max, but for this problem I realy need the 1st through 4th, not the 4th through the 1st, since there could in theory be any number of options from 0 to whatever. The query I've built so far makes great use of subqueries and nexted subqueries. I recently discovered them and was intrigued, likely not that efficent yet. Anyway, I added one field with a number of nested subqueries to get the 2nd option, and it was soooooo slow.

Can anyone point me in the right direction for an sql subquery for sequencing in separate fields?

i.e. I have
Cust1, prod1, option1
Cust1, prod1, option2
Cust1, prod1, option3
Cust1, prod1, option4
Cust2, prod1
Cust3, prod1, option1
Cust3, prod1, option2
Cust4, prod1, option1


and I want:
Cust1, prod1, option1, option2, option3, option4
Cust2, prod1
Cust3, prod1, option1, option2
Cust4, prod1, option1

Thanks!

 

[dhookom]

Did you find the two (maybe more) solutions in the the FAQs of this forum? faq701-3499 faq701-4233

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TrollBro]

Thanks dhookom

I could be wrong, but I'm thinking that a select statement in a query that hits a function that opens dao recordsets to get these values for thousands of records will be quite slow, and I also was hoping to find a subquery solution that wouldn't require adding additional temp tables or secondary queries. I didn't see how these faqs would give me the correct order, but I would assume I could figure that part out in a dao recordset type solution. At least this has fall-back plan B potential.

Many thanks

 

[dhookom]

These solutions would be slow. I am not aware of a faster alternative. The temp table might be the quickest but you don't want that.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]