wgraphics
asked on
How to Modify the Recordsource
Hello-
Access 2002 Report
I have a report that has multiple detail lines. I have it so that only the top line is visible (which is what I want) BUT when I go to sum, I am getting the total for all of them.
My report is bit more complex but here is an easier example:
Each product (PrA and PrB) has 3 items. The overall ship charge for PrAis 2.00, yet the detail shows 2.00 for each item, and the overall ship charge for PrB is 3.00, yet the detail shows 3.00 for each item.
When I go to total, I want the total to be 5.00, not 15.00.
PrA Item ShipChge
1 2.00 (visible)
2 2.00 (hidden)
3 2.00 (hidden)
PrB Item ShipChge
1 3.00 (visible)
2 3.00 (hidden)
3 3.00 (hidden)
Can anyone explain to me how to modify the report so I only get the sum of the first line of each item?
Thanks!
Access 2002 Report
I have a report that has multiple detail lines. I have it so that only the top line is visible (which is what I want) BUT when I go to sum, I am getting the total for all of them.
My report is bit more complex but here is an easier example:
Each product (PrA and PrB) has 3 items. The overall ship charge for PrAis 2.00, yet the detail shows 2.00 for each item, and the overall ship charge for PrB is 3.00, yet the detail shows 3.00 for each item.
When I go to total, I want the total to be 5.00, not 15.00.
PrA Item ShipChge
1 2.00 (visible)
2 2.00 (hidden)
3 2.00 (hidden)
PrB Item ShipChge
1 3.00 (visible)
2 3.00 (hidden)
3 3.00 (hidden)
Can anyone explain to me how to modify the report so I only get the sum of the first line of each item?
Thanks!
ASKER
Hi fyed,
Not that this means anything really, but the query is quite a monster with custom tables, tables from weekly downloads from outside sources and tables from company software.
I'll do my best at explaining, bear with me-
The orders (Products)have multiple line items that the system adds charges to (each line) and we are responsible for making it show/total only the first line. When the data gets dumped down into from the web, it comes in with the shipping charges attached to each line.
So I guess what I am saying is- by the time we get it- the shipping charges are already attached to each line item, and whether we like it or not, have to figure out how to show the charge only once.
My above example showed only 3 lines but some of our orders have 10 lines, some only 1 line, 6 lines, it varies depending on the order.
Thanks-
Not that this means anything really, but the query is quite a monster with custom tables, tables from weekly downloads from outside sources and tables from company software.
I'll do my best at explaining, bear with me-
The orders (Products)have multiple line items that the system adds charges to (each line) and we are responsible for making it show/total only the first line. When the data gets dumped down into from the web, it comes in with the shipping charges attached to each line.
So I guess what I am saying is- by the time we get it- the shipping charges are already attached to each line item, and whether we like it or not, have to figure out how to show the charge only once.
My above example showed only 3 lines but some of our orders have 10 lines, some only 1 line, 6 lines, it varies depending on the order.
Thanks-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff,
Your approach is working great- except my grand total keep showing up as 0. (Not sure if this is relevant, but the very last order happens to have a shipping charge of zero).
The other day I did a similar runsum on a different column, which was successful. I attempted it on this shipping charge column and got the same result- grand total of zero.
I do have running sum set to Overall.
I will continue to work on this 0.00 grand total issue.
Thanks-
Your approach is working great- except my grand total keep showing up as 0. (Not sure if this is relevant, but the very last order happens to have a shipping charge of zero).
The other day I did a similar runsum on a different column, which was successful. I attempted it on this shipping charge column and got the same result- grand total of zero.
I do have running sum set to Overall.
I will continue to work on this 0.00 grand total issue.
Thanks-
ASKER
Does runsum resets at 0?
If so, any suggestions for a workaround for when that happens?
Thank you!
If so, any suggestions for a workaround for when that happens?
Thank you!
ASKER
Jeff and fyed thanks for your time. The runsum function works great on all my other columns, but it just doesn't want to work with this summary.
The summary you gave as an example looks good. For some reason though, those ship charges do not want to runsum and I cannot get a total at the bottom either.
I am going to close out this question and perhaps I will revisit again next week. I am out of the office the rest of the week. Anyway, as always, your help is very much appreciated.
Megan
The summary you gave as an example looks good. For some reason though, those ship charges do not want to runsum and I cannot get a total at the bottom either.
I am going to close out this question and perhaps I will revisit again next week. I am out of the office the rest of the week. Anyway, as always, your help is very much appreciated.
Megan
Post a sample of your database and I can take a look
ASKER
Thanks Jeff- I will try to do that. Have meetings and such so may not get to it today. Thanks!
ASKER
Ok I was able to do it quick. I just added a sample to the mdb you made previous. Just want one shipping charge per orderID. For example order 2493-1809 has 46.9 ten times- only want to see it once and only want to add it once in a runsum. Thanks-
Access-EEQ-27381688-Sum-Only-Fir.mdb
Access-EEQ-27381688-Sum-Only-Fir.mdb
<Just want one shipping charge per orderID.>
Then literally move both fields from the detail section into the group header.
Or...
Make a query like this:
SELECT EOM_LineItemReport.[Order ID] AS [EOM_LineItemReport_Order ID], EOM_OrdersImport.Shipping
FROM EOM_LineItemReport INNER JOIN EOM_OrdersImport ON EOM_LineItemReport.[Order ID] = EOM_OrdersImport.[Order ID]
GROUP BY EOM_LineItemReport.[Order ID], EOM_OrdersImport.Shipping;
... and just use the wizard to create a basic report (No Grouping needed)
This will all depend on what you want to do with Order 2493-1900, which has a value of 0 and 16.49
(This is not consistent with your original post that *All* vales will be the same)
Then literally move both fields from the detail section into the group header.
Or...
Make a query like this:
SELECT EOM_LineItemReport.[Order ID] AS [EOM_LineItemReport_Order ID], EOM_OrdersImport.Shipping
FROM EOM_LineItemReport INNER JOIN EOM_OrdersImport ON EOM_LineItemReport.[Order ID] = EOM_OrdersImport.[Order ID]
GROUP BY EOM_LineItemReport.[Order ID], EOM_OrdersImport.Shipping;
... and just use the wizard to create a basic report (No Grouping needed)
This will all depend on what you want to do with Order 2493-1900, which has a value of 0 and 16.49
(This is not consistent with your original post that *All* vales will be the same)
ASKER
Hi Jeff,
My fault- Order 2493-1899 and 2493-1900 are "new" orders that should not be in there. I should have filtered it to show "Completed" orders only. Once these orders are fulfilled, the shipping charges willl turn from 0 to a cost.
I am able to get the one shipping charge per order (from your instructions above in comment #3). What I cannot get it to do though, is runsum and total at the bottom. When I set it up, the runsum function doesn't appear to work and I get an incorrect total as well.
I have 2 other columns in the original report, though, that I am able to runsum and total fine.
Can you think of anything that is prohibiting me from runsumming and totaling this column? I attached the file again, with an example of what it looks like when I try to runsum/total it.
Thanks again, Meg
Access-EEQ-27381688-Sum-Only-Fir.mdb
My fault- Order 2493-1899 and 2493-1900 are "new" orders that should not be in there. I should have filtered it to show "Completed" orders only. Once these orders are fulfilled, the shipping charges willl turn from 0 to a cost.
I am able to get the one shipping charge per order (from your instructions above in comment #3). What I cannot get it to do though, is runsum and total at the bottom. When I set it up, the runsum function doesn't appear to work and I get an incorrect total as well.
I have 2 other columns in the original report, though, that I am able to runsum and total fine.
Can you think of anything that is prohibiting me from runsumming and totaling this column? I attached the file again, with an example of what it looks like when I try to runsum/total it.
Thanks again, Meg
Access-EEQ-27381688-Sum-Only-Fir.mdb
1. Now can you see why we all stated in the beginning that source data like this is a nightmare to work with...
2. You have a lot of fields in your table defined as "Text" when they should be defined explicitly as other types.
This is why your running sums are not working... The Shipping field is defined as *Text* in the source table(s), when it should be defines as "Currency"
So if you fix this basic flaw, and use the SQL I posted, this all becomes simple, ...and only =Sum(Shipping) is need to get the Grand Total.
But you *Really* need to go back and re-evaluate your table design here.
There are a lot of other issues there that that will cause you more frustration in the future if left un-addressed...
Sample attached
JeffCoachman
Access-EEQ-27381688-Sum-Only-Fir.mdb
2. You have a lot of fields in your table defined as "Text" when they should be defined explicitly as other types.
This is why your running sums are not working... The Shipping field is defined as *Text* in the source table(s), when it should be defines as "Currency"
So if you fix this basic flaw, and use the SQL I posted, this all becomes simple, ...and only =Sum(Shipping) is need to get the Grand Total.
But you *Really* need to go back and re-evaluate your table design here.
There are a lot of other issues there that that will cause you more frustration in the future if left un-addressed...
Sample attached
JeffCoachman
Access-EEQ-27381688-Sum-Only-Fir.mdb
ASKER
Jeff THANK YOU! Yes that worked.
I appreciate the time you took and I very much appreciate your input and expertise regarding the table design. I look into it and will be sure to mention this to those who are requesting the reports. Have a good week. Meg
I appreciate the time you took and I very much appreciate your input and expertise regarding the table design. I look into it and will be sure to mention this to those who are requesting the reports. Have a good week. Meg
Meg,
No sweat,...
I enjoy working with people who can work independently.
;-)
Just to recap what was done....:
The query does the grouping...
This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report.
With the fields set to the correct datatype, it is a simple matter to get the running sum, or just the grand total.
But again, the kicker here is that:
1. The other datatypes must also be corrected (ex.: Date fields will also cause you grief if they are not true dates)
2. If the data is ever "Normalized" the report and query I used here will be irrelevant, and you will have to recreate the report, grouping, sums, Totals, etc form scratch with the new data.
Some final notes,
1. ...technically, the running sum (and the Grand total) is based on the "Field Name", not the "control name" as you were trying to use.
2. Investigate adopting a consistent "Naming convention":
http://www.xoc.net/standards/rvbanc.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention
Glad I was able to help
;-)
Jeff
No sweat,...
I enjoy working with people who can work independently.
;-)
Just to recap what was done....:
The query does the grouping...
This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report.
With the fields set to the correct datatype, it is a simple matter to get the running sum, or just the grand total.
But again, the kicker here is that:
1. The other datatypes must also be corrected (ex.: Date fields will also cause you grief if they are not true dates)
2. If the data is ever "Normalized" the report and query I used here will be irrelevant, and you will have to recreate the report, grouping, sums, Totals, etc form scratch with the new data.
Some final notes,
1. ...technically, the running sum (and the Grand total) is based on the "Field Name", not the "control name" as you were trying to use.
2. Investigate adopting a consistent "Naming convention":
http://www.xoc.net/standards/rvbanc.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention
Glad I was able to help
;-)
Jeff
ASKER
Jeff,
haha yes, one woman show here...
"The query does the grouping...This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report." Ah, I see....thanks for the explanation.
re: dates- yes I changed them when I changed the currency. I am going to go through more tables tomorrow and double check datatypes.
It is quite a messy monster that is piece mealed together over the years and I am the lucky one that gets to "take it over".
Thanks for the links. I hope to read them in the AM with my morning coffee- now that I can stop stressing about that ship calc (for the time being anyway!) Thanks!
haha yes, one woman show here...
"The query does the grouping...This means that in the report, each "Group" is now a "Record".
Thus no Grouping was needed in the report." Ah, I see....thanks for the explanation.
re: dates- yes I changed them when I changed the currency. I am going to go through more tables tomorrow and double check datatypes.
It is quite a messy monster that is piece mealed together over the years and I am the lucky one that gets to "take it over".
Thanks for the links. I hope to read them in the AM with my morning coffee- now that I can stop stressing about that ship calc (for the time being anyway!) Thanks!
What is the SQL string that you have for the report?