Reveal recent PriceChanges by date comparison?

[jason]

Access 2000:

If a listing/product has the potential for multiple price changes which are
stored in tbl ListingPriceChanges, how do I generate a daily report to pull
only the
records from the tbl Listings Table and the allied [ChangeDates] from the
ListingsPriceChanges Table which were added in the last 48 hours.

Listings Table
ListingsID Name InsertionDate OriginalPrice
23 Waypoint 05/23/2003 $100,000

ListingsPriceChanges
ListingsID NewPrice ChangeDate InsertionDate
23 $60.000 5/28/2003 5/30/2003
23 $50,000 5/29/2003 5/31/2003

....In other words, I wish to capture recent Price Reductions for a yacht but
NOT all reductions - just the ones that are relevant for a recent time
frame.
From this, I will send out an bulk email.

I am getting head twised but the answer probably lies in some kind of a date
comparsion. I need to:

A. Find out if the boat has any price reductsion
B. Track the most recent price reduction
C. Make sure this price reduction is not stale but fits into a 'recent' 48
hour period

Maybe there is a better way of conceptaulizing this. I would appreciate
help.

Jason

 

[Michel Walsh]

Hi,



A.
SELECT Name
FROM Listings AS l INNER JOIN ListingsPriceChanges As p
ON l.ListingsID=p.ListingsID

An inner join, where there is no duplicated values in at least ONE
of the tables, behaves like a INTERSECTION. Here, Listings has no dup under
its field ListingsID, so we have the records values under ListingsID in
Listings also in ListingsPriceChanges with the inner join.


C.

SELECT Name
FROM Listings AS l INNER JOIN ListingsPriceChanges As p
ON l.ListingsID=p.ListingsID
WHERE p.InsertionDate >= Date( ) - 2


since 48 hours = 2 days, we just have to add that criteria to the
previous query.

B.

Add field InsertionDate in the previous Selected list of fields
and applied, to that saved query, any then apply one of the four techniques
described at http://www.mvps.org/access/queries/qry0020.htm on that saved
query.

Hoping it may help,
Vanderghast, Access MVP