DLookup and DMax with dates

Per Gary's suggestion:

SELECT
m.KitStartTime,
PARAMETERS h as number, q as date
.....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
[UserEntry] As m

For reference, I have 2 tables loaded in the query: UserEntry, History,
and a previous query, BOM. LastActivty and StartLoc are formatted
date/time and Location is formatted as a number. I'm not sure how the
SQL would look to have the above code with the code which joins the
tables and previous query.

Like I said, my programming experience is very basic and I've never
written SQL for Access, only used set functions. I'm assuming from
Gary's post that h, q, and m are variables which could really be any
name but I'm not sure how to declare them in Access (i.e. I know above,
by setting "PARAMETER h as number" I get an error since "number" is a
reserved word in Access).

Thanks for helping.

Joe


Duane Hookom wrote:
Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time closest
to

the StartTime and returns the Location for that LastActivity
date/time.
In summary, I want to return the location of a part at the specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] & "#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

The m, h and q were table aliases that allow
one to not need to write out full table names,
plus they allow use of the same table more
than once in a query without Access
becoming confused.

The SQL that was hoped for was your
original query.

If you started with your original query
and right-mouse clicked on the table
UserEntry, and chose Properties,
you could change the Alias of the
table to "m"

I did not know where KitStartTime
came from (which table), so used an alias
for the table.

the subquery

(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime))

needs to replace the field row of the column
in your original query where you tried to use
the domain function to get StartLoc.

So, in your original query, find that column
and replace your lookup function with the
subquery above (typed out all on one line).

If you could provide the SQL for your original
query, we could probably give you back a SQL
string that should work properly.

Thanks

"Joseph Rosing" wrote:

SELECT
m.KitStartTime,
PARAMETERS h as number, q as date
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
[UserEntry] As m

For reference, I have 2 tables loaded in the query: UserEntry, History,
and a previous query, BOM. LastActivty and StartLoc are formatted
date/time and Location is formatted as a number. I'm not sure how the
SQL would look to have the above code with the code which joins the
tables and previous query.

Like I said, my programming experience is very basic and I've never
written SQL for Access, only used set functions. I'm assuming from
Gary's post that h, q, and m are variables which could really be any
name but I'm not sure how to declare them in Access (i.e. I know above,
by setting "PARAMETER h as number" I get an error since "number" is a
reserved word in Access).

Thanks for helping.

Joe


Duane Hookom wrote:
Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time
closest
to

the StartTime and returns the Location for that LastActivity
date/time.
In summary, I want to return the location of a part at the
specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] &
"#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is
less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I
set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one
at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.

Thanks for the help!


Gary Walter wrote:
The m, h and q were table aliases that allow
one to not need to write out full table names,
plus they allow use of the same table more
than once in a query without Access
becoming confused.

The SQL that was hoped for was your
original query.

If you started with your original query
and right-mouse clicked on the table
UserEntry, and chose Properties,
you could change the Alias of the
table to "m"

I did not know where KitStartTime
came from (which table), so used an alias
for the table.

the subquery

(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime))

needs to replace the field row of the column
in your original query where you tried to use
the domain function to get StartLoc.

So, in your original query, find that column
and replace your lookup function with the
subquery above (typed out all on one line).

If you could provide the SQL for your original
query, we could probably give you back a SQL
string that should work properly.

Thanks

"Joseph Rosing" wrote:

SELECT
m.KitStartTime,
PARAMETERS h as number, q as date
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
[UserEntry] As m

For reference, I have 2 tables loaded in the query: UserEntry, History,
and a previous query, BOM. LastActivty and StartLoc are formatted
date/time and Location is formatted as a number. I'm not sure how the
SQL would look to have the above code with the code which joins the
tables and previous query.

Like I said, my programming experience is very basic and I've never
written SQL for Access, only used set functions. I'm assuming from
Gary's post that h, q, and m are variables which could really be any
name but I'm not sure how to declare them in Access (i.e. I know above,
by setting "PARAMETER h as number" I get an error since "number" is a
reserved word in Access).

Thanks for helping.

Joe


Duane Hookom wrote:
Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

"Joseph Rosing" wrote in message
oups.com...
Thanks for the help Gary. I think your logic will work but when I put
it into the SQL screen I get a syntax error (missing operator). Any
idea what would be missing? Do I need to declare m, h, and q in the
beginning?

I'm new to access and do not have much experience with programming so
I'm not sure what else needs to be entered in the SQL screen other than
what you have below.

Thanks for the help,
Joe


Gary Walter wrote:
that should be:

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
History As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
History As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m


"Gary Walter" typed out erroneously:
"Joseph Rosing" wrote:
I have two date/time fields: StartTime and LastActivity.
In the History table, there are Location and LastActivity fields.
I want to run a query which finds the LastActivity date/time
closest
to

the StartTime and returns the Location for that LastActivity
date/time.
In summary, I want to return the location of a part at the
specified
StartTime.

The code I'm using in the calculated field is below which returns a
#Error message in the StartLoc column.


StartLoc:
DLookUp([LOCATION],[HISTORY]![LOCATION],"[LASTACTIVITY]=#" &
DMax([LASTACTIVITY] &
"#",[HISTORY]![LASTACTIVITY],"[LASTACTIVITY]=#"
& "[KitStartTime]" & "#"))

Thus, the query looks up the maximum LastActivity time which is
less
than or equal to the StartTime and returns the Location where
LastActivity equals that maximum LastActivity (or so I wish).

I have the domain fields set to be [Table]![Field] because when I
set
them to just [Table], the query asks me for the parameter before it
runs. I've tried splitting up the functions and working them one
at a
time but to no avail.

I would think you could add a correlated subquery

SELECT
m.KitStartTime,
....
(SELECT
First(h.Location)
FROM
Location As h
WHERE
h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
Location As q
WHERE
q.LastActivity = m.KitStartTime)) As StartLoc
FROM
sometable As m

"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

Thanks Gary. I'm trying to get it to work but Access freezes up on me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions on
optimizing this query so it doesn't time-out or crash Access? Does it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

does this bomb?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO;



"joer" wrote in message
oups.com...
Thanks Gary. I'm trying to get it to work but Access freezes up on me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions on
optimizing this query so it doesn't time-out or crash Access? Does it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

Still crashes....

Gary Walter wrote:
does this bomb?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO;



"joer" wrote in message
oups.com...
Thanks Gary. I'm trying to get it to work but Access freezes up on me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions on
optimizing this query so it doesn't time-out or crash Access? Does it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

Hi,
I tried deleting some of the formula thinking maybe I don't have to
"select" the location but can just leave it as a column that will be
filtered by the rest of the equation. I tried the code below and it
still crashed but is there something I can do along these lines that
will optimize the query?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO


This way it should just select the last activity where last activity is
= StartTime and then place the locations at each of those times in the
locations column next to the last activity field. Is this the same as
using a DMax in the criteria field of LastActivity?

Joe


joer wrote:
Still crashes....

Gary Walter wrote:
does this bomb?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO;



"joer" wrote in message
oups.com...
Thanks Gary. I'm trying to get it to work but Access freezes up on me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions on
optimizing this query so it doesn't time-out or crash Access? Does it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience. Below
is the SQL for the entire query with Gary's code in the field row of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM query
in the history table and return the locations of each part number at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

I'm sorry I'm so busy at work
(plus I cannot see your data)...

Please verify for me that this works:

SELECT
UserEntryData.KitStartTime,
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO

does every record have a valid KitStartTime
(and an associated LastActivity)?

please post SQL for query BOM.

if I understood correctly,
UserEntryData
and
History
are tables?

thanks

"joer" wrote in message
ups.com...
Hi,
I tried deleting some of the formula thinking maybe I don't have to
"select" the location but can just leave it as a column that will be
filtered by the rest of the equation. I tried the code below and it
still crashed but is there something I can do along these lines that
will optimize the query?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO


This way it should just select the last activity where last activity is
= StartTime and then place the locations at each of those times in the
locations column next to the last activity field. Is this the same as
using a DMax in the criteria field of LastActivity?

Joe


joer wrote:
Still crashes....

Gary Walter wrote:
does this bomb?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO;



"joer" wrote in message
oups.com...
Thanks Gary. I'm trying to get it to work but Access freezes up on
me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions
on
optimizing this query so it doesn't time-out or crash Access? Does
it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience.
Below
is the SQL for the entire query with Gary's code in the field row
of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE
h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As
q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a
given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last
activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code
just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM
query
in the history table and return the locations of each part number
at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

Gary,
The SQL you gave does not crash Access but it doesn't return any data
either. I tried moving some of the fields around but it still didn't
return anything.

To answer your questions, every "Setup" record will have a KitStartTime
once the user enters the start time in the form (the underlying table
of that form is UserEntryData). So there is not a unique KitStartTime
for each ReelNo or Workorder but only each Setup. Make sense?

UserEntryData and History are tables. UserEntryData comes from a form
where user's will input information and History comes from a materials
database and is updated automatically. BOM is a query based off of the
History table and the Workorders query. The BOM query identifies the
reel numbers on a given workorder and the workorder query identifies
the workorders on a given setup. The two queries lead me to linking
reel number and setup which is ultimatly how the data needs to be
displayed (along with the reel locations at the beginning and end of
the setup).

The SQL for BOM is:
SELECT DISTINCT Workorders.Setup, Workorders.WORKORDERNO,
[History].REELNO
FROM [History] INNER JOIN Workorders
ON [History].WORKORDERNO = Workorders.WORKORDERNO
ORDER BY Workorders.Setup;


Thanks for the help.


Gary Walter wrote:
I'm sorry I'm so busy at work
(plus I cannot see your data)...

Please verify for me that this works:

SELECT
UserEntryData.KitStartTime,
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO

does every record have a valid KitStartTime
(and an associated LastActivity)?

please post SQL for query BOM.

if I understood correctly,
UserEntryData
and
History
are tables?

thanks

"joer" wrote in message
ups.com...
Hi,
I tried deleting some of the formula thinking maybe I don't have to
"select" the location but can just leave it as a column that will be
filtered by the rest of the equation. I tried the code below and it
still crashed but is there something I can do along these lines that
will optimize the query?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO


This way it should just select the last activity where last activity is
= StartTime and then place the locations at each of those times in the
locations column next to the last activity field. Is this the same as
using a DMax in the criteria field of LastActivity?

Joe


joer wrote:
Still crashes....

Gary Walter wrote:
does this bomb?

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
INNER JOIN
[HISTORY]
ON
BOM.REELNO = [HISTORY].REELNO;



"joer" wrote in message
oups.com...
Thanks Gary. I'm trying to get it to work but Access freezes up on
me
everytime I run the query. I've tried filtering it so that there are
less recrods to sort through but it still freezes. Any suggestions
on
optimizing this query so it doesn't time-out or crash Access? Does
it
matter if it is querying from a table vs. a previous query?

Thanks,
Joe


Gary Walter wrote:
"Joseph Rosing" wrote in message
ups.com...
Hi,
Sorry for the confusion, you'll have to excuse my inexperience.
Below
is the SQL for the entire query with Gary's code in the field row
of
the StartLoc column.

SELECT UserEntryData.Setup, BOM.WORKORDERNO, BOM.REELNO,
[HISTORY].LASTACTIVITY, [HISTORY].LOCATION, (SELECT
First(h.Location) FROM [HISTORY] As h WHERE
h.LastActivity
= (SELECT Max(q.LastActivity) FROM [HISTORY] As
q
WHERE q.LastActivity = m.KitStartTime)) AS StartLoc
FROM [HISTORY] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [HISTORY].REELNO = BOM.REELNO;

UserEntryData is a table where KitStartTime is stored.
BOM is a query which produces the part numbers (BOM.REELNO) for a
given
workorder number (BOM.WORKORDERNO).
History is the table which contains the locations and last
activity
time stamps for all the part numbers.

I have not set the aliases because I was unsure which each letter
identifies: h is History table, q is Location field, and m is
UserEntryData table? I guess the q confuses me-shouldn't the code
just
refer to h again: "....(SELECT Max(q.LastActivity) FROM
[HISTORY] As q WHERE q.LastActivity = ...."

The query needs to look up the part numbers produced from the BOM
query
in the history table and return the locations of each part number
at
the LastActivity most recent to the KitStartTime.


thanks...I think this should do it..

SELECT
UserEntryData.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = UserEntryData.KitStartTime
)
) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData
INNER JOIN
BOM
ON
UserEntryData.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;

or

SELECT
m.Setup,
BOM.WORKORDERNO,
BOM.REELNO,
[HISTORY].LASTACTIVITY,
[HISTORY].LOCATION,
(SELECT
First(h.Location)
FROM
[HISTORY] As h
WHERE h.LastActivity =
(SELECT
Max(q.LastActivity)
FROM
[HISTORY] As q
WHERE
q.LastActivity = m.KitStartTime)) AS StartLoc
FROM
[HISTORY]
INNER JOIN
(UserEntryData As m
INNER JOIN
BOM
ON
m.Setup = BOM.Setup)
ON
[HISTORY].REELNO = BOM.REELNO;