DLookup and DMax with dates

[Joseph Rosing]

Hi,
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

![Field] because when I set
them to just

, 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.

Thanks for any help.


Joe

 

[Duane Hookom]

I would probably write my own function that opens a record set to find the
information. However a quick review of your syntax reveals that you haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" & DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]<=#" & [KitStartTime] & "#") & "#")

 

[Joseph Rosing]

Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

I would probably write my own function that opens a record set to find the
information. However a quick review of your syntax reveals that you haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" & DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]<=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

Hi,
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

![Field] because when I set
them to just

, 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.

Thanks for any help.


Joe

 

[Duane Hookom]

Again, I would probably create a small function that would do the lookups.
Nesting domain aggregate functions is too complex for me to maintain.

--
Duane Hookom
MS Access MVP

Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

I would probably write my own function that opens a record set to find
the
information. However a quick review of your syntax reveals that you
haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" &
DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]<=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

Hi,
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

![Field] because when I set
them to just

, 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.

Thanks for any help.


Joe

 

[Joseph Rosing]

Can you give me an idea of what the function would look like? Are
there other things I can do maybe in terms of filtering the data and
using only one aggregate function to pull the locations?

Joe

Again, I would probably create a small function that would do the lookups.
Nesting domain aggregate functions is too complex for me to maintain.

--
Duane Hookom
MS Access MVP

Thanks for the advice but I'm still not getting what I need. After
fixing the quotations, the query returned one record (and then froze up
on me) but the record did not have anything in the StartLoc column. So
while it didn't give me the #Error, it still didn't return the
location.

So I'm thinking I could have the location column in the query and use
Dmax as a criteria for that column? The only problem is that I have
several different part numbers in the query and need the last location
for each part number before the start time (i.e. if the query only
returns one record it's no good; I need 1 record per part number).
Does that make sense?

What kind of things could I do in terms of writing my own function?

Thanks,
Joe

I would probably write my own function that opens a record set to find
the
information. However a quick review of your syntax reveals that you
haven't
placed you field and table names in quotes and have place quotes around
expressions that shouldn't. Without testing your logic, I would expect to
change your syntax to:

DLookUp("LOCATION","[HISTORY]","[LASTACTIVITY]=#" &
DMax("[LASTACTIVITY]",
"[HISTORY]","[LASTACTIVITY]<=#" & [KitStartTime] & "#") & "#")

--
Duane Hookom
MS Access MVP

Hi,
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

![Field] because when I set
them to just

, 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.

Thanks for any help.


Joe

 

[Gary Walter]

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

![Field] because when I set
them to just

, 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

 

[Gary Walter]

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:

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

![Field] because when I set
them to just

, 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]

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

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:

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

![Field] because when I set
them to just

, 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

 

[Duane Hookom]

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

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

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:

:
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

![Field] because when I set
them to just

, 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]

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

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

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

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:
:
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

![Field] because when I set
them to just

, 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]

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

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

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

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:
:
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

![Field] because when I set
them to just

, 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

 

[Gary Walter]

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

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

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

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:
:
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

![Field] because when I
set
them to just

, 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]

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!

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

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

Joe,
Reply back with your exact SQL view from your query.

--
Duane Hookom
MS Access MVP

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:
:
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

![Field] because when I
set
them to just

, 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

 

[Gary Walter]

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;

 

[joer]

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

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 Walter]

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;

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

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;

 

[joer]

Still crashes....

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;

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

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;

 

[joer]

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

Still crashes....

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;

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:
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 Walter]

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

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

Still crashes....

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;



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:
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;

 

[joer]

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.

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

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

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;



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:
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;