Can access analyze orders with a SKU list?

[shubhamgandhi]

I have a spreadsheet of a few thousand lines of order data of order # and SKUs Ordered which I imported into Access. Now I need analyze the data to figure out which two SKUs are most commonly sold together (i.e. same order).

Is there a way I can automatically do this? Or do I have to write some VBA?

I have tried setting up relationships and making queries and reports based on the data but I just cant figure it out.

I know this can be done VBA but I need a fast and automated way of doing this, after all, this is the type of thing access is made for, right?

Heres a screenshot of what my data looks like: The first column is Order, second column is SKU ordered.





Any help is appreciated! Thank you!

[rpeare]

Build your SELECT query that shows all of the data you want, in your case select a specific order number, SKU and count(sku) this will give you three columns, sort the data by the count(SKU) column in descending order. go to the SQL view of the query and after the SELECT statement add TOP 2 so your statement should start with SELECT TOP 2. NOTE, unless you limit it access will show all ties. So for instance in your data there are no duplicate SKU numbers so you would have a bunch of records with 1 total SKU type.

EDIT: it occurs to me that you mean you want a list of order numbers and the top 2 items for each order in a single query which is a different type of query but you did say it was for a single order you were looking.

[shubhamgandhi]

I edited the OP to clarify.
I'm looking to figure out which 2 items are found together most commonly in an order.
So at the end of the month you can say "wow, looks like its a trend that people buy these two things together"

[rpeare]

ah well then I assume you have a date field. So you would just pick all the data between two dates in a summary (aggregate) query, make sure the date field is set to WHERE not GROUP BY, have one field that is your SKU, one field that is your Count(SKU) sort by the count field in descending order and proceed as I said before using the TOP 2 statement on your select SQL