Dsum nightmare

[noop71]

Hi there!

I have a table (table1)
that table has several columns: company / Qty / date / ERT

I want to be able to run a query that will group all the records by Company and by date and provide the sum of QTY - as well as the cumulative totals for each company and date.

My query looks like this:
amonth: MONTH CODE
Group By
assending

Field 1: Alias: Company
Group by
Ascending

Field 2: QTY
Table1


Sum

Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]=" & [alias] & "")

I have the query set up - but my runtotal Dsum formula is giving me grief - specifically a #ERROR and I can't figure it out

I basically want it to look like this:

A company 15/Nov/2012 10 10
A Company 16/Nov/2012 10 20
B Company 12/Jan/2012 15 15
B Company 21/Mar/2012 5 20

is this even possible??

[June7]

If field is text type, need apostrophe delimiter:

Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]='" & [alias] & "'")

Not understanding the field [alias]. Where is this field?

Instead of DSum, try an aggregate (GROUP BY) query. Or build a report using Grouping and Sorting with aggregate calcs in group footer.

[noop71]

If field is text type, need apostrophe delimiter:

Field 3: Runtot: DSum("qty","table1","[month code]=" & [amonth] & " And [company]='" & [alias] & "'")

Not understanding the field [alias]. Where is this field?

Instead of DSum, try an aggregate (GROUP BY) query. Or build a report using Grouping and Sorting with aggregate calcs in group footer.

Thanks for the quick response

the "Alias" is the field name for the field Company (just like amonth is the field name for month code) - I wanted to seperate the field
name from the table name to avoid confusion.

I can't use a group by (or report footer) as this will sum the totals for me - what I need are cumulative totals for each incremental date
code for each company.

thanks for the correction of the text fields though - good catch - though it didn't solve the problem - still getting #error

[June7]

I don't understand any of this. I will have to review your file to analyse structure. If you want to provide, follow instructions at bottom of my post.