Search results

I've got an IIF function running, but i'm starting to think that I can maybe build all my rules into one query, I'm currently creating 4 queries that i'll then have to merge into one output, As well as ranked group being a criteria there are a couple of others [Territory] and [Space_Type]...

[Ranked Group] is in one of the source tables and is also brought into the query that I'm building that contains [Sectional_Space_Available_CM] as a calculation field, I want this field [Sectional_Space_Used_CM] to be another calculation field in the same query, based on the lookp result of what...

I'm not quite sure how to go about this, I want a calculation field in my query but the amount to multiply by depends on a critera, without the criteria it would look like this: [Sectional_Space_Available_CM] x X , X being 0, 1 or 2 but whether it is 0,1 or 2 depends on what the value in...

What a doof! I put your text into the front end of design view and it runs fine, clicking into build in the field and letting Access add the Expr1 bit seemed to be the problem thanks for your inputs!

Basically I'm building a query bringing in columns from tables with joined fields, this is me trying to insert a field that doesn't exist in any of the tables that is a calculation of one of the fields that is also being brought into the query Going into SQL view on the above error, after all...

Thanks for that, that's not erroring now, I had to delete out and reload all my tables with spaces / brackets removed but now when I switch into datasheet view it asks for a paramater value and then brings me an empty datasheet [Space_Available_CM] is a table field containing values and I...

I'm building an access query, in it one of the fields is called [Space Available (cm)] I'm trying to add a divided by 6 field, so I've inserted a field in design view in the query, it looks like this: Field: Expr1: SectioalSpaceAvailable Table: Sort: Show: Criteria: =[Space...

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

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

Site is numeric Error Message Data type mismatch in criteria expression

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] & "'")

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...

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...

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...

15 feidls, pesky typo....

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

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...

@arnelgp - i will play with this method also as i need to refresh myself on joins

with a little tweaking i got that working (no pk's in the helper tables, just a single field of values in each)

OK the table is called tbl_CurrentSites the field i want 3 out of 2500 returning is called Site and the field I want (currently) 273 out of 15000 returning is called StockItem, both of these fileds are numerical, Site is 4 digit and StockItem is 6 digits, I thought i could create an additional...