DLookup and DMax with dates

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.


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;
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

I could be wrong, but the SQL I had you try
was basically the *original query* that started
this discussion without the "calculated field"
StartLoc .....

If it does not "return any data,"
figuring out the subquery will be
a moot point since the subquery
depends on a KitStartTime to work
which apparently doesn't "exist."

Did you see the email I sent to your
gmail account?

joer said:
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;
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

In the query grid under column for field
History.LastActivity, type the following
in the "Criteria:" row (all as one line):

(SELECT Max(h.LastActivity)
FROM [History] As h
WHERE
h.ReelNo = BOM.ReelNo
AND
h.LastActivity <= UserEntryData.KitStartTime)

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

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;
Click to expand...
Click to expand...
Click to expand...
 
J

joer

This code doesn't crash Access but the query takes forever to run...i.e
I Ctrl+Alt+Delete after running it for 45 minutes. Any ideas why it
would take so long? I added criteria in the setup to limit it to only
look up one setup thinking it wouldn't take as long too.

Thanks


Gary said:
In the query grid under column for field
History.LastActivity, type the following
in the "Criteria:" row (all as one line):

(SELECT Max(h.LastActivity)
FROM [History] As h
WHERE
h.ReelNo = BOM.ReelNo
AND
h.LastActivity <= UserEntryData.KitStartTime)

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

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;
Click to expand...
Click to expand...
Click to expand...
 
J

joer

Do I need "#" to specify date/time fields for lastactivity and kit
start time?

Joe
This code doesn't crash Access but the query takes forever to run...i.e
I Ctrl+Alt+Delete after running it for 45 minutes. Any ideas why it
would take so long? I added criteria in the setup to limit it to only
look up one setup thinking it wouldn't take as long too.

Thanks


Gary said:
In the query grid under column for field
History.LastActivity, type the following
in the "Criteria:" row (all as one line):

(SELECT Max(h.LastActivity)
FROM [History] As h
WHERE
h.ReelNo = BOM.ReelNo
AND
h.LastActivity <= UserEntryData.KitStartTime)

joer said:
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.



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

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;
Click to expand...
Click to expand...
Click to expand...
 
J

joer

Alright, I think I have something going here.

I broke up my process into two queries. Query 1 runs with a Totals row
where LastActivity is "Max" and another LastActivity column is "Where"
with <= [KitStartTime] in the criteria. This produces the maximum last
activity time which is less than kit start time for each reel number.
The second query consists of setup, reelno, and MaxofLastActivity from
Query 1 and Location from the History table. This gives me the start
locations for each reel number.

So it's a little more roundabout way but it works quickly and I'll just
have to write a macro in the end to make the application run each query
in sequence. I also need to do the same procedure now for kit end
time.

Any thoughts? Is this a good long term solution?

Joe

Do I need "#" to specify date/time fields for lastactivity and kit
start time?

Joe
This code doesn't crash Access but the query takes forever to run...i.e
I Ctrl+Alt+Delete after running it for 45 minutes. Any ideas why it
would take so long? I added criteria in the setup to limit it to only
look up one setup thinking it wouldn't take as long too.

Thanks


Gary said:
In the query grid under column for field
History.LastActivity, type the following
in the "Criteria:" row (all as one line):

(SELECT Max(h.LastActivity)
FROM [History] As h
WHERE
h.ReelNo = BOM.ReelNo
AND
h.LastActivity <= UserEntryData.KitStartTime)

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



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

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;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Dlookup and Dmax with dates 11
Calculate time difference 3
DLookup in Query 8
sqlQuery for daily plans and tasks 1
Dlookup in query - any pointers? 1
Using Max or DMax in DLookUp criteria? 1
Convert GPS Date and Time 2
DMAX on UPDATE with INNER JOIN 5

Top