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

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

P: 22
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:
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 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
  3. WHERE (((tblItemSpecifics.Removed)=True))
  4. GROUP BY tblOrder.GID, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblOrder.Supervisor, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number;
Jan 24 '08 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:
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 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
  3. WHERE (((tblItemSpecifics.Removed)=True))
  4. 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.
Jan 25 '08 #2

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

puppydogbuddy
Expert 100+
P: 1,923
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 #.
Jan 25 '08 #4

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

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 25 '08 #6

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

puppydogbuddy
Expert 100+
P: 1,923
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??
Jan 28 '08 #8

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

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 30 '08 #10

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

Post your reply

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