INNER JOIN and min value

[dubr12]

Hi,
I have two tables. In one are apartment data - "apart" table(site for renting apartments). In other are prices - "prices" table. There are more than one price for one apartment (from 01.8.2012- 31.08.2012 is 50 Eur, 01.09.2012 - 31.12.2012 is 45,...). Two tables are connected with rentalid field. I want to get only lowest price. I have query that gives me all prices for one apartment. I get for example apartment1 with price 50Eur, then again apartment1 with price 45Eur. I need only to show price 45Eur. Here is the code:

"SELECT apart.apname, apart.rentalid, apart.status, apart.statpicture, apart.peoplebasic, apart.sdescription, apart.picture1, apart.bedrooms, apart.maxpeople, apart.arem2, prices.grosspr from apart INNER JOIN prices ON apart.rentalid = prices.rentalid WHERE prices.pricedateto > Date() ORDER BY apart.apname"

Please help

Thanks

 

[dhookom]

Try

SELECT apart.apname, apart.rentalid, apart.status, apart.statpicture, 
apart.peoplebasic, apart.sdescription, apart.picture1, apart.bedrooms,
apart.maxpeople, apart.arem2, Min(prices.grosspr) as MinPrice
FROM apart INNER JOIN prices ON apart.rentalid = prices.rentalid 
WHERE prices.pricedateto > Date() 
GROUP BY apart.apname, apart.rentalid, apart.status, apart.statpicture, 
apart.peoplebasic, apart.sdescription, apart.picture1, apart.bedrooms,
apart.maxpeople, apart.arem2
ORDER BY apart.apname

Duane
Hook'D on Access
MS Access MVP

 

[dubr12]

Thanks you for your answer, but it doesnt work. When I click search button nothing happend.

 

[dhookom]

Are you suggesting you didn't get an error message? Did you at least see column headings in a datasheet view?

Are any of your fields memo data type?

Duane
Hook'D on Access
MS Access MVP