• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1708
  • Last Modified:

Question on Max Date query using Access 2007

I am having trouble understanding how to use Max to pull the last date from a table.  I have a select query put together in design view that pulls from a couple of different tables.  I'll paste the SQL below.  Where do I use Max in this statement to pull the last date for tblInspection,InspectionDate?

SELECT tblFund.FundSort, tblFund.FundLegalName, tblFund.FundTypeID, tblProperty.PropNameLegal, qryUnitTotals.ResidentialUnits, qryUnitTotals.NonResidentialUnits, tblInspection.InspectionDate
FROM ((tblProperty INNER JOIN tblInspection ON tblProperty.PropertyID = tblInspection.PropertyID) INNER JOIN (tblFund INNER JOIN tblProperties_Funds ON tblFund.FundID = tblProperties_Funds.FundID) ON tblProperty.PropertyID = tblProperties_Funds.PropID) INNER JOIN qryUnitTotals ON tblProperty.PropertyID = qryUnitTotals.PropertyID;
SELECT tblFund.FundSort, tblFund.FundLegalName, tblFund.FundTypeID, tblProperty.PropNameLegal, qryUnitTotals.ResidentialUnits, qryUnitTotals.NonResidentialUnits, tblInspection.InspectionDate
FROM ((tblProperty INNER JOIN tblInspection ON tblProperty.PropertyID = tblInspection.PropertyID) INNER JOIN (tblFund INNER JOIN tblProperties_Funds ON tblFund.FundID = tblProperties_Funds.FundID) ON tblProperty.PropertyID = tblProperties_Funds.PropID) INNER JOIN qryUnitTotals ON tblProperty.PropertyID = qryUnitTotals.PropertyID;

Open in new window

0
Phereklos
Asked:
Phereklos
1 Solution
 
Patrick MatthewsCommented:
SELECT tblFund.FundSort, tblFund.FundLegalName, tblFund.FundTypeID, tblProperty.PropNameLegal,
    qryUnitTotals.ResidentialUnits, qryUnitTotals.NonResidentialUnits, Max(tblInspection.InspectionDate) AS LastDate
FROM ((tblProperty INNER JOIN tblInspection ON tblProperty.PropertyID = tblInspection.PropertyID) INNER JOIN
    (tblFund INNER JOIN tblProperties_Funds ON tblFund.FundID = tblProperties_Funds.FundID) ON tblProperty.PropertyID = tblProperties_Funds.PropID) INNER JOIN
    qryUnitTotals ON tblProperty.PropertyID = qryUnitTotals.PropertyID
GROUP BY tblFund.FundSort, tblFund.FundLegalName, tblFund.FundTypeID, tblProperty.PropNameLegal,
    qryUnitTotals.ResidentialUnits, qryUnitTotals.NonResidentialUnits;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now