Y
Yam84
Hello:
I am have created a query which tells the user where a panel is at a
certain time. I have two queries that I combine to get my results.
the first query results in the return of all times a panel has moved.
SELECT [Project Information].jobNumber, [Project
Information].jobName, [Yard Location].locationFrom, [Yard
Location].locationTo, [Yard Location].panelID, [Yard
Location].damage, [Yard Panel Number].panelNumber, [Yard
Location].locDate, [Yard Location].completionTime
FROM [Project Information] INNER JOIN ([Yard Panel Number] INNER JOIN
[Yard Location] ON [Yard Panel Number].panelID = [Yard
Location].panelID) ON [Project Information].jobNumber = [Yard Panel
Number].jobNumber
WHERE ((([Project Information].jobNumber)=28199))
ORDER BY [Project Information].jobNumber, [Yard Location].panelID;
The second query results in the return of the most recent date and
time of the moves.
SELECT [Yard Panel Number].panelNumber, Max([Yard Location].locDate)
AS MaxOflocDate, Max([Yard Location].completionTime) AS
MaxOfcompletionTime, [Yard Panel Number].jobNumber, [Yard
Location].panelID
FROM [Yard Panel Number] INNER JOIN [Yard Location] ON [Yard Panel
Number].panelID = [Yard Location].panelID
WHERE ((([Yard Panel Number].panelID)=[Yard Location].[panelID]))
GROUP BY [Yard Panel Number].panelNumber, [Yard Panel
Number].jobNumber, [Yard Location].panelID, [Yard Panel
Number].panelNumber, [Yard Location].panelID
HAVING ((([Yard Panel Number].jobNumber)=28199));
I use this query which is a combination of the first and second
queries to determine the most recent date and time of the move.
My problem is this: I expect that there will be as many records as
the second query returns (46) in my third query, which combines 1 and
2. I have noticed that some results are non-existent. Additionally,
I use the function MAX to determine date and time, however I noticed
that (in the second query) as opposed to returning the max dt and
time for a set of records per panelID, it returns the most recent
date and pairs it with the most recent time in the set of records.
Panel#/Date/Time/Loc1/Loc2
3/12-1-08/2:00PM/A/B
3/12-2-08/12:00PM/B/V
5/12-2-08/1:30PM/V/D
MY RESULTS (Incorrect):
3/12-2-08/2:00PM/B/V---The largest date and time are paired
incorrectly
5/12-2-08/1:30PM/V/D
MY RESULTS (Correct):
3/12-2-08/12:00PM/B/V
5/12-2-08/1:30PM/V/D
How would I go about pairing my data to correctly reflect the date
and time records that belong together?
I am have created a query which tells the user where a panel is at a
certain time. I have two queries that I combine to get my results.
the first query results in the return of all times a panel has moved.
SELECT [Project Information].jobNumber, [Project
Information].jobName, [Yard Location].locationFrom, [Yard
Location].locationTo, [Yard Location].panelID, [Yard
Location].damage, [Yard Panel Number].panelNumber, [Yard
Location].locDate, [Yard Location].completionTime
FROM [Project Information] INNER JOIN ([Yard Panel Number] INNER JOIN
[Yard Location] ON [Yard Panel Number].panelID = [Yard
Location].panelID) ON [Project Information].jobNumber = [Yard Panel
Number].jobNumber
WHERE ((([Project Information].jobNumber)=28199))
ORDER BY [Project Information].jobNumber, [Yard Location].panelID;
The second query results in the return of the most recent date and
time of the moves.
SELECT [Yard Panel Number].panelNumber, Max([Yard Location].locDate)
AS MaxOflocDate, Max([Yard Location].completionTime) AS
MaxOfcompletionTime, [Yard Panel Number].jobNumber, [Yard
Location].panelID
FROM [Yard Panel Number] INNER JOIN [Yard Location] ON [Yard Panel
Number].panelID = [Yard Location].panelID
WHERE ((([Yard Panel Number].panelID)=[Yard Location].[panelID]))
GROUP BY [Yard Panel Number].panelNumber, [Yard Panel
Number].jobNumber, [Yard Location].panelID, [Yard Panel
Number].panelNumber, [Yard Location].panelID
HAVING ((([Yard Panel Number].jobNumber)=28199));
I use this query which is a combination of the first and second
queries to determine the most recent date and time of the move.
My problem is this: I expect that there will be as many records as
the second query returns (46) in my third query, which combines 1 and
2. I have noticed that some results are non-existent. Additionally,
I use the function MAX to determine date and time, however I noticed
that (in the second query) as opposed to returning the max dt and
time for a set of records per panelID, it returns the most recent
date and pairs it with the most recent time in the set of records.
Panel#/Date/Time/Loc1/Loc2
3/12-1-08/2:00PM/A/B
3/12-2-08/12:00PM/B/V
5/12-2-08/1:30PM/V/D
MY RESULTS (Incorrect):
3/12-2-08/2:00PM/B/V---The largest date and time are paired
incorrectly
5/12-2-08/1:30PM/V/D
MY RESULTS (Correct):
3/12-2-08/12:00PM/B/V
5/12-2-08/1:30PM/V/D
How would I go about pairing my data to correctly reflect the date
and time records that belong together?