Sum Strictly by Date

[Guest]

Have a single table which has daily data which I capture on a transaction
number basis that falls in 2 general categories. I might have several
transactions occur on a single date. Ideally, I would like to be able to
query the table and get transaction totals that occur on a single date by
category. The query result would show, for example, 1-1-06 Category A 600
Category B 325.

Data is captured via a single form and a record will show the following:

Date: 1-01-2006
Day: Mon
Trans#: 22033
CustNm: Woods
Hammer: 100
Chisel: 200
Saw: 100
Torquewr: 60
Sparkplg: 40
Filter: 25

Date: 1-01-2006
Day: Mon
Trans#: 22034
CustNm: Spicer
Hammer: 50
Chisel: 50
Saw: 100
Torquewr: 100
Sparkplg: 50
Filter: 50


The above named fields would have tally quantities by date by customer
transaction (which might be several)

The categories would total by "Carpentry" and "Automotive" per date on a
single line in the query. Carpentry and Automotive are not fields but
captions which I would use for the calculated daily totals.

What I'm getting in query result is several lines representing all the
transactions, but not breaking strictly on the date and showing single
category totals by date.

Would someone please help? Have BrainLock

 

[Guest]

hi,

Simply use Group By clauses in your query...

SELECT tbl_name.trans_date, tbl_name.category1, Sum(tbl_name.field1) AS
Category1Sum, Sum(tbl_name.field2) AS Category2Sum
FROM tbl_name
GROUP BY tbl_name.trans_date, tbl_name.category1;


Hope this helps,
geebee

 

[Guest]

Thanks much geebee,

However, failed to mention that there are 2 records in the given example
rather than 1 as indicated in the original post and am using Access 97.
Also, have tried groupby in the totals query.

Looking for totals field in query by date for "Carpentry" and totals field
on same line for "Automotive".

 

[Guest]

hi,

Do you mean there are 2 "tables" instead of 2 "records"?

If so, you could join them by a related column in each table. Perhaps join
the 2 tables by date, then you could expand your query to include the other
table.

Hope this helps,
geebee

 

[John Vinson]

What I'm getting in query result is several lines representing all the
transactions, but not breaking strictly on the date and showing single
category totals by date.

Are you using a Totals query, grouping by date and transaction?

Please open the query in SQL view and post it here. Clearly there's
something wrong with the query, but since we can't see the query, we
can't tell what!

John W. Vinson[MVP]

 

[Guest]

Here is what I have:


SELECT RS_Day_PerfT_T.Dt, Sum(RS_Day_PerfT_T.TorqueWr) AS SumOfTorqueWr,
Sum(RS_Day_PerfT_T.Sparkplg) AS SumOfSparkplg, Sum(RS_Day_PerfT_T.Filter) AS
SumOfFilter, Sum([TorqueWr]+[Sparkplg]+[Filter]) AS Expr1
FROM RS_Day_PerfT_T
GROUP BY RS_Day_PerfT_T.Dt;


SELECT RS_Day_PerfT_T.Dt, Sum(RS_Day_PerfT_T.Hammer) AS SumOfHammer,
Sum(RS_Day_PerfT_T.Chisel) AS SumOfChisel, Sum(RS_Day_PerfT_T.Saw) AS
SumOfSaw, Sum([Hammer]+[Chisel]+[Saw]) AS Expr1
FROM RS_Day_PerfT_T
GROUP BY RS_Day_PerfT_T.Dt;


FirstOfExpr1, First(RS_Dt_PerfT_Carpentry_Q.Expr1) AS FirstOfExpr11
FROM RS_Day_PerfT_T, RS_Date_PerfT_Automotive_Q INNER JOIN
RS_Dt_PerfT_Carpentry_Q ON RS_Date_PerfT_Automotive_Q.Dt =
RS_Dt_PerfT_Carpentry_Q.Dt
GROUP BY RS_Date_PerfT_Automotive_Q.Dt;


Created 2 queries to sum by category Carpentry and Automotive. Then use a
3rd query to summarize the the 1st and 2nd. Used 'First' to break in the
3rd.The figures now do not repeat. However, maybe there is a shorter way?

Thanks to geebee and John W. Vinson. You were a great help. your questions
and answers were excellent!!!!!!!! Much obliged