Sort, group and sort again kind of problem. (1 Viewer)

KingBroil

Registered User
Joined
Oct 3, 2012
Messages
41
In my database, I have a table I use to track the ins and outs of the inventory items. Fields are as follow : [N°](key), [Project], [#série], [ApprouvéPar], [SortiDate], [SortiPar], [RetourDate], [RetourPar]
So, each record stores the item serial#, where it went, by whom, when it came back, if it is back. Now, I have some code that when no return date is detected, it toggle a boolean field to « out » in the main inventory table. I also have a report that can display a list of the items that are « out » of the inventory.
What I would like to do now, is to add a control to this report that would display on which project every « out » item was assigned last. I tried with a query with « totals » and stuff but couldn’t get it right, but almost. I basically need to isolate the records by serial#, then select the most recent date associated with that serial# then get the project# associated with this date/serial# which by now would be down to one record in this table. I use a similar logic in my code for the « In/out » status, where I have a query that regroup the items based on the serial# curently selected and the code checks if the last [SortiDate] has a date in it and change the « in/out » status accordingly. For the project assignation, the problem seem to be that it needs to do the same but for all serial# at once.
I think this might be confusing a bit but I just need some hint or a direction to look for now.
I appreciate the time that all you knowledgeable people are taking for guys like me that are stubborn enough to try to learn on their own, I learned a lot here so far.

Guillaume
 
Last edited:

informer

Registered User
Joined
May 25, 2016
Messages
75
Hi KingBroil

Just a question, when an item is back, do you reset the SortiDate = ""?

If you do this, it's easy to select the no retrun items

Code:
SELECT * FROM table WHERE SortiDate = ""
could you send a screen capture of your database model and give an example about your need with explicit data?
 
Last edited:

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom