NEXT

[Bibi]

I have a reservation db and have just now been able to create a query which
shows each property available on a specific date (thanks to this group). I
have created a form to input the target date. This form runs the query and I
have attached a subdata sheet that shows for each the property, by property,
all leases after the target date. I really don't need to see all future
leases for the property, just the first one with a start date after the
target date. I have drawn a complete blank on how to do this. As always all
help greatly appreciated.

 

[Michel Walsh]

Something like:



SELECT *
FROM myTable AS a
WHERE a.productID = whichProductIDParameter
AND
dateStamp = (SELECT MIN(b.dateStamp)
FROM myTable AS b
WHERE b.productID=
whichProductIDParameter
AND b.dateStamp >
afterWhichDateParameter )





Vanderghast, Access MVP

 

[Bibi]

Oh, my.....I'm lost still. I tried working in my fields but couldn't figure
out which went where......I think myTable is tblLease....but is productID the
lease number?
this is how I interpreted your response but clealy I missed something....

SELECT tblLeases.[Lease num], tblLeases.Property, tblLeases.Arrive,
tblLeases.Depart
FROM tblLeases AS a
WHERE (((tblLeases.Arrive)>Date())) AND
WHERE [Lease num] = which [lease num] Parameter
AND
arrive = (SELECT MIN(arrive)
FROM tblleases AS b
WHERE [lease num]=
which[lease num]Parameter
AND arrive >
afterWhichDateParameter )

If you have the time to look at this I would greatly appreciate it.
Bibi

Something like:



SELECT *
FROM myTable AS a
WHERE a.productID = whichProductIDParameter
AND
dateStamp = (SELECT MIN(b.dateStamp)
FROM myTable AS b
WHERE b.productID=
whichProductIDParameter
AND b.dateStamp >
afterWhichDateParameter )





Vanderghast, Access MVP

 

[Michel Walsh]

I assumed your table was about multiple products, probably in your case it
is about multiple properties?




[Lease number], Property, Arrive ' fields
1009 Chocolate 2007.01.01
1010 Chocolate 2007.06.06
1011 Chocolate 2007.12.12
....
2010 Oil 2007.09.09
2011 Oil 2007.12.12 ' data sample





SELECT a.*
FROM myTable As a
WHERE a.property = 'Chocolate'
AND a.arrive=(SELECT MIN(b.arrive)
FROM myTable As b
WHERE b.property='Chocolate'
AND b.arrive > Date())



will probably return the lease number 1011 (since this is the next arrival
of chocolate).




Vanderghast, Access MVP