help me with this access sql please?

[Papachumba]

Hi guys, i have a little problem and was wandering if anyone can help.
I just need my query modified a little bit to allow some extra values
in there...

Tables:

offers Table

OfferID (primary)
OStartValidity (date)
OValidity (date)
OComments
LocationID
some insignificant Values

hotels Table

HotelID (primary)
LocationID
HName
Some other insignificant values

locations Table

LocationID (primary)
LDescription
LArea

prices Table

PriceID (primary)
OfferID
Price
DepartFrom (date)
DepartTo (date)

Relationships:
locations (one to many LocationID) hotels
locations (one to many LocationID) offers
offers (one to many OfferID) prices

My goal is to retrieve a set of rows from 'offers' table where certain
criteria meets, and then for each row retrieved pull in the Minimum
price from the 'prices' table (matching the same OfferID in 'offers'
obviously). This is my problem - i want to be able to pull in
DepartFrom and DepartTo fields from 'prices' table as well and i dont
know how.

This is the current query i have been using to retrieve just the
Min(price) from prices (i have simplified it a bit to enable you to
comprehend it easier, you will find some extra tables in there not
mentioned above, just ignore them - the ones above are the most
important ones). This has worked fine for me, but as i said i cannot
just add prices.DepartFrom and prices.DepartTo to it as it doesnt seem
to work.

SELECT DISTINCT offers.OfferID, offers.OStartValidity,
offers.OValidity, Min(prices.Price) AS strPrice

FROM locations INNER JOIN ((offers INNER JOIN (hotels INNER JOIN
accomodation ON hotels.HotelID = accomodation.HotelID) ON
offers.OfferID = accomodation.OfferID) INNER JOIN prices ON
offers.OfferID = prices.OfferID) ON (locations.LocationID =
hotels.LocationID)

WHERE offers.OStartValidity<=#2006/3/21# AND
offers.OValidity>=#2006/3/21# AND prices.DepartTo>=#2006/3/21# AND
(locations.LocationID='Dubai Beaches')

GROUP BY offers.OfferID, offers.OStartValidity, offers.OValidity
ORDER BY MIN(prices.Price);

I think i might be needing to use a subquery but unfortunately that is
too complicated for my little brain, i need help!

 

[Guest]

Why do you have your offers table related to the locations table? Shouldn't
an offer be related to a hotel, and the hotel related to a location? You
don't seem to have a hotel related to anything except a location.

 

[Papachumba]

do not worry about the relations between those 2, the locatications ->
offers relation is not being used. There is another table called
accommodation which links the two you mentioned, so

locations (LocationID) -> hotels (HotelID) -> accommodation (OfferID)
-> offers

this works quite well for all my needs and purposes, it is those darn
DepartFrom & DepartTo from prices that is giving me grief...