Max Date Problems

speakers_86

speakers_86

Registered User.
Local time
Today, 18:19
Joined
May 17, 2007
Messages
1,919
Can anyone tell my why this works:
Code: 
SELECT Equipment_Transactions.EquipmentID, Max([Date_of_Transfer]+[Time_of_Transfer]) AS [Date]
FROM Equipment_Transactions
GROUP BY Equipment_Transactions.EquipmentID
ORDER BY Equipment_Transactions.EquipmentID;



But this does not???
Code: 
SELECT Equipment_Transactions.EquipmentID, Max([Date_of_Transfer]+[Time_of_Transfer]) AS [Date], Equipment_Transactions.To_Location
FROM Equipment_Transactions
GROUP BY Equipment_Transactions.EquipmentID, Equipment_Transactions.To_Location
ORDER BY Equipment_Transactions.EquipmentID;
 
It would probably be more correct to say that it doesn't do what you want. I'm sure it "works" correctly. Generally, you want to join the first query to the original table in a second query, on those 2 fields. That will enable you to bring back other information from that record.
 
Well, Ive tried many different ways to get it, but to no avail.

Ill include the db if someone wants to take a look.

The query I am looking at it called qryEquipment_Current_Location_Sub. The results it returns are right at the moment, but I need another field returned. It can be transaction Id from the transaction table, or to_Location, or "to" from qryEquipment_ToLocation. Any one of those fields should work.
 

Attachments

I only have 2000 on this PC, so perhaps someone else can take a look. Did you try the second query I suggested? That's the standard way to get other fields in this situation.
 
That didnt work.

I changed the name of the qry in question. It is now called qryEquipment_LocationSUB
 

Attachments

It's working for me. I don't see anything not working. What version (and Service Pack level) of Access are you using?
 
Bob's here to save me again! Oh goody.

This is complicated by storing the date and time separately. If I save that first query as qryPaulBase, this appears to work.

SELECT Equipment_Transactions.EquipmentID, Equipment_Transactions.To_Location
FROM Equipment_Transactions INNER JOIN qryPaulBase ON Equipment_Transactions.EquipmentID = qryPaulBase.EquipmentID AND (Equipment_Transactions.Date_of_Transfer + Equipment_Transactions.Time_of_Transfer) = qryPaulBase.Date
 
How many records does your query result in? It should be 2 dated 10/2 and 10/3
 
speakers_86 said:
How many records does your query result in? It should be 2 dated 10/2 and 10/3
Click to expand...

I have 3 - one on 10/1, one on 10/2, and one on 10/3. There are no criteria in the query so it is returning all from the table (there are 3 records in the table).
 
The idea here is to return one equipment id, with the most recent date. So the 10/1 date is not supposed to be there, because that equipment id has a more recent transaction date on 10/2.

The end result is that I want a report to show all equipment with its most recently recorded date. Thats why 10/1 cant show in the query results.
 
speakers_86 said:
Pretty much.
Click to expand...


Oh, I didnt realize Paul was refering to him being save. When i said pretty much, I was refering to me being saved! I didnt realize that when I quoted that what it was sounding like.
 
Okay, that would explain your first post. You can't have your cake and eat it too. Because all of the fields are not exactly the same in each record (different To location, etc.) it can't return only a Max for the one equipment ID UNLESS you do it first with that for ONLY the equipmentID and Date and then use that query in another query to pull the rest of the data.
 
If the subquery only returns equipmentid and date, then there is no field to link this to the transaction table. I see no way to pull any other information. So I am left with equipmentid, date, and no to location. Knowing when something moved isnt enough, I need to know where it went to, otherwise I cant make any reports.
 
speakers_86 said:
If the subquery only returns equipmentid and date, then there is no field to link this to the transaction table. I see no way to pull any other information. So I am left with equipmentid, date, and no to location. Knowing when something moved isnt enough, I need to know where it went to, otherwise I cant make any reports.
Click to expand...

What are you talking about - no field to link to??? You can link to the EquipmentID - HELLO!
 
I replied once, but it dissappeared, here it is again.

If I create a qry with just equipmentid and date, there is no way to link that to the transaction table. Knowing when something moved isnt enough for me to create my reports, I need to know where it went to.

edit-oh, two pages :)
 
Last edited:
speakers_86 said:
I replied once, but it dissappeared, here it is again.

If I create a qry with just equipmentid and date, there is no way to link that to the transaction table. Knowing when something moved isnt enough for me to create my reports, I need to know where it went to.
Click to expand...

You have the equipment ID field, right? So you can do another query up with that query in it LINKED (by equipment ID) to the equipment table and then you can also link any other tables as well.
 
I just tried linking equipment id, heres what happened, as i expected.

Equipmentid date to
1 10/2 31
1 10/2 30
2 10/3 30


This says that equipment id is in 2 places at once on 10/2, which is erroneus. The first record in the results should not be there. Equipment id 1 was in the to location 31 on 10/1, not 10/2. This is due to the max function.
 
You must log in or register to reply here.

Similar threads

C
Crosstab query that uses Form field as criteria
Replies
14
Views
1,123
Ken Sheridan
Ken Sheridan
essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
1,414
essaytee
essaytee
lodmark
Solved Sorting with DISTINCT
Replies
19
Views
1,111
lodmark
lodmark
D
Max Query Join
2
Replies
21
Views
2,145
DPW88
D
E
Solved Union Query Average Grouped
Replies
5
Views
779
E9-Tech
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom