Calculated Field In Query

[Jason]

Greetings,

I have a query which gives me data similar to the
following:

Client Name Unit Number Sales
XXX Company 10 100,000
XXX Company 11 120,000
XXX Company 12 110,000
YYY Company 150 230,000
YYY Company 151 250,000
YYY Company 152 280,000
YYY Company 153 240,000
ZZZ Company 81 360,000
ZZZ Company 82 300,000

I would like to create another field (after sales) in the
query which would give me the following:

Client Name Unit Number Sales Total Sales By Client
XXX Company 10 100,000 330,000
XXX Company 11 120,000 330,000
XXX Company 12 110,000 330,000
YYY Company 150 230,000 1,000,000
YYY Company 151 250,000 1,000,000
YYY Company 152 280,000 1,000,000
YYY Company 153 240,000 1,000,000
ZZZ Company 81 360,000 660,000
ZZZ Company 82 300,000 660,000

As you can see, I would like for the calculated field to
look at the client name, and sum up all of the sales
figures grouped by client name. For my purposes, it is
critical at this point to have it in the format above, as
I also need to be able to see the sales by each
individual unit. Would this be possible through an
expression in a new field in the query, or if not, is
there an alternate approach which would give me the same
thing. I am fairly beginner with access, so would
appreciate any help you may give. Thanks in advance.

Jason

 

[Alp Bekisoglu]

Hi Jason,

Neither am I an "expert" but just a suggestion:
QRY1 = Your first query that gives you the first set
QRY2 = just make another query where you get the sum per company/unit
Once you get your results properly for qry2, then create a third one and use
*.qry1 (all) and TotalSales.qry2 as its source. I think that should give you
what you need.

Alp

 

[John Vinson]

I would like to create another field (after sales) in the
query which would give me the following:

Client Name Unit Number Sales Total Sales By Client
XXX Company 10 100,000 330,000

Try:

[Total Sales By Client]: DSum("[Sales]", "[yourtablename]", "[Client
Name] = " & Chr(34) & [Client Name] & Chr(34))