473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
10 1312
puppydogbuddy
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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
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
1,923 Expert 1GB
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
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

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

Similar topics

2
by: Hohn Upshew | last post by:
I need some help to build a report enumerating the products in descending order depending on the sum of liters. In this way i can view the top products sold for a given period.But i fail to do...
3
by: RC | last post by:
I can't quite grasp the concept of creating custom reports depending upon what options a user picks on a Form. For example, the user clicks on a "Print Reports" button and a Form pops up. On the...
2
by: amith.srinivas | last post by:
Hi all, From a word macro in VBA, I am trying to create a report in access DB. The report is based on a query with a parameter. I am using Set rpt = Application.CreateReport rpt.RecordSource =...
5
by: David L. | last post by:
I'm running into a frustrating problem. ASP.NET 2.0 web application, against SQL'05, I'm trying to run a report against a 'table provider' (query) that excutes well within SQL MGT studio, and...
2
by: john | last post by:
My report is based on a query with user input . In my report I have a text box with the following code: =. This works when the query outcome is 1 or more records. But when the query output is zero...
11
by: radink | last post by:
Hey All, I have a report that I would like to show a word on based on if a field is checked in a form. For example. The form has a check box called Fee. If that is checked, I want the word Paid...
3
by: vstolmech513 | last post by:
Here goes nothing... I'm trying to make a report that shows what guys work on what crew, ie. day crew or night crew. For this selection I've got a form with a combo box with either a "D" or "N"...
5
by: Christina123 | last post by:
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...
1
by: eogyamfi | last post by:
i have subreport within the main report. The grand total for the main report is showing on the report. The grandtotal for the subreport is not showing even though on the subreport itself it shows...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.