Too much info showing in a report need to better define query...help!?!

[Christina123]

I have a database that tracks the withdrawls and returns of shared assets.
I have a query that a report runs off of that is supposed to only show the most current withdrawl (based off of a yes/no check box in the order form and return forms). However, the report shows every time the item has ever been removed, any time the check box has been checked.

I think that if I set it so that if the return date is less than the order date, show the info.

Would this work? And if so how would I write that in the design veiw of a query in MS Acess for 2000 professional?

[zaidlig]

I have a database that tracks the withdrawls and returns of shared assets.
I have a query that a report runs off of that is supposed to only show the most current withdrawl (based off of a yes/no check box in the order form and return forms). However, the report shows every time the item has ever been removed, any time the check box has been checked.

I think that if I set it so that if the return date is less than the order date, show the info.

Would this work? And if so how would I write that in the design veiw of a query in MS Acess for 2000 professional?

It would help if you provide the SQL from your current query. Essentially you want to use the group by function where the query will look at a group of records to select and report a single record based on the date order and any criteria you add.

[Christina123]

It would help if you provide the SQL from your current query. Essentially you want to use the group by function where the query will look at a group of records to select and report a single record based on the date order and any criteria you add.

The SQL in my current query is:
SELECT tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.OrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM ((tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN tblReturn ON tblEmployee.GlobalID = tblReturn.GID) INNER JOIN ((tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) INNER JOIN tblReturnInfo ON tblItemSpecifics.Serial_Number = tblReturnInfo.Serial_Number) ON (tblReturn.ReturnNumber = tblReturnInfo.Return_Number) AND (tblOrder.OrderNumber = tblOrderInfo.Order_Number)
GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.OrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number, tblItemSpecifics.Removed
HAVING (((tblItemSpecifics.Removed)=True));

I don't know how to read SQL (yet, working on it). So I'm not sure if this will help.

[zaidlig]

The SQL in my current query is:
SELECT tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.OrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM ((tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN tblReturn ON tblEmployee.GlobalID = tblReturn.GID) INNER JOIN ((tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) INNER JOIN tblReturnInfo ON tblItemSpecifics.Serial_Number = tblReturnInfo.Serial_Number) ON (tblReturn.ReturnNumber = tblReturnInfo.Return_Number) AND (tblOrder.OrderNumber = tblOrderInfo.Order_Number)
GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.OrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number, tblItemSpecifics.Removed
HAVING (((tblItemSpecifics.Removed)=True));

I don't know how to read SQL (yet, working on it). So I'm not sure if this will help.

I see you are already using Group By. So at the field that contains the withdrawl date change it from Group By to Max. And that should give you the last date the item was checked instead of the all the checked dates.

[Christina123]

I see you are already using Group By. So at the field that contains the withdrawl date change it from Group By to Max. And that should give you the last date the item was checked instead of the all the checked dates.

Your solution worked! Thanks a bunch!

[Christina123]

Same problem, different twist.

In the database each person who can remove a tool has a unique ID number. My earlier issue that showed every time an item was removed has been fixed if it is only one person who has ever removed the item.

Now if I take the item out and then someone else takes the item out after I bring it back, it shows the last time I took it out and the last time anyone else took it out.
This is the code:

SELECT tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, Max(tblOrder.OrderDate) AS MaxOfOrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number, tblItemSpecifics.Removed
HAVING (((tblItemSpecifics.Removed)=True));