Help with a (relatively simple?) query

[BonnieW via AccessMonster.com]

I know I'm missing something terribly obvious here, but I just can't put my
finger on it.
I have a table, with many linked tables, and I want to get data from that
table and its linked tables, but only for one record in the "main" table.
The record I'd like to see will *always* be the most recent one, and it has
an autonumber field. I *only* want to see that record; and it'll be feeding
a report. The report will have some user-entered information (once I get a
handle on how to do that), but I'd rather the user not have to enter this ID
number, even though they could if worst comes to worst.

The SQL I have is:
SELECT Max(tblConsEFFORT.ConsEffortID) AS MaxOfConsEffortID, tblConsEFFORT.
Activity, tblNatAreas.NatArea, tblManageUnits.ManageUnit, tblConsEFFORT.
AddlMgmtUnits, tblConsEFFORT.PeopleType, tablePeople.FirstName, tablePeople.
LastName, tblRevegetationNew.RevegType, tblRevegetationNew.PlantID,
tblTrailWorkNew.TrailWorkType, tblInvControlNew.Technique, tblInvControlNew.
TargetPlant, tblInvControlNew.HerbicideType, tblInvControlNew.[%Active],
tblInvControlNew.AmtHerbicide
FROM tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN (tblManageUnits INNER
JOIN tblConsEFFORT ON tblManageUnits.ManageUnitID = tblConsEFFORT.
ManageUnitID) ON tablePeople.PeopleID = tblConsEFFORT.PeopleID) LEFT JOIN
tblInvControlNew ON tblConsEFFORT.ConsEffortID = tblInvControlNew.
ConsEffortID) LEFT JOIN tblRevegetationNew ON tblConsEFFORT.ConsEffortID =
tblRevegetationNew.ConsEffortID) LEFT JOIN tblTrailWorkNew ON tblConsEFFORT.
ConsEffortID = tblTrailWorkNew.ConsEffortID) ON (tblNatAreas.NatAreaID =
tblManageUnits.NatAreaID) AND (tblNatAreas.NatAreaID = tblConsEFFORT.
NatAreaID)
HAVING (((tblConsEFFORT.PeopleType)<>"crew"));

This is, predictably, telling me that I can't do that, because I didn't
specify aggregate functions for everyone else.

I tried this:
SELECT Max([tblConsEFFORT]![ConsEffortID]) AS Expr1, tblConsEFFORT.Activity,
tblNatAreas.NatArea, tblManageUnits.ManageUnit, tblConsEFFORT.AddlMgmtUnits,
tblConsEFFORT.PeopleType, tablePeople.FirstName, tablePeople.LastName,
tblRevegetationNew.RevegType, tblRevegetationNew.PlantID, tblTrailWorkNew.
TrailWorkType, tblInvControlNew.Technique, tblInvControlNew.TargetPlant,
tblInvControlNew.HerbicideType, tblInvControlNew.[%Active], tblInvControlNew.
AmtHerbicide
FROM (tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN tblConsEFFORT ON
tablePeople.PeopleID = tblConsEFFORT.PeopleID) LEFT JOIN tblInvControlNew ON
tblConsEFFORT.ConsEffortID = tblInvControlNew.ConsEffortID) LEFT JOIN
tblRevegetationNew ON tblConsEFFORT.ConsEffortID = tblRevegetationNew.
ConsEffortID) LEFT JOIN tblTrailWorkNew ON tblConsEFFORT.ConsEffortID =
tblTrailWorkNew.ConsEffortID) ON tblNatAreas.NatAreaID = tblConsEFFORT.
NatAreaID) INNER JOIN tblManageUnits ON tblNatAreas.NatAreaID =
tblManageUnits.NatAreaID
WHERE (((tblConsEFFORT.PeopleType)<>"crew"))
ORDER BY Max([tblConsEFFORT]![ConsEffortID]) DESC;
Same error.

Is it something in how I have table relationships set up? I'm pretty much a
noob, and this is a handmedown table, so there could be a lot of things going
wrong behind-the-scenes, I suppose.

All of the fields are necessary for the report if they exist, but not all of
them will necessarily have a value- the tblReveg, tblInControl, tblTrailWork
stuff, specificially.

 

[Jerry Whittle]

How do you know which record in the main table is the most recent one? If you
say the autonumber field, go directly to jail and do not pass go! Autonumbers
are not guaranteed to be sequential and the next one could well be a negative
number!

Also you can't depend on the last record being the bottom one when you open
up the table. The next time you open the table, that record could be
somewhere else in the table.

What I use in these situations is a Date/Time field in the table with a
default of Now(). Unless you have more than one record being added in a
second, for example an append query or multiple users entering records, you
can tell the last record added by doing a Max on the date/time field. Of
course you'll need to somehow add dates to the existing records as a default
value only works on added records and not existing data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I know I'm missing something terribly obvious here, but I just can't put my
finger on it.
I have a table, with many linked tables, and I want to get data from that
table and its linked tables, but only for one record in the "main" table.
The record I'd like to see will *always* be the most recent one, and it has
an autonumber field. I *only* want to see that record; and it'll be feeding
a report. The report will have some user-entered information (once I get a
handle on how to do that), but I'd rather the user not have to enter this ID
number, even though they could if worst comes to worst.

The SQL I have is:
SELECT Max(tblConsEFFORT.ConsEffortID) AS MaxOfConsEffortID, tblConsEFFORT.
Activity, tblNatAreas.NatArea, tblManageUnits.ManageUnit, tblConsEFFORT.
AddlMgmtUnits, tblConsEFFORT.PeopleType, tablePeople.FirstName, tablePeople.
LastName, tblRevegetationNew.RevegType, tblRevegetationNew.PlantID,
tblTrailWorkNew.TrailWorkType, tblInvControlNew.Technique, tblInvControlNew.
TargetPlant, tblInvControlNew.HerbicideType, tblInvControlNew.[%Active],
tblInvControlNew.AmtHerbicide
FROM tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN (tblManageUnits INNER
JOIN tblConsEFFORT ON tblManageUnits.ManageUnitID = tblConsEFFORT.
ManageUnitID) ON tablePeople.PeopleID = tblConsEFFORT.PeopleID) LEFT JOIN
tblInvControlNew ON tblConsEFFORT.ConsEffortID = tblInvControlNew.
ConsEffortID) LEFT JOIN tblRevegetationNew ON tblConsEFFORT.ConsEffortID =
tblRevegetationNew.ConsEffortID) LEFT JOIN tblTrailWorkNew ON tblConsEFFORT.
ConsEffortID = tblTrailWorkNew.ConsEffortID) ON (tblNatAreas.NatAreaID =
tblManageUnits.NatAreaID) AND (tblNatAreas.NatAreaID = tblConsEFFORT.
NatAreaID)
HAVING (((tblConsEFFORT.PeopleType)<>"crew"));

This is, predictably, telling me that I can't do that, because I didn't
specify aggregate functions for everyone else.

I tried this:
SELECT Max([tblConsEFFORT]![ConsEffortID]) AS Expr1, tblConsEFFORT.Activity,
tblNatAreas.NatArea, tblManageUnits.ManageUnit, tblConsEFFORT.AddlMgmtUnits,
tblConsEFFORT.PeopleType, tablePeople.FirstName, tablePeople.LastName,
tblRevegetationNew.RevegType, tblRevegetationNew.PlantID, tblTrailWorkNew.
TrailWorkType, tblInvControlNew.Technique, tblInvControlNew.TargetPlant,
tblInvControlNew.HerbicideType, tblInvControlNew.[%Active], tblInvControlNew.
AmtHerbicide
FROM (tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN tblConsEFFORT ON
tablePeople.PeopleID = tblConsEFFORT.PeopleID) LEFT JOIN tblInvControlNew ON
tblConsEFFORT.ConsEffortID = tblInvControlNew.ConsEffortID) LEFT JOIN
tblRevegetationNew ON tblConsEFFORT.ConsEffortID = tblRevegetationNew.
ConsEffortID) LEFT JOIN tblTrailWorkNew ON tblConsEFFORT.ConsEffortID =
tblTrailWorkNew.ConsEffortID) ON tblNatAreas.NatAreaID = tblConsEFFORT.
NatAreaID) INNER JOIN tblManageUnits ON tblNatAreas.NatAreaID =
tblManageUnits.NatAreaID
WHERE (((tblConsEFFORT.PeopleType)<>"crew"))
ORDER BY Max([tblConsEFFORT]![ConsEffortID]) DESC;
Same error.

Is it something in how I have table relationships set up? I'm pretty much a
noob, and this is a handmedown table, so there could be a lot of things going
wrong behind-the-scenes, I suppose.

All of the fields are necessary for the report if they exist, but not all of
them will necessarily have a value- the tblReveg, tblInControl, tblTrailWork
stuff, specificially.

 

[BonnieW via AccessMonster.com]

All of the previous records do have date fields that record the date the data
was entered. This new function that I'm creating the query for, however,
would only be for use with future data, as it's a genuinely new feature for a
genuinely new process- they've no interest in doing this for past data. I
had been using the autonumber- I didn't realize it was quite such a liability.


So, presuming I add a field with a default value of =Now(), to tblConsEffort-
how would I structure that query?

Always happy to give you more info if you'd like. Thanks for your help.

-Bonnie

How do you know which record in the main table is the most recent one? If you
say the autonumber field, go directly to jail and do not pass go! Autonumbers
are not guaranteed to be sequential and the next one could well be a negative
number!

Also you can't depend on the last record being the bottom one when you open
up the table. The next time you open the table, that record could be
somewhere else in the table.

What I use in these situations is a Date/Time field in the table with a
default of Now(). Unless you have more than one record being added in a
second, for example an append query or multiple users entering records, you
can tell the last record added by doing a Max on the date/time field. Of
course you'll need to somehow add dates to the existing records as a default
value only works on added records and not existing data.

I know I'm missing something terribly obvious here, but I just can't put my
finger on it.

[quoted text clipped - 55 lines]

them will necessarily have a value- the tblReveg, tblInControl, tblTrailWork
stuff, specificially.

 

[John Spencer]

If you were to rely on the Autonumber or on the new date time field your
query could look like the following. Basically you are moving the Max into
the where clause

SELECT tblConsEFFORT.ConsEffortID
, tblConsEFFORT.Activity
, tblNatAreas.NatArea
, tblManageUnits.ManageUnit
, tblConsEFFORT.AddlMgmtUnits
, tblConsEFFORT.PeopleType
, tablePeople.FirstName
, tablePeople.LastName
, tblRevegetationNew.RevegType
, tblRevegetationNew.PlantID
, tblTrailWorkNew.TrailWorkType
, tblInvControlNew.Technique
, tblInvControlNew.TargetPlant
, tblInvControlNew.HerbicideType
, tblInvControlNew.[%Active]
, tblInvControlNew.AmtHerbicide
FROM tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN
(tblManageUnits INNER JOIN tblConsEFFORT
ON tblManageUnits.ManageUnitID = tblConsEFFORT.ManageUnitID)
ON tablePeople.PeopleID = tblConsEFFORT.PeopleID)
LEFT JOIN tblInvControlNew
ON tblConsEFFORT.ConsEffortID = tblInvControlNew.ConsEffortID)
LEFT JOIN tblRevegetationNew
ON tblConsEFFORT.ConsEffortID =tblRevegetationNew.ConsEffortID)
LEFT JOIN tblTrailWorkNew
ON tblConsEFFORT.ConsEffortID = tblTrailWorkNew.ConsEffortID)
ON (tblNatAreas.NatAreaID =tblManageUnits.NatAreaID)
AND (tblNatAreas.NatAreaID = tblConsEFFORT.NatAreaID)
WHERE ConsEffortID = (SELECT Max(ConsEffortID) from tblConsEffort)

Just change the WHERE clause if you are going to use a DateTimeField as the
determining factor

WHERE TheNewDateTimeField = (SELECT Max(TheNewDateTimeField) FROM
tblConsEFFORT)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

All of the previous records do have date fields that record the date the
data
was entered. This new function that I'm creating the query for, however,
would only be for use with future data, as it's a genuinely new feature
for a
genuinely new process- they've no interest in doing this for past data. I
had been using the autonumber- I didn't realize it was quite such a
liability.


So, presuming I add a field with a default value of =Now(), to
tblConsEffort-
how would I structure that query?

Always happy to give you more info if you'd like. Thanks for your help.


-Bonnie

How do you know which record in the main table is the most recent one? If
you
say the autonumber field, go directly to jail and do not pass go!
Autonumbers
are not guaranteed to be sequential and the next one could well be a
negative
number!

Also you can't depend on the last record being the bottom one when you
open
up the table. The next time you open the table, that record could be
somewhere else in the table.

What I use in these situations is a Date/Time field in the table with a
default of Now(). Unless you have more than one record being added in a
second, for example an append query or multiple users entering records,
you
can tell the last record added by doing a Max on the date/time field. Of
course you'll need to somehow add dates to the existing records as a
default
value only works on added records and not existing data.

I know I'm missing something terribly obvious here, but I just can't put
my
finger on it.

[quoted text clipped - 55 lines]

them will necessarily have a value- the tblReveg, tblInControl,
tblTrailWork
stuff, specificially.

 

[BonnieW via AccessMonster.com]

That looks gorgeous, and I'm sure it'd work, but there's either something
wonky in my relationships or, more likely, in the field names- this throws
the error "The specified field 'ConsEffortID' could refer to more than one
table listed in the FROM clause of your SQL statement."

This whole database seems to get more confusing the more I learn about how
these things should be done, gah.

Thanks for your help.

If you were to rely on the Autonumber or on the new date time field your
query could look like the following. Basically you are moving the Max into
the where clause

SELECT tblConsEFFORT.ConsEffortID
, tblConsEFFORT.Activity
, tblNatAreas.NatArea
, tblManageUnits.ManageUnit
, tblConsEFFORT.AddlMgmtUnits
, tblConsEFFORT.PeopleType
, tablePeople.FirstName
, tablePeople.LastName
, tblRevegetationNew.RevegType
, tblRevegetationNew.PlantID
, tblTrailWorkNew.TrailWorkType
, tblInvControlNew.Technique
, tblInvControlNew.TargetPlant
, tblInvControlNew.HerbicideType
, tblInvControlNew.[%Active]
, tblInvControlNew.AmtHerbicide
FROM tblNatAreas INNER JOIN ((((tablePeople RIGHT JOIN
(tblManageUnits INNER JOIN tblConsEFFORT
ON tblManageUnits.ManageUnitID = tblConsEFFORT.ManageUnitID)
ON tablePeople.PeopleID = tblConsEFFORT.PeopleID)
LEFT JOIN tblInvControlNew
ON tblConsEFFORT.ConsEffortID = tblInvControlNew.ConsEffortID)
LEFT JOIN tblRevegetationNew
ON tblConsEFFORT.ConsEffortID =tblRevegetationNew.ConsEffortID)
LEFT JOIN tblTrailWorkNew
ON tblConsEFFORT.ConsEffortID = tblTrailWorkNew.ConsEffortID)
ON (tblNatAreas.NatAreaID =tblManageUnits.NatAreaID)
AND (tblNatAreas.NatAreaID = tblConsEFFORT.NatAreaID)
WHERE ConsEffortID = (SELECT Max(ConsEffortID) from tblConsEffort)

Just change the WHERE clause if you are going to use a DateTimeField as the
determining factor

WHERE TheNewDateTimeField = (SELECT Max(TheNewDateTimeField) FROM
tblConsEFFORT)

All of the previous records do have date fields that record the date the
data

[quoted text clipped - 42 lines]

 

[John Spencer]

So SPECIFY the tablename that you want to use.

WHERE tblConsEFFORT.ConsEffortID =
(SELECT Max(ConsEffortID) from tblConsEffort)

I know that you have ConsEffortID as a field name in at least 4 tables by
looking at your FROM clause.

tblConsEFFORT.ConsEffortID
tblInvControlNew.ConsEffortID
tblRevegetationNew.ConsEffortID
tblTrailWorkNew.ConsEffortID

I should have noticed that and added the tablename. Laziness on my part.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

That looks gorgeous, and I'm sure it'd work, but there's either something
wonky in my relationships or, more likely, in the field names- this throws
the error "The specified field 'ConsEffortID' could refer to more than one
table listed in the FROM clause of your SQL statement."

This whole database seems to get more confusing the more I learn about how
these things should be done, gah.

Thanks for your help.

Just change the WHERE clause if you are going to use a DateTimeField as
the
determining factor

WHERE TheNewDateTimeField = (SELECT Max(TheNewDateTimeField) FROM
tblConsEFFORT)

All of the previous records do have date fields that record the date the
data

[quoted text clipped - 42 lines]

tblTrailWork
stuff, specificially.

 

[BonnieW via AccessMonster.com]

Perfect! Thanks!

I had to fuss with some of my joins a bit, but your help was just what I
needed for the tough spot

Not sure if you or anyone else cares, but for posterity's sake, this is the
code I ended up with:

SELECT tblConsEFFORT.ConsEffortID
, tblConsEFFORT.Activity
, tblNatAreas.NatArea
, tblManageUnits.ManageUnit
, tblConsEFFORT.AddlMgmtUnits
, tblConsEFFORT.PeopleType
, tablePeople.FirstName
, tablePeople.LastName
, tblTrailWorkNew.TrailWorkType
, tblInvControlNew.Technique
, tblInvControlNew.TargetPlant
, tblInvControlNew.HerbicideType
, tblInvControlNew.[%Active]
, tblInvControlNew.AmtHerbicide
, tblRevegetationNew.RevegType
, tblRevegetationNew.PlantID
FROM ((tblNatAreas
RIGHT JOIN ((tablePeople
RIGHT JOIN (tblManageUnits
RIGHT JOIN tblConsEFFORT
ON tblManageUnits.ManageUnitID = tblConsEFFORT.ManageUnitID)
ON tablePeople.PeopleID = tblConsEFFORT.PeopleID)
LEFT JOIN tblTrailWorkNew
ON tblConsEFFORT.ConsEffortID = tblTrailWorkNew.ConsEffortID)
ON tblNatAreas.NatAreaID = tblConsEFFORT.NatAreaID)
LEFT JOIN tblRevegetationNew
ON tblConsEFFORT.ConsEffortID = tblRevegetationNew.ConsEffortID)
LEFT JOIN tblInvControlNew
ON tblConsEFFORT.ConsEffortID = tblInvControlNew.ConsEffortID
WHERE (((tblConsEFFORT.ConsEffortID)=(SELECT Max(ConsEffortID) from
tblConsEffort)));

Thanks again!

So SPECIFY the tablename that you want to use.

WHERE tblConsEFFORT.ConsEffortID =
(SELECT Max(ConsEffortID) from tblConsEffort)

I know that you have ConsEffortID as a field name in at least 4 tables by
looking at your FROM clause.

tblConsEFFORT.ConsEffortID
tblInvControlNew.ConsEffortID
tblRevegetationNew.ConsEffortID
tblTrailWorkNew.ConsEffortID

I should have noticed that and added the tablename. Laziness on my part.

That looks gorgeous, and I'm sure it'd work, but there's either something
wonky in my relationships or, more likely, in the field names- this throws

[quoted text clipped - 5 lines]

Thanks for your help.

Just change the WHERE clause if you are going to use a DateTimeField as
the [quoted text clipped - 8 lines]
tblTrailWork
stuff, specificially.