Too much info showing in a report run off of a query...part 2

[Christina123]

I have a report that runs off of a query. The report is supposed to show the items that are currently withdrawn. I had an issue with it showing every time an item had been withdrawn, even though it had been returned. So I grouped the date by max. This solves the issue if the item had only ever been withdrawn by one person. However if more than one person over 'history' has removed the item it shows the last date (max) that the item was withdrawn under that persons ID.

I just need it to show what is current. I have tried to make it so that if the order date is greater than the return date it won't show, but that didn't work and I went all crosseyed trying to figure it out. So...help?!?! I'm now using Access for MS Office Professional 2003.

Here 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 tblReturn ON tblEmployee.GlobalID = tblReturn.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
WHERE (((tblItemSpecifics.Removed)=True))
GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number;

[puppydogbuddy]

I have a report that runs off of a query. The report is supposed to show the items that are currently withdrawn. I had an issue with it showing every time an item had been withdrawn, even though it had been returned. So I grouped the date by max. This solves the issue if the item had only ever been withdrawn by one person. However if more than one person over 'history' has removed the item it shows the last date (max) that the item was withdrawn under that persons ID.

I just need it to show what is current. I have tried to make it so that if the order date is greater than the return date it won't show, but that didn't work and I went all crosseyed trying to figure it out. So...help?!?! I'm now using Access for MS Office Professional 2003.

Here 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 tblReturn ON tblEmployee.GlobalID = tblReturn.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number
WHERE (((tblItemSpecifics.Removed)=True))
GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number;

Christina123,
I think your easiest solution is to leave the query alone, and use the sorting and grouping tool in the report designer to Group By Serial # and MaxOfOrderDate before grouping by the employee.

[Christina123]

Christina123,
I think your easiest solution is to leave the query alone, and use the sorting and grouping tool in the report designer to Group By Serial # and MaxOfOrderDate before grouping by the employee.

I tried all of the grouping options, by date then serial, by serial then date etc...., but in each case I was shown the last time the item was taken out by each employee.

[puppydogbuddy]

I tried all of the grouping options, by date then serial, by serial then date etc...., but in each case I was shown the last time the item was taken out by each employee.

Now I am confused! You said that when you grouped by employee, the Max order date gave you the last time the item was taken out by each employee. So in your report, group and sort first by Serial# then by MaxOrderDate and eliminate the grouping by employee. That should give you the MaxOrderDate for each serial #.

[Christina123]

Now I am confused! You said that when you grouped by employee, the Max order date gave you the last time the item was taken out by each employee. So in your report, group and sort first by Serial# then by MaxOrderDate and eliminate the grouping by employee. That should give you the MaxOrderDate for each serial #.

Yup even when I do not group by employee, it still shows each time the item was taken out. Even if I remove the employee field and/or the supervisor field from the report it shows all of the times the item was taken out.

If I enter in an order today and use a date from last week, it won't use today's date as the order date, it will use the date that I enter in the order date field, won't it?

[puppydogbuddy]

Yup even when I do not group by employee, it still shows each time the item was taken out. Even if I remove the employee field and/or the supervisor field from the report it shows all of the times the item was taken out.

If I enter in an order today and use a date from last week, it won't use today's date as the order date, it will use the date that I enter in the order date field, won't it?

Christina,

Yes. Just out of curiousity, do the entries to the field/expression dialog box in the grouping and sorting tool dialog box look like the following? Make sure the expression for the max is preceded by an = sign. Also, make sure you have nothing in the Detail section. All the info to be displayed should be in the headers. Let me know what this looks like after you run the report..

Serial_Number Ascending
= Max(OrderDate) Ascending

and in the group properties dialog box , do you have:
for Serial_Number grouping:
Group on header= yes
Group footer = no
Group on = each value
Group interval = 1
Keep together = Yes

for MaxOfOrderDate grouping:
Group on header= yes
Group footer = no
Group on = each value
Group interval = 1
Keep together = Yes

[Christina123]

Christina,

Yes. Just out of curiousity, do the entries to the field/expression dialog box in the grouping and sorting tool dialog box look like the following? Make sure the expression for the max is preceded by an = sign. Also, make sure you have nothing in the Detail section. All the info to be displayed should be in the headers. Let me know what this looks like after you run the report..

Serial_Number Ascending
= Max(OrderDate) Ascending

and in the group properties dialog box , do you have:
for Serial_Number grouping:
Group on header= yes
Group footer = no
Group on = each value
Group interval = 1
Keep together = Yes

for MaxOfOrderDate grouping:
Group on header= yes
Group footer = no
Group on = each value
Group interval = 1
Keep together = Yes

In the Group/sort tool dialog box there are two fields that say MaxofOrderDate, but neither of them say =Max(OrderDate) . If I attempt to input that manually, I get an error that says "Cannot have aggregate function in ORDER BYclause (Int(Max([OrderDate]))\1). I don't know.. :( ... I don't get paid enought to do this...

[puppydogbuddy]

In the Group/sort tool dialog box there are two fields that say MaxofOrderDate, but neither of them say =Max(OrderDate) . If I attempt to input that manually, I get an error that says "Cannot have aggregate function in ORDER BYclause (Int(Max([OrderDate]))\1). I don't know.. :( ... I don't get paid enought to do this...

Sorry, use MaxofOrderDate. ....but why 2 lines??

[Christina123]

Sorry, use MaxofOrderDate. ....but why 2 lines??

I haven't the foggiest, that's what populates into the group/sort thing when I click on the button.

[puppydogbuddy]

I haven't the foggiest, that's what populates into the group/sort thing when I click on the button.

Christina,
Didn't you enter those expressions? How did they get there? Try deleting the second line that has MaxOfOrderDate in it.

PDB

[Christina123]

Christina,
Didn't you enter those expressions? How did they get there? Try deleting the second line that has MaxOfOrderDate in it.

PDB

I did not enter the expressions, I used the report wizard to create the report and I think it does all of that for me.