ok grasped the basics there - tried to add a little more and stumbled so
mailed some data
:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:
tblSupercalafragilisticexpialidocious:
filed1
field2
field3
tblAnotherreallylongstupidname
field1
field2
field3
SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copy and
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries
Example
EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement
Help!
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
