J
joer
Hey,
Spoke too soon...I played some more with the previous SQL and think I
got something similar to work (shows data and doesn't crash):
SELECT DISTINCT UserEntryData.Setup, BOM.REELNO,
UserEntryData.KitStartTime, [History].LASTACTIVITY, [History].LOCATION
FROM [History] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [History].REELNO = BOM.REELNO;
An example of what this gives me:
Setup ReelNo KitStartTime LastActivity Location
123 5678 7/25 5:00 7/24 8:00 2
123 5678 7/25 5:00 7/25 3:00 4
123 5678 7/25 5:00 7/26 7:00 2
123 8765 7/25 5:00 7/24 10:00 2
123 8765 7/25 5:00 7/26 5:00 4
And example of what I want:
Setup ReelNo KitStartTime LastActivity Location
123 5678 7/25 5:00 7/25 3:00 4
123 8765 7/25 5:00 7/24 10:00 2
So it should only return one record per reel number and that record is
the one which has the most recent last activity less than or equal to
the KitStartTime. Hopefully this clarifies what I'm trying to do in
terms of the data that I have. Maybe there's a simpler solution than
what we've been trying?
Thanks.
Spoke too soon...I played some more with the previous SQL and think I
got something similar to work (shows data and doesn't crash):
SELECT DISTINCT UserEntryData.Setup, BOM.REELNO,
UserEntryData.KitStartTime, [History].LASTACTIVITY, [History].LOCATION
FROM [History] INNER JOIN (UserEntryData INNER JOIN BOM ON
UserEntryData.Setup = BOM.Setup) ON [History].REELNO = BOM.REELNO;
An example of what this gives me:
Setup ReelNo KitStartTime LastActivity Location
123 5678 7/25 5:00 7/24 8:00 2
123 5678 7/25 5:00 7/25 3:00 4
123 5678 7/25 5:00 7/26 7:00 2
123 8765 7/25 5:00 7/24 10:00 2
123 8765 7/25 5:00 7/26 5:00 4
And example of what I want:
Setup ReelNo KitStartTime LastActivity Location
123 5678 7/25 5:00 7/25 3:00 4
123 8765 7/25 5:00 7/24 10:00 2
So it should only return one record per reel number and that record is
the one which has the most recent last activity less than or equal to
the KitStartTime. Hopefully this clarifies what I'm trying to do in
terms of the data that I have. Maybe there's a simpler solution than
what we've been trying?
Thanks.
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 said: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 said: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;
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;