[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Question on Max Date query using Access 2007

Posted on 2008-11-11
1
Medium Priority
?
1,703 Views
Last Modified: 2012-05-05
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
Comment
Question by:Phereklos
1 Comment
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22930684
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question