UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sub Query, Access 2010    
 
   
APJordaan
post Mar 14 2018, 07:21 AM
Post#1



Posts: 127
Joined: 2-January 13
From: Paarl, South Africa


Hi there
I am working on a tyre stock movement db. I have two linked tables TyreMovement and MovementDetails(attached is a pdf of the relationships). MovementDetails has the transaction details for each movement of each tyre on the system ie, receiving into store, fit to vehicle, remove from vehicle, etc. That then means that each tyre can have many movements. I am trying to write an query that will list only the tyres who's last "MoveTo" entry was "Store"(one of the values in the filed). If I could accomplish that I would the end up with a list of tyres currently in the store. I have looked up subqueries using the "Last" function, but cannot seem to get the correct results. Any ideas would be appreciated.
Thanks
Attached File(s)
Attached File  Report1.pdf ( 35.39K )Number of downloads: 13
 
Go to the top of the page
 
GroverParkGeorge
post Mar 14 2018, 08:14 AM
Post#2


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


Given the format in which the tables are provided as a PDF, it's a bit harder, but in general, here are some things to consider.

Last is seldom a useful concept in databases, when thinking about data in a table. Tables have NO inherent sort order, so "first" and "last" depend entirely on how you apply a sort order. And you do that within a query against the table. In other words, "Last" means the final record as it appears in the currently selected recordset. And that changes for every different sort you apply

In order to find "the most recent date for XXXX", you must use Max() as the aggregate and apply it to the field in which you are interested: Possibly, in your case, Max(MovementDetails.MovementDate) as LastMoveDate
This post has been edited by GroverParkGeorge: Mar 14 2018, 08:17 AM
Go to the top of the page
 
APJordaan
post Mar 14 2018, 08:36 AM
Post#3



Posts: 127
Joined: 2-January 13
From: Paarl, South Africa


Hi George
Thanks for the reply. I have attached a zipped copy of the db if you might want to have a look. It's an attempt to improve on an existing db, so I have just dumped most of the data into it and normalized the whole thing, and now I'm trying to get it "up and running" again.
Anyway, I'll go and see if I have any better luck trying your suggestion.
Thanks

Attached File(s)
Attached File  Trail.zip ( 1.16MB )Number of downloads: 4
 
Go to the top of the page
 
LPurvis
post Mar 14 2018, 09:25 AM
Post#4


UtterAccess Editor
Posts: 16,295
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

The data itself is informative. You have the MovedDate, but there are apparently occasions where the same tyre is moved twice on the same date. You'd really want another field with which to determine order of event to a more granular level.

Other than that, as George says, you can Max date it, or Not Exists.
CODE
SELECT T.TyreIndividual_ID, D.ID_MovementDetails, D.MovedTo, D.MovementDate
FROM MovementDetails AS D INNER JOIN TyreMovement AS T ON D.ID_MovementDetails = T.MovementDetails_ID
WHERE D.MovedTo=1 AND Not Exists (SELECT 0 FROM TyreMovement T2 INNER JOIN MovementDetails D2 ON T2.MovementDetails_ID = D2.ID_MovementDetails WHERE T.TyreIndividual_ID = T2.TyreIndividual_ID AND D2.MovementDate > D.MovementDate)


Both shown in the returned version. (Made a separate query for the Max Date method to, hopefully, explain it more easily.)

Cheers
Attached File(s)
Attached File  Trail.zip ( 269.82K )Number of downloads: 2
 
Go to the top of the page
 
APJordaan
post Mar 14 2018, 10:10 AM
Post#5



Posts: 127
Joined: 2-January 13
From: Paarl, South Africa


Hi Leigh
Much appreciation for the feedback. That was what I was attempting, but unfortunately did not have it in the arsenal.
Regarding the movement of the tyres; frequently a smooth tyre would be moved to the store, but shortly thereafter, on the same day, it would be moved to the supplier to be retreaded.
That would then correspond to two entries, one for the tyre into the store and one out again. I need a list of the tyres that have gone in and haven't gone out again = Stock in store.
I'm not that familiar with subqueries so I'm still trying to get my head around the logic behind it and your input is just what I needed. Just a last question if you don't mind: could you please explain what you meant with an additional field to determine the order of events?
Anyway, I really appreciate the help given!!
Cheers
Go to the top of the page
 
LPurvis
post Mar 14 2018, 10:56 AM
Post#6


UtterAccess Editor
Posts: 16,295
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

When you have a date field that is going to be used to be used to establish order of events, but it only stores a date and not a time component, then you can't determine order any greater than the day level.
So it's possible that a movement might have seen a tyre in stock, but then moved out again on the same day and, because the date matches, it counts as there not being a movement after that. If you had a time component, you could make sure you have the very last movement event.
You might feel inclined to use an autonumber field in lieu of such a DateTime field (they should be generated in sequence). But that, at best, would give you theoretical order of entry - not necessarily order of event.

Cheers
Go to the top of the page
 
APJordaan
post Mar 16 2018, 03:19 AM
Post#7



Posts: 127
Joined: 2-January 13
From: Paarl, South Africa


Hi again Leigh
Sorry for the late reply, but I was involved in training yesterday and not close to my pc.
I now get what you meant by the extra field/sort order. My realtime situation doesn’t allow for a “timestamp” necessarily, but I’ll figure out another way to get the job doene.
Lastly I would just like to thank everyone for the valuable feedback.
Excellent job guys!!!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 11:45 PM