Max Date Query Part 2

[stephen.belyea]

I want to thank both John Vinson and John Spencer for help on my
previous "Max Date" problem. The final solution was to use the
following SQL:

qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;

Then join *this* query to tblProperty:

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;

Now, however, I have a new problem. Per the revisions done by my boss,
I now need to add a couple of fields into the query (which need to
appear on the final report). These fields all reside in tblInspection.

InspRequestedOn
InspRequiredOn
InspResponseDueBy
InspCompletedOn
InspRepairsBy
InspRepairsCompletedOn
InspComplete?

(the first six are Date/Time in Short Date format, InspRecordComplete
is a Yes/No).

tblInspection is related to tblProperty by the field AutoIDProperty.

I attempted to do something similar to what worked last time, by doing
a join between the tblProperty and tblInspection, including all
records from tblProperty and only those from tblInspection where the
joined fields were equal. This is what my SQL looks like:

SELECT tblInspection.AutoIDProperty, tblProperty.PropSiteName,
Max(tblInspection.InspRequestedOn) AS MaxOfInspRequestedOn,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM tblProperty LEFT JOIN tblInspection ON tblProperty.AutoIDProperty
= tblInspection.AutoIDProperty
GROUP BY tblInspection.AutoIDProperty, tblProperty.PropSiteName,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
ORDER BY Max(tblInspection.InspRequestedOn);

When I run this query, however, I get an error that says "no current
record". When I run this query without doing a join, I only get the
records that have dates recorded.

What I want to do is the same as last time - have all properties
listed, even if they have not been inspected yet. I'd really
appreciate any help you guys/gals could provide - I've been trying to
figure this out for a few hours now, and am just getting more
confused.

Thanks!

 

[Guest]

I changed your first query to use AutoIDProperty instead of PropSiteName.
qryInspectionLatest ---
SELECT tblInspection.AutoIDProperty, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY tblInspection.AutoIDProperty;

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate, tblInspection.InspRequestedOn,
tblInspection.InspRequiredOn, tblInspection.InspResponseDueBy,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM (tblProperty INNER JOIN qryInspectionLatest ON
tblProperty.AutoIDProperty = qryInspectionLatest.AutoIDProperty) INNER JOIN
tblInspection ON (qryInspectionLatest.LatestInspDate =
tblInspection.InspDate) AND (qryInspectionLatest.AutoIDProperty =
tblInspection.AutoIDProperty);

--
KARL DEWEY
Build a little - Test a little

I want to thank both John Vinson and John Spencer for help on my
previous "Max Date" problem. The final solution was to use the
following SQL:

qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;

Then join *this* query to tblProperty:

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;

Now, however, I have a new problem. Per the revisions done by my boss,
I now need to add a couple of fields into the query (which need to
appear on the final report). These fields all reside in tblInspection.

InspRequestedOn
InspRequiredOn
InspResponseDueBy
InspCompletedOn
InspRepairsBy
InspRepairsCompletedOn
InspComplete?

(the first six are Date/Time in Short Date format, InspRecordComplete
is a Yes/No).

tblInspection is related to tblProperty by the field AutoIDProperty.

I attempted to do something similar to what worked last time, by doing
a join between the tblProperty and tblInspection, including all
records from tblProperty and only those from tblInspection where the
joined fields were equal. This is what my SQL looks like:

SELECT tblInspection.AutoIDProperty, tblProperty.PropSiteName,
Max(tblInspection.InspRequestedOn) AS MaxOfInspRequestedOn,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM tblProperty LEFT JOIN tblInspection ON tblProperty.AutoIDProperty
= tblInspection.AutoIDProperty
GROUP BY tblInspection.AutoIDProperty, tblProperty.PropSiteName,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
ORDER BY Max(tblInspection.InspRequestedOn);

When I run this query, however, I get an error that says "no current
record". When I run this query without doing a join, I only get the
records that have dates recorded.

What I want to do is the same as last time - have all properties
listed, even if they have not been inspected yet. I'd really
appreciate any help you guys/gals could provide - I've been trying to
figure this out for a few hours now, and am just getting more
confused.

Thanks!

 

[stephen.belyea]

I changed your first query to use AutoIDProperty instead of PropSiteName.
qryInspectionLatest ---
SELECT tblInspection.AutoIDProperty, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY tblInspection.AutoIDProperty;

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate, tblInspection.InspRequestedOn,
tblInspection.InspRequiredOn, tblInspection.InspResponseDueBy,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM (tblProperty INNER JOIN qryInspectionLatest ON
tblProperty.AutoIDProperty = qryInspectionLatest.AutoIDProperty) INNER JOIN
tblInspection ON (qryInspectionLatest.LatestInspDate =
tblInspection.InspDate) AND (qryInspectionLatest.AutoIDProperty =
tblInspection.AutoIDProperty);

--
KARL DEWEY
Build a little - Test a little

I want to thank both John Vinson and John Spencer for help on my
previous "Max Date" problem. The final solution was to use the
following SQL:

qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;

Then join *this* query to tblProperty:

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;

Now, however, I have a new problem. Per the revisions done by my boss,
I now need to add a couple of fields into the query (which need to
appear on the final report). These fields all reside in tblInspection.

(the first six are Date/Time in Short Date format, InspRecordComplete
is a Yes/No).

tblInspection is related to tblProperty by the field AutoIDProperty.

I attempted to do something similar to what worked last time, by doing
a join between the tblProperty and tblInspection, including all
records from tblProperty and only those from tblInspection where the
joined fields were equal. This is what my SQL looks like:

SELECT tblInspection.AutoIDProperty, tblProperty.PropSiteName,
Max(tblInspection.InspRequestedOn) AS MaxOfInspRequestedOn,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM tblProperty LEFT JOIN tblInspection ON tblProperty.AutoIDProperty
= tblInspection.AutoIDProperty
GROUP BY tblInspection.AutoIDProperty, tblProperty.PropSiteName,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
ORDER BY Max(tblInspection.InspRequestedOn);

When I run this query, however, I get an error that says "no current
record". When I run this query without doing a join, I only get the
records that have dates recorded.

What I want to do is the same as last time - have all properties
listed, even if they have not been inspected yet. I'd really
appreciate any help you guys/gals could provide - I've been trying to
figure this out for a few hours now, and am just getting more
confused.

Thanks!

Is there a way for me to simply cut and past this code into a query to
see if it works? I attempted creating a query in design view, and then
cut and pasted the SQL, but it said there were characters after an SQL
statement.

 

[Guest]

You have to remove any extra hard returns that pasting and posting may
insert. A way to test is to drag the window size bigger and smaller to check
wrap. The only place a hard return should be is preceeding the following
words --
FROM
GROUP BY

--
KARL DEWEY
Build a little - Test a little

I changed your first query to use AutoIDProperty instead of PropSiteName.
qryInspectionLatest ---
SELECT tblInspection.AutoIDProperty, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY tblInspection.AutoIDProperty;

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate, tblInspection.InspRequestedOn,
tblInspection.InspRequiredOn, tblInspection.InspResponseDueBy,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM (tblProperty INNER JOIN qryInspectionLatest ON
tblProperty.AutoIDProperty = qryInspectionLatest.AutoIDProperty) INNER JOIN
tblInspection ON (qryInspectionLatest.LatestInspDate =
tblInspection.InspDate) AND (qryInspectionLatest.AutoIDProperty =
tblInspection.AutoIDProperty);

--
KARL DEWEY
Build a little - Test a little

I want to thank both John Vinson and John Spencer for help on my
previous "Max Date" problem. The final solution was to use the
following SQL:

qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;

Then join *this* query to tblProperty:

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;

Now, however, I have a new problem. Per the revisions done by my boss,
I now need to add a couple of fields into the query (which need to
appear on the final report). These fields all reside in tblInspection.

(the first six are Date/Time in Short Date format, InspRecordComplete
is a Yes/No).

tblInspection is related to tblProperty by the field AutoIDProperty.

I attempted to do something similar to what worked last time, by doing
a join between the tblProperty and tblInspection, including all
records from tblProperty and only those from tblInspection where the
joined fields were equal. This is what my SQL looks like:

SELECT tblInspection.AutoIDProperty, tblProperty.PropSiteName,
Max(tblInspection.InspRequestedOn) AS MaxOfInspRequestedOn,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM tblProperty LEFT JOIN tblInspection ON tblProperty.AutoIDProperty
= tblInspection.AutoIDProperty
GROUP BY tblInspection.AutoIDProperty, tblProperty.PropSiteName,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
ORDER BY Max(tblInspection.InspRequestedOn);

When I run this query, however, I get an error that says "no current
record". When I run this query without doing a join, I only get the
records that have dates recorded.

What I want to do is the same as last time - have all properties
listed, even if they have not been inspected yet. I'd really
appreciate any help you guys/gals could provide - I've been trying to
figure this out for a few hours now, and am just getting more
confused.

Thanks!

Is there a way for me to simply cut and past this code into a query to
see if it works? I attempted creating a query in design view, and then
cut and pasted the SQL, but it said there were characters after an SQL
statement.

 

[stephen.belyea]

You have to remove any extra hard returns that pasting and posting may
insert. A way to test is to drag the window size bigger and smaller to check
wrap. The only place a hard return should be is preceeding the following
words --
FROM
GROUP BY

--
KARL DEWEY
Build a little - Test a little

I changed your first query to use AutoIDProperty instead of PropSiteName.
qryInspectionLatest ---
SELECT tblInspection.AutoIDProperty, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY tblInspection.AutoIDProperty;
SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate, tblInspection.InspRequestedOn,
tblInspection.InspRequiredOn, tblInspection.InspResponseDueBy,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM (tblProperty INNER JOIN qryInspectionLatest ON
tblProperty.AutoIDProperty = qryInspectionLatest.AutoIDProperty) INNER JOIN
tblInspection ON (qryInspectionLatest.LatestInspDate =
tblInspection.InspDate) AND (qryInspectionLatest.AutoIDProperty =
tblInspection.AutoIDProperty);
--
KARL DEWEY
Build a little - Test a little
:
I want to thank both John Vinson and John Spencer for help on my
previous "Max Date" problem. The final solution was to use the
following SQL:
qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;
Then join *this* query to tblProperty:
SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;
Now, however, I have a new problem. Per the revisions done by my boss,
I now need to add a couple of fields into the query (which need to
appear on the final report). These fields all reside in tblInspection.
InspRequestedOn
InspRequiredOn
InspResponseDueBy
InspCompletedOn
InspRepairsBy
InspRepairsCompletedOn
InspComplete?
(the first six are Date/Time in Short Date format, InspRecordComplete
is a Yes/No).
tblInspection is related to tblProperty by the field AutoIDProperty.
I attempted to do something similar to what worked last time, by doing
a join between the tblProperty and tblInspection, including all
records from tblProperty and only those from tblInspection where the
joined fields were equal. This is what my SQL looks like:
SELECT tblInspection.AutoIDProperty, tblProperty.PropSiteName,
Max(tblInspection.InspRequestedOn) AS MaxOfInspRequestedOn,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
FROM tblProperty LEFT JOIN tblInspection ON tblProperty.AutoIDProperty
= tblInspection.AutoIDProperty
GROUP BY tblInspection.AutoIDProperty, tblProperty.PropSiteName,
tblInspection.InspResponseDueBy, tblInspection.InspRequiredOn,
tblInspection.InspCompletedOn, tblInspection.InspRepairsBy,
tblInspection.InspRepairsCompletedOn, tblInspection.[InspComplete?]
ORDER BY Max(tblInspection.InspRequestedOn);
When I run this query, however, I get an error that says "no current
record". When I run this query without doing a join, I only get the
records that have dates recorded.
What I want to do is the same as last time - have all properties
listed, even if they have not been inspected yet. I'd really
appreciate any help you guys/gals could provide - I've been trying to
figure this out for a few hours now, and am just getting more
confused.
Thanks!

Is there a way for me to simply cut and past this code into a query to
see if it works? I attempted creating a query in design view, and then
cut and pasted the SQL, but it said there were characters after an SQL
statement.

Thanks for all of your help Karl.

I finally figured out why I kept getting the "No records" error - for
some reason, my field tblInspection.InspComplete? was the field
causing the error - once I dropped that field from my query,
everything worked perfectly. I changed InspComplete into a Short Date
format field, and now everything works fine.

Thanks for your help!