Hi TRB_NV,
Sorry for delay but my main system went down
(or more accurately, my "free" Logitech mouse apparently
overpowered the PS/2 chip on my motherboard).
I take it then that quantities are not type Long,
but instead Single or Double?
You just cannot do simple arithmetic accurately
with type double fields in Access.
Please read Luke Chung's excellent paper on FMS website:
"When Access Math Doesn't Add Up"
http://www.fmsinc.com/tpapers/math/index.html
I might use CDec function in original query
so the "Sum's" will be accurate.
SELECT
Orders.Date As OrderDate,
Orders.State, "Aluminum" As Metal,
CDec(Orders.Aluminum & 0) As Qty
FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Copper",
CDec(Orders.Copper & 0) FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Iron",
CDec(Orders.Iron & 0) FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Tin",
CDec(Orders.Tin & 0) FROM Orders;
Then in report, you have several options
with how you display the "Sum's".
- set the format for each textbox to "#.#"
- use Round function
- use following to round to nearest tenths
in source for your [Qtr x] textboxes
=Int([Qtr x]*10 + .5)/10
Example:
Qtr1=CDec(1565303.125 & 0)
?Int(Qtr1 * 10 +.5)/10
1565303.1
none of the above will change 97.000091234 to 97.1 though.
I assume you were expecting 97.1xxx but Access summation
(without changing to Decimal) gave different result.
In your report it should be simple to add a "txtTotal" textbox
and just do your summations there for your "Total".
As for the summary, I might just create another query
where you only Group By Metal
TRANSFORM Nz(Sum([Qty]),0) AS QtrSum
SELECT qryNormOrders.Metal
FROM qryNormOrders
GROUP BY qryNormOrders.Metal
PIVOT "Qtr " & Format([OrderDate],"q")
In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
Then create a subreport based on this query
and put this subreport in the original report's
ReportFooter.
Please respond back if I was not clear about something.
Good luck,
Gary Walter
TRB_NV said:
I've got a bit of a problem with numbers like 97.1 being displayed as
97.000091234 in the final data output. Is there a way to clean up the data
or force a fixed decimal? I've checked the orders table and the data is
clean/good in there.
Also, is there a quick way to put a Total's column right after 4th Quarter
and a Metals Total's at the bottom?
State Metal Qtr 1 Qtr 2 Qtr 3 Qtr 4 Total
CA Aluminum 2100 0 0 0 2100
CA Copper 4600 0 0 0 4600
CA Iron 7300 0 0 0 7300
CA Tin 2600 0 0 0 2600
NV Aluminum 0 500 0 0 500
NV Copper 0 1500 0 0 1500
NV Iron 0 4500 0 0 4500
NV Tin 0 2900 0 0 2900
--------------------------------------------------------------------
Total Aluminum 2100 500 0 0 2600
Total Copper 4600 1500 0 0 6100
Total Iron 7300 4500 0 0 11800
Total Tin 2600 2900 0 0 5500
Thanks
Gary Walter said:
TRB_NV said:
I'm trying to write a SQL query in Access that can later be used in a
quarterly report. Here's the basics of what I'd to do:
Orders table has the following fields:
Date, State, Aluminum, Copper, Iron, Tin
01/01/04, CA, 1200, 2600, 1900, 800
01/15/04, CA, 900, 2000, 5400, 1800
04/02/04, NV, 500, 1500, 4500, 2900
Q1-Aluminum = Total Aluminum for Q1 in State of CA
Q1-Aluminum = Total Aluminum for Q1 in State of NV
ETC...
Report layout (sorted ascending by state and metal):
State, Q1-Aluminum, Q2-Aluminum, Q3-Aluminum, Q4-Aluminum
Q1-Copper, Q2-Copper, Q3-Copper, Q4-Copper
Q1-Iron, Q2-Iron, Q3-Iron, Q4-Iron
Q1-Tin, Q2-Tin, Q3-Tin, Q4-Tin
Output results:
State, Q1, Q2, Q3, Q4
CA, 2100, 0, 0, 0
CA, 4600, 0, 0, 0
CA, 7300, 0, 0, 0
CA, 2600, 0, 0, 0
NV, 0, 500, 0, 0
NV, 0, 1500, 0, 0
NV, 0, 4500, 0, 0
NV, 0, 2900, 0, 0
Hi,
I might suggest normalizing your
orders with following "qryNormOrders"
(or redesign the table):
SELECT
Orders.Date As OrderDate,
Orders.State, "Aluminum" As Metal,
Orders.Aluminum As Qty
FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Copper", Orders.Copper FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Iron", Orders.Iron FROM Orders
UNION ALL
SELECT Orders.Date, Orders.State, "Tin", Orders.Tin FROM Orders;
Then the following crosstab
TRANSFORM Nz(Sum([Qty]),0) AS QtrSum
SELECT qryNormOrders.State, qryNormOrders.Metal
FROM qryNormOrders
GROUP BY qryNormOrders.State, qryNormOrders.Metal
PIVOT "Qtr " & Format([OrderDate],"q")
In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
would produce from your example data:
State Metal Qtr 1 Qtr 2 Qtr 3 Qtr 4
CA Aluminum 2100 0 0 0
CA Copper 4600 0 0 0
CA Iron 7300 0 0 0
CA Tin 2600 0 0 0
NV Aluminum 0 500 0 0
NV Copper 0 1500 0 0
NV Iron 0 4500 0 0
NV Tin 0 2900 0 0
All that would be needed then would be criteria clause
to limit to a specific year.
Please respond back If I have misunderstood.
Good luck,
Gary Walter
