SQL Query help in Access 2000

February 13, 2011 10:59 AM

I have a MS Access 2000 table with approx 300,000 records. I need help with constructing a query.

I have all this information in a single table.

The table has, among others, these fields.

Item : Price: First sold date : Last sold date
Beef    $1.99  28/12/2010   31/12/2010
Beef    $1.89  27/12/2010   30/12/2010
Beef    $1.79  27/12/2010   29/12/2010
(maybe many more records with same item at different prices & dates)
Milk     $0.59  28/11/2010   28/11/2010
Milk     $0.55  27/11/2010   28/11/2010
(maybe many more records with same item at different prices & dates)

(many more records with different items sold at various prices/dates)

As time (sold dates) passes one would expect that the price would increase, but the trend is not constantly down - also on the same day things can have been sold at different prices.

Using the 'beef' @ $1.99 above as an example. On 28/12/10 it was sold at $1.99, but on the 29th it went down to $1.79 and then on 30th it was sold at $1.89 before finally settling back at $1.99 on the 31st.

I want is a query to give me this:

Item : Price  : later sold at 
Beef : $1.99  : $1.79, $1.89
Beef : $1.89  : $1.79
Beef : $1.79  : none
Milk : $0.59 : $0.55

I'm not overly impressed with Access 2000 so far, nor am I at all familiar with it. But the main problem is constructing this query, I just can't get my head around how to do it.

posted by selton to computers & internet (3 answers total)

So for each tuple (item,date), you want the price on that date, followed by the list of all prices for that item on all previous dates?

Which list can be arbitrarily long?

Not easily done in Access as a single query. Doable with MySQLs group_concat, or the XML (yes, really) functions in Oracle or MSSQL, but not really useful to do: what do you do with an arbitrary length list? (I presume you want to graph it? But why not just graph date over price by item?)
posted by orthogonality at 11:12 AM on February 13, 2011

I think part of the problem is the table structure itself. I'm not sure I understand how beef can be sold at two different prices in an overlapping set of dates.
posted by Pants! at 2:15 PM on February 13, 2011

Yeah, it's not really doable in access in a single query (you'd need to have a subquery for each possible item in "later sold at")

However, here's how I would get it done:

You write your query like so:

SELECT Item, Price, also_get(Item, Price, [First Sold Date]) as [Later Sold at]
From my_table

Then you fire up VBA and write a nice little function (don't forget to include a reference to VBA Scripting Runtime [in Tools -> References] to get the nifty dictionary class):

function also_get(item as String, price as Double, sold_date as Date) as String
    Dim other_prices as New Dictionary
    With CurrentDB.OpenRecordset("SELECT * FROM my_table WHERE item = """ & Item & """ and price <> " & price & and date > something")
      Do While not .EOF
        other_prices.add .AbsolutePosition, !Price
      loop
    end with

    also_get = Join(other_prices.items, ", ")
end function

I didn't quite understand what the criteria for the prices to show is, but you can change the query used in the VBA function accordingly.

Good luck!

(Also, that's a pretty unwieldy schema to use for your table... If at all possible, use id, item_name, price, date_sold, have a record for each transaction, and then get the table you have via a GROUP BY query and MIN() and MAX().)
posted by Mons Veneris at 2:36 PM on February 13, 2011