Select a single record from a recordset??

[braedon]

I am building a client referral tool. When a potential client makes a purchase inquiry, I want to send the inquiry request to a product dealer in the city specified by the client. I have a table with a list of dealers sorted by city.

One complication is that I need the referral tool to select only a single dealer from a list of potential dealers in that city.

My question is this: I can query the dealers with a WHERE statement to group my results on a specific city. This will give a number of results in my recordset. How do I select only a specific record from a recorset?

For example: I want to send the purchase inquiry to the first dealer in the city specified in the inquiry (dealers are sorted alphabetically). This will obviously be the first record in the recorset. The next time I want to specify that the inquiry be sent to the second record in the recordset...and so on... and so on...

Thanks

 

[barbarul2001]

- general dbms solving: use a cursor .
web/sql developer

 

[braedon]

Thanks.

I am using Access as my database for this project, and I am web-enabling it with Cold Fusion.

After having read about cursors, I am not sure how to write the statement to do what I need it to do.

Cold Fusion passes and SQL statement using ODBC to access - but I am not sure how to write this SQL statement so that it will work in access.

Any suggestions or examples would be appreciated.