How to Pull the First Date and Last Date when payments were made.

[c02homer]

Best wishes for a safe and prosperous New Year to everyone!

Now, question:

I have a database of hotels and dates on which certain payments were
made. I need some very simple information out of this database: I
need to pull the first date a payment was made and the last date a
payment was made. Can it be done in a query? I don't know VB or VBA
so I'm pretty limited. (But I'll be taking a VB college course this
spring! Yeah!)

Thanks for any help!

 

[Guest]

I assume you want the max and min based on something like account number?

Here is how to get the max based on account number:

SELECT tblAccounts.account, Max(tblAccounts.DateOfPayment) AS
MaxOfDateOfPayment
FROM tblAccounts
GROUP BY tblAccounts.account;

To get the min change Max to Min.

-Steve Huff
http://www.huffs.us

 

[Allen Browne]

Use a subquery to get this info.

This example assumes you have a tblBooking with BookingID as primary key,
and a tblPayment with BookingID as foreign key so that one booking can have
many payments:

SELECT BookingID,
( SELECT Min(PaymentDate) FROM tblPayment
WHERE tblPayment.BookingID = tblBooking.BookingID )
AS FirstPaymentDate,
( SELECT Max(PaymentDate) FROM tblPayment
WHERE tblPayment.BookingID = tblBooking.BookingID )
AS LastPaymentDate
FROM tblBooking;

For more information about subqueries, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066