By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

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

P: 22
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?
Jan 11 '08 #1
Share this Question
Share on Google+
5 Replies


P: 45
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.
Jan 11 '08 #2

P: 22
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.
Jan 11 '08 #3

P: 45
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.
Jan 11 '08 #4

P: 22
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!
Jan 14 '08 #5

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, Max(tblOrder.OrderDate) AS MaxOfOrderDate, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number
  2. 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
  3. GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number, tblItemSpecifics.Removed
  4. HAVING (((tblItemSpecifics.Removed)=True));
Jan 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.