Find most recent Date

[Guest]

Hi There
Seem to be having trouble finding the most recent date on one of my tables.
I have a table I call [Equipment Rotation History] I keep a running record
of [Equipment ID] and the location’s [Location ID] that the Equipment has
been at. When I print my reports for the Equipment I need the most recent
date that the Equipment has been moved.
If I keep entering the dates that I move the Equipment in order the
following query so far has worked OK.

SELECT [Equipment Rotation History].[Equipment ID], Last([Equipment Rotation
History].[Location ID]) AS [LastOfLocation ID], Last([Equipment Rotation
History].[Date Installed]) AS [LastOfDate Installed]
FROM [Equipment Rotation History]
GROUP BY [Equipment Rotation History].[Equipment ID];

But if some time goes buy and I don’t stay on top of my paper work I may not
enter the dates of the moves in order and my report will not reflect the most
recent date for the Equipment and Location.
So I tried using this query WHERE I’m trying to SELECT the MAX [Date
Installed] but for some reason it just doesn’t work.

SELECT [Equipment ID], [Location ID], [Date Installed]
FROM [Equipment Rotation History]
WHERE [Date Installed] In (SELECT MAX([Date installed]) FROM [Equipment
Rotation History] GROUP BY [Equipment ID]);

Any ideas???
I need help Please

 

[Albert D.Kallal]

SELECT [Equipment ID], [Location ID], [Date Installed]
FROM [Equipment Rotation History]
WHERE [Date Installed] In (SELECT MAX([Date installed]) FROM [Equipment
Rotation History] GROUP BY [Equipment ID]);

hum...looking at the sub query of

In (SELECT MAX([Date installed]) FROM [Equipment

Rotation History] GROUP BY [Equipment ID]);

What reocrd are you restricintg the above to? It will run on the WHOLE
file...not just those for a given Equipment id.

Any ideas???

I going to suggest we apeparhc this problem from the tblEquipment...and NOT
the histry table. (I hope, or assume we have a tblEquipment list).

It makes no sense to try and "group" the equipment id with all kinds of
diffcilot groupby commands. That file is going to have many dupliclaes of
equipment id. If we ALREADY HAVE a nice uniquire list of equipment from the
table called tblEquipment, then this whole probem becomes more clear to deal
with.

Furhter, by basing your reprot on a query that runs from tblEquipment, then
fields like the descripton, and all kinds of things you need from the
tblEquipment can be displyed in the report.

Ok....so, lets assume we have tblEquipment, and your above history
table.....

By the way, dump the use of spaces in your sql. They REALLY are hard to deal
with...and force you to puse []. Wrose, the inddsltury standard systems like
Oracle, or even MS's own sql server do not support spaces in field names.

To display our tblEqiupment, we would have

select id, EquipmentName, EquipmentPurchaseDate
from tblEquipment

Of couse, we want to include some fields from the histry table...so, we
simply drop in the hisotry table (note that we can build this query in the
query buidler up to this point).

select id, EquipmentName, PurchaseDate, LocationID, DateInstalled
from tblEquipment
left join tblRotaionHistory on tblEquipment.ID =
tblRotationHistory.EquipmentID

ok, so, you can now see how our query returns fields from both talbes..from
tblEquipment, we have id, EquipmetName, and from the tblRotationHistory
table, we include the lcoationid, and dateinstalled.

Of couse, you want to inlucde only the LAST date....., so,

select id, EquipmentName, PurchaseDate, LocationID, DateInstalled
from tblEquipment
left join tblRotaionHistory on tblEquipment.ID =
tblRotationHistory.EquipmentID
where EquipmentID in
(select top 1 EquipmentID from tblRotationHistry
where EquipmentID = tblEquipment.ID order by DateInstalled DESC)


So, the above query allows us to freely pull values from the main
tblEquipment, and also from the history table. Of course, for fields like
location id...you can just continues using the query builder...and throw in
those additional tables if you need descriptions from the locations table
etc. The result is a query that is much more suited for reporting use...

 

[Guest]

--
thank You

SELECT [Equipment ID], [Location ID], [Date Installed]
FROM [Equipment Rotation History]
WHERE [Date Installed] In (SELECT MAX([Date installed]) FROM [Equipment
Rotation History] GROUP BY [Equipment ID]);

hum...looking at the sub query of

In (SELECT MAX([Date installed]) FROM [Equipment

Rotation History] GROUP BY [Equipment ID]);

What reocrd are you restricintg the above to? It will run on the WHOLE
file...not just those for a given Equipment id.

Any ideas???

I going to suggest we apeparhc this problem from the tblEquipment...and NOT
the histry table. (I hope, or assume we have a tblEquipment list).

It makes no sense to try and "group" the equipment id with all kinds of
diffcilot groupby commands. That file is going to have many dupliclaes of
equipment id. If we ALREADY HAVE a nice uniquire list of equipment from the
table called tblEquipment, then this whole probem becomes more clear to deal
with.

Furhter, by basing your reprot on a query that runs from tblEquipment, then
fields like the descripton, and all kinds of things you need from the
tblEquipment can be displyed in the report.

Ok....so, lets assume we have tblEquipment, and your above history
table.....

By the way, dump the use of spaces in your sql. They REALLY are hard to deal
with...and force you to puse []. Wrose, the inddsltury standard systems like
Oracle, or even MS's own sql server do not support spaces in field names.

To display our tblEqiupment, we would have

select id, EquipmentName, EquipmentPurchaseDate
from tblEquipment

Of couse, we want to include some fields from the histry table...so, we
simply drop in the hisotry table (note that we can build this query in the
query buidler up to this point).

select id, EquipmentName, PurchaseDate, LocationID, DateInstalled
from tblEquipment
left join tblRotaionHistory on tblEquipment.ID =
tblRotationHistory.EquipmentID

ok, so, you can now see how our query returns fields from both talbes..from
tblEquipment, we have id, EquipmetName, and from the tblRotationHistory
table, we include the lcoationid, and dateinstalled.

Of couse, you want to inlucde only the LAST date....., so,

select id, EquipmentName, PurchaseDate, LocationID, DateInstalled
from tblEquipment
left join tblRotaionHistory on tblEquipment.ID =
tblRotationHistory.EquipmentID
where EquipmentID in
(select top 1 EquipmentID from tblRotationHistry
where EquipmentID = tblEquipment.ID order by DateInstalled DESC)

So, the above query allows us to freely pull values from the main
tblEquipment, and also from the history table. Of course, for fields like
location id...you can just continues using the query builder...and throw in
those additional tables if you need descriptions from the locations table
etc. The result is a query that is much more suited for reporting use...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Yes we do have a Equipment table but unfortunately it does not have the LocationID or the DateInstalled fields. I do have a Location table (would be same as a Customer table).

The Equipment table just has information like Description, Mauf., S/N, Type,
etc…
The location table just has information like Name, adderrss, phone, etc…
The dateInstalled is unique to the RotationHistory table, Which also has the
LocationID, EquuipmentID.
So I can’t do the left join.
For some reason all of the query’s I’ve tried,the locationID always seems to
be the trouble.

 

[Albert D.Kallal]

you are most welcome...and my apologies for missing the spell check....I was
on a laptop..and hit sent by accident...

 

[Guest]

Yes we do have a Equipment table but unfortunately it does not have the
LocationID or the DateInstalled fields. I do have a Location table (would be
same as a Customer table).
The Equipment table just has information like Description, Mauf., S/N, Type,
etc…
The location table just has information like Name, adderrss, phone, etc…
The dateInstalled is unique to the RotationHistory table, Which also has the
LocationID, EquuipmentID.
So I can’t do the left join.
For some reason all of the query’s I’ve tried,the locationID always seems to
be the trouble.

 

[Albert D.Kallal]

Yes we do have a Equipment table but unfortunately it does not have the
LocationID or the DateInstalled fields. I do have a Location table (would
be
same as a Customer table).
The Equipment table just has information like Description, Mauf., S/N,
Type,
etc.
The location table just has information like Name, adderrss, phone, etc.
The dateInstalled is unique to the RotationHistory table, Which also has
the
LocationID, EquuipmentID.
So I can't do the left join.

The above seems a good desing.

For some reason all of the query's I've tried,the locationID always seems
to
be the trouble.

Hum, it should work...and is even kind of fun to build....

lets try it...

......lets do the following.

fire up query builder....

Drop in our main equipment table. I going to call it tblEquipment...

tblEquipmcnet....

Now, drag into the query grid any, and all fields you need from this table.

Try the query...(save it...and view it...). I OFTEN flip between design
view..and query view just to see how the piece of art is progressing!!

Try the query....

Ok. now, we need our tblRotationHistry....

drop in that table to the query builder. Drop the join line FROM our
tblEquipment TO THE tblRotationHistory table. (the direclty you draw this
line is important). Now, make this a left join......


A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join.

To make a left join, you drop in the tables (in the query builder)

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "tblEquipment" and only those records from
"tblRotation" where the joined fields are equal

(this is our left join. So, our main table tblEquipment will be returned
in
this query, REGARDLESS if the child records (tblRotaton in this example)
exist, or not!. This is left join

ok..now, drop into the grid as many fields as you need from the
tblRotation..

You mentioned that tblRotation has a few lookup fields..such as
location.....

Ok...just drop in the tblLocation table into your query builder also. Again
make this a left join. Grab as may fields you want from the location
table....

You can continue the above process for ANY of the above tables that have
lookup, or relations to other tables. Again, try the query as you go along
here....

If you have 2, or 35 tables to lookup, or join in..then do so. You don't
need write any raw sql..and it is very easy, and almost fun to just drop in
each additional table you need..and draw the join lines. (if the join lines
already draw for you..then double click on the join line..and MAKE SURE YOU
SELECTED A LEFT join).

Simply get the query working. Now, you can simply then add a "where" clause
to the above to restrict the results to the LAST date as I demonstrated.

You need to get these left joins works...for virtually ANY TIME you work
with more then one table in the query builder...you will be using left
joins...

 

[Guest]

I think I got it. It was like a light bulb going off in my head.
It seems to work. I’ll know soon, if the reports (collation tickets) don’t
match the equipment on location.
I could never have more equipmentID records form my tblEquipment.
If my tblRotationHistory query came back with more equipmentID it had to be
wrong.
And I had several equipmentID that never been placed so I would have a
“NULL†value in the MAX(DateInstalled) field of the Query.

This is my SQL (I’m work on renaming all my tables –HUNGARIAN NAMING- my
first class never taught that)

SELECT Equipment.[Equipment ID], Equipment.Description,
Last(Location.[Location ID]) AS [LastOfLocation ID], Max([Equipment Rotation
History].[Date Installed]) AS [MaxOfDate Installed]
FROM Location RIGHT JOIN (Equipment LEFT JOIN [Equipment Rotation History]
ON Equipment.[Equipment ID] = [Equipment Rotation History].[Equipment ID]) ON
Location.[Location ID] = [Equipment Rotation History].[Location ID]
GROUP BY Equipment.[Equipment ID], Equipment.Description;

But will changing my joins in the query will that mess up my relationships
of all my tables??? Or is that just for the query??? I could also use a
Dlookup for Location name???

Anyway thanks for all the help!!!

Hope to see you again In a couple of months because I’ll be changing my
income form and I’ll be working on a listBox that will have multiple columns
for display records from my tblIncome, tblLocation, tblEquipment , and let my
enter the income from each piece of equipment. The ListBox has to grow in
size for the equipment description.