Solved Max qry in form (1 Viewer)

[Jomat]

I have an inventory table that contains some inventories and a sub table that contains check in and check out records.

I'm trying to create a qry that will open a form to show the latest inventories that have been checked out but not checked in.

I do have the dates and time of check out with unique id's.

Does any one have any ideas on how to do this?

Thank you ahead of time.

 

[CarlettoFed]

Maybe if you attach an example file with a minimum of data it's easier to understand and then help you.

 

[Jomat]

That's a better idea. I will do that.

 

[arnelgp]

if you have CheckOutDate and CheckInDate Date fields on same row:

select * from yourTable Where CheckOutDate = DMax("CheckOutDate", "yourTable", "ItemID=" & [ItemID] & " And IsNull(CheckInDate)")

or if you are recording it, either of the CheckOutDate or CheckInDate can be blank:

SELECT yourTable.*
FROM yourTable Where (CheckInDate Is Null) And CheckOutDate > DMax("CheckInDate","yourTable", "ItemID=" & [ItemID]);

 

[Jomat]

I've uploaded a sample db. I'm looking to not include records that the equipment have been returned.

edited:
Return a list of only equipments that are checked out and has not been checked in.
I tried the max and last in a qry but can't seems to get it to work right.

 

[Jomat]

if you have CheckOutDate and CheckInDate Date fields on same row:

select * from yourTable Where CheckOutDate = DMax("CheckOutDate", "yourTable", "ItemID=" & [ItemID] & " And IsNull(CheckInDate)")

or if you are recording it, either of the CheckOutDate or CheckInDate can be blank:

SELECT yourTable.*
FROM yourTable Where (CheckInDate Is Null) And CheckOutDate > DMax("CheckInDate","yourTable", "ItemID=" & [ItemID]);

 

[Jomat]

It's from a combo box.

 

[arnelgp]

see Query1.

 

[CarlettoFed]

create the query qryLastMovements

SELECT INVENTORY_CHECK.LINKEDID, Max(INVENTORY_CHECK.ICDATE) AS MaxDiICDATE, Max(INVENTORY_CHECK.ICTIME) AS MaxDiICTIME FROM INVENTORY_CHECK GROUP BY INVENTORY_CHECK.LINKEDID;

​

create the query qryInventoryOut

SELECT INVENTORY_CHECK.ID, INVENTORY_CHECK.LINKEDID, INVENTORY_CHECK.ICDATE, INVENTORY_CHECK.ICTIME, INVENTORY_CHECK.INOUT FROM INVENTORY_CHECK INNER JOIN qryLastMovements ON (INVENTORY_CHECK.ICTIME=[qryLastMovements].MaxDiICTIME) AND (INVENTORY_CHECK.ICDATE=[qryLastMovements].MaxDiICDATE) AND (INVENTORY_CHECK.LINKEDID=[qryLastMovements].LINKEDID) WHERE (((INVENTORY_CHECK.INOUT)="Check Out"));

 

[Jomat]

I will try that.

 

[Jomat]

see Query1.

Got it. I will test it.

 

[CarlettoFed]

Queries have changed.

 

[Jomat]

Queries have changed.

Thank you.

 

[Jomat]

see Query1.

Your solution worked perfect. Only the records that were needed are pulled and added into a single line.
1. Can you help explain the sql statement that you used?
2. If I'm reading it right, you created the table T because you did not want to change the original records?
3. The latest check out time must be greater then the check in time. I purposely changed for item #3 to a lesser time and to check out. No records shows. The time field will have to be auto populated and locked to prevent wrong inputs?
I've included an updated db. I've changed the query1 to query2. I placed the new query2 into a form to view. I've also added a macro to the ID, so that if I click on the ID, it will open the original records in another new update form to update or check in the item. When you get a chance please see the new db and give me any feed backs on whether what I changed is a good idea.
I don't quite understand the sql statement but I will study it further.
Thank you.

 

[Jomat]

Queries have changed.

Your queries worked the same as arnelgp. One of the qry must be named qryLastMovements. This query I understand, you took the max. I have to study them because I don't understand the how the second qry works to display the latest check out that doesn't have a check in yet.
Thank you.

EDITED:
I've upload the changes to the sample db that I have done just incase it would helps someone else.
The item #6 will not show. I purpose did this to test when the wrong time is inputted into the time field that is less then the prior. No correction is needed because new check out time should be greater then the others.
Thank you.

 

[arnelgp]

1. Can you help explain the sql statement that you used?

There is a sub-Query (inner Select statement).
As you can see I use "As T", to Name the inner query.
so T there is but a name to the sub-query.

there is an explanation here:

SQL Subqueries - w3resource

 

[Jomat]

There is a sub-Query (inner Select statement).
As you can see I use "As T", to Name the inner query.
so T there is but a name to the sub-query.

there is an explanation here:

SQL Subqueries - w3resource

Got it. Sub query is what it's called. Similarly sub macro to macro. Keyword ia probably nested. Thank you.