Sumif

[jyadayada]

I have an access query that generates 15 fields of output, I would export this into excel and insert additional columns with calculations, the first being a running total.

I have many sites, with a site id [Site] in the first filed of out put, column A in the excel export and each site appearing numerous times. I have a % achieved field [PAd] which is column O in the excel export

In excel in column P I would run this and populate down through the rows

=SUMIF(A$2:A2,A2,O$2:O2)

so essentially if within A [Site] the [Site] = current record total column O [PAd]

what's the access equivalent of this? I've been digging around Dsum but I can't seem to find an example of similar condions

 

[Gasman]

I have something similar for a balance in a table

CMS is the ref, ID the autonumber key. The result is formatted as Currency to be placed in an email.

FWIW I would have thought you could just add another field to the query with your formula.

=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID <= " & [ID])

HTH

I have an access query that generates 15 fields of output, I would export this into excel and insert additional columns with calculations, the first being a running total.

I have many sites, with a site id [Site] in the first filed of out put, column A in the excel export and each site appearing numerous times. I have a % achieved field [PAd] which is column O in the excel export

In excel in column P I would run this and populate down through the rows

=SUMIF(A$2:A2,A2,O$2:O2)

so essentially if within A [Site] the [Site] = current record total column O [PAd]

what's the access equivalent of this? I've been digging around Dsum but I can't seem to find an example of similar condions

 

[jyadayada]

The 5 fields (exportable columns) i'm querying are the results of another query so ther isn't an autonumber key in there

 

[jyadayada]

15 feidls, pesky typo....

 

[arnelgp]

use aggregate (total query) to summarized each sites.

 

[jyadayada]

It's a running total against each record within each site, so rather than a total against the group of records for each site,


a cumulative running total against each record grouped by Site [Site], so Site A has 5 records with % Achieved [PAd] of say 1,3,5,7,9 I want the Running Total field to read 1,4,9,16,25 with it resetting to whatever the first PAd is for Site B

 

[plog]

Demonstate your issue with just data. Provide 2 sets:

A. Starting sample data from your source. Include the source name and the field names. Also, include enough data to cover all cases.

B. Expected results based on A. Show what data you hope to end up with when you feed the data from A into this yet to be built query.

Again, demonstrate with data, 2 sets. No more explanation, just show what should happen.

 

[arnelgp]

if there is no autonumber field then there must be a date field. how can you do a running total without a date:

select *, (sum(t1.numField) from yourTable as t1 where t1.siteID=yourtable.siteID and t1.datefield<= yourtable.datefield) as runningT from yourtable order by siteid, datefield;

 

[Gasman]

Well it does not have to be an ID, but you need two items I believe.

1. Data to identify a key to sum on
2. Data to determine the order.

In my case
1 is CMS
2 is ID

The 5 fields (exportable columns) i'm querying are the results of another query so ther isn't an autonumber key in there

 

[jyadayada]

Its a query result, there are 15 fields, currently no ID field, There are 2 fields I can concatenate to create a unique field, for orders sake, or I can duplicate the original query that results in 15 fields and turn this duplicate into a create table query with an autonumber field in addition to the 15

 

[jyadayada]

I'm going to go back to the source data or the initial import activity and build in a primary key


In the meantime I've duplicated the query that results in the 15 fields and turned this query into a create table query with an autonumber primary key in it, I can then build this DSum query against that table, So I want a cumulative running total of [PAd] grouped by [Site] with [ID] as a Seperate entity identifier, from the table [DataPrep]


I've been playing with several versions of the syntax and they all keep erroring, this is the latest iteration:


DSum("PAd]","[DataPrep]","[ID]<=" & [ID] & " And [Site]='" & [Site] & "'")


this keeps erroring

 

[Gasman]

I do not use brackets [] for the most part as I do not use spaces in my names.

Try

DSum("[PAd]","[DataPrep]","ID<=" & [ID] & " And Site='" & [Site] & "'")

 

[jyadayada]

This errors as wel, I might be missing something totally obvious, my first time back in access in over a decade;

I'm putting the syntax into the field box in design view, like this:

RunTot: DSum("[PAd]","[DataPrep]","ID<=" & [ID] & " And Site='" & [Site] & "'")

 

[Gasman]

Error message would help?
Is Site numeric or text?

This errors as wel, I might be missing something totally obvious, my first time back in access in over a decade;

I'm putting the syntax into the field box in design view, like this:

RunTot: DSum("[PAd]","[DataPrep]","ID<=" & [ID] & " And Site='" & [Site] & "'")

 

[jyadayada]

Site is numeric

Error Message Data type mismatch in criteria expression

 

[Gasman]

Site is numeric

Error Message Data type mismatch in criteria expression

Check what is text and what is numeric.

 

[arnelgp]

how can you have a transaction without a date.

an apoend query doesnt guarantee that the records are appended in order.

 

[jyadayada]

PAd is Number
DataPrep is the table name
ID is autonumber
Site is Number

 

[jyadayada]

There is no transaaction date as it's not transaction data, it's one off activity data against each site with the need for a cumulative / running total by site to establish those sites that haven't hit the mark and those that have gone over the mark to remove superfluous instances of the activity

 

[Gasman]

Don't surround the site variable with single quotes then.

PAd is Number
DataPrep is the table name
ID is autonumber
Site is Number