Complicated Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Complicated Query?

Author

Topic

matt.orme
Starting Member

23 Posts

Posted - 2008-11-24 : 16:49:58

I have a simple table that I need to run what seems to me to be a complicated query. Here's an example of the data:

Table Name: History

- Ticket (Text)- / - SKU (Text)-

212624 / 10891780
212624 / 10947210
212624 / 11019510
212624 / 10231260
212628 / 013770
212628 / 151200
212628 / 11019590
212628 / 11006830
212628 / 11006970
212632 / 10074980
212632 / 10268530
212632 / 10268540
212634 / 10952040
212636 / 12288
212636 / 61805810
212636 / 10483840
212636 / 10223150
212636 / 10843574
212636 / 11051290
212636 / 10313260
212636 / 10952744
212636 / 10359700

Ticket here represents a single transaction with a single customer, while SKU represents the items they purchased. I need to determine which items were purchased most frequently with each SKU - perhaps the top 10. Ideally, this would populate another table with the "Main" SKU acting as a unique identifier for the row and 10 subsequent fields with one SKU per container in descending order of occurrence from columns 2 through 11.

Doing this for a single SKU is easy, but getting everything squared away for each unique SKU stumps me. It seems like a For Each loop of some kind, but I am not good with any of that...

Any help would be appreciated. Please let me know what vital info I left out, as I always seem to forget something.

Thank you

*EDIT* - Don't know if this matters, but a SKU only appears once on any given Ticket.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 16:53:02

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115103

E 12°55'05.63"
N 56°04'39.26"

matt.orme
Starting Member

23 Posts

Posted - 2008-11-24 : 18:31:43

Yes. Totally sorry. I think that I flubbed up when I put it in the "New to SQL Server" section.

matt.orme
Starting Member

23 Posts

Posted - 2008-11-26 : 18:37:45

I got some replies to this issue in my original post, but I believe that they will only work for SQL Server. I am limited to getting this job done in Access 2003. Any help would be greatly appreciated.

Happy Thanksgiving

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources