By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Query Generation - repeating previous value

P: n/a
Hi all,

I can't tell if I'm just being a spaccer, or whether this is tricky.
Here goes.

(Please note - this is a dummy example, for ease of explaining.)

So, I have a table with three fields:

ID
Date
Value

ID and Date together are the primary key. Essentially what it's for is
this: ID is, say, a product (oranges, apples, pears, grapes). Value is
the price of said product. What I want is the table to contain
information just when the price changes. So there's an entry for each
product at the start of time, then just new records when the price
changes.

Then, I want to have a query/report/printable data showing me the
price of each product over time, with it filling in the blanks where
the price has stayed the same.
So, we'd have something like this:

Table Data
ID........Date.........Value
Oranges...01/01/2004...30
Oranges...01/02/2004...32
Oranges...01/04/2004...35
Apples....01/01/2004...23
Apples....01/03/2004...24
Apples....01/04/2004...25
And I want to turn that into

Query Output
Date......01/01/2004...01/02/2004...01/03/2004...01/04/2004
Oranges...30...........32...........32...........3 5
Apples....23...........23...........24...........2 5
So, what do I need to do in terms of queries, guys? I'm using Access
97, if it makes a huge amount of difference.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Uncle Mike" <bi*********@ntlworld.com> wrote in message
news:9a**************************@posting.google.c om...
Hi all,

I can't tell if I'm just being a spaccer, or whether this is tricky.
Here goes.

(Please note - this is a dummy example, for ease of explaining.)

So, I have a table with three fields:

ID
Date
Value

ID and Date together are the primary key. Essentially what it's for is
this: ID is, say, a product (oranges, apples, pears, grapes). Value is
the price of said product. What I want is the table to contain
information just when the price changes. So there's an entry for each
product at the start of time, then just new records when the price
changes.

Then, I want to have a query/report/printable data showing me the
price of each product over time, with it filling in the blanks where
the price has stayed the same.


Use a calendar table with all the dates you are ever likely to need and join
to this table iin your query.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.