Trying to do something in an Access query

You can try using the iif function.  You won't be able to 'recalculate your A column' without some serious trickery with VBA.  You can however make a 'pretend A column'.  Try some SQL like this.

SELECT Table1.number, IIf([number]>=28900,[number]-28900,0) AS Calculated1, IIf([number]>28900,[number]-[calculated1],0) AS RecalcColumnA
FROM Table1;

Select allOpen in new window

db3.mdb

Can you explain your query in detail please?  I am not getting this.

Sure.  Note that i added the example DB to my first post so you can see the design view of the query.  Before I get into it, understand that:
1) Tables hold FACT.  What i mean by this is that the data on the record is the data on the record is the data on the record.  Nothing more, nothing less
2) Querys can be used to display that fact and sort it and calculate other values based on the 'facts' in the table that is being queried on.
This is where a lot of people with a lot of background in excel get confused when they first start using access (you can't put a formula in a table, you can however do so in a query (in a sense))
Anyway..  The IIF function (Immediate IF) works pretty much the same way that the Excel IF function:
IIF(Criteria to test, value to display if TRUE, value to display if FALSE)
ex:  IIF(10>5, "Ten is greater than five", "This is false")
would return "Ten is greater than five" because the test that we put in (10>5) is true
With the above in mind, the SQL that i gave you above is similar to what would be created when you used the IIF function in your query.  Imagine that you have a table:
The table name is "Table1"
there is a field in that table called "Number"
The table has 2 records.  the first is 2000 and the second is 31000.
Using the SQL I provided we are bringing in the field called Number from the table (this field holds 'FACTUAL' data.  
There isn't a simple way to get it to 'recalculate' without analyzing the table, and updating the record.  What we can do however, is create calculated fields within the query.  They don't exist anywhere in the table itself, but will use the FACT in the table to derive another value.
In addition to the Number field (that exists in the table), we are creating a calculated field called "Calculated1" (you can name it whatever you want).  This field holds the logic:
IIf([number]>=28900,[number]-28900,0)  
("if the value in the Number field is greater than or equal to 28900, then display the value in the number field minus 28900, otherwise just display 0")
There is also a 3rd calculated field in the query, called "RecalcColumnA".  Although we don't really have a graceful way of recalculating column A, like you requested in your original post, we can, pretty easily, display that desired value in this calculated field.  it holds the logic:
IIf([number]>28900,[number]-[calculated1],0)
("if the value in the Number field is greater than or equal to 28900, then display the value in the number field minus the value in the (calculated) Calculated1 field, otherwise just display 0)
 
Finally, in a nutshell this SQL is:
 brining in the Number field
bringing in the Calculated1 field (and actually defining the way it works since it isn't FACTUAL)
bringing in the RecalcColumnA field and actually defining the way it works since it isn't FACTUAL)
this is all done in the part that says :  SELECT Table1.number, IIf([number]>=28900,[number]-28900,0) AS Calculated1, IIf([number]>28900,[number]-[calculated1],0) AS RecalcColumnA
The last part of the query, tells the database which table to bring in the (FACTUAL) tables from.  In this case there is only one table which is the Table1 table
this is done in the part that says :  FROM Table1;
 
I hope this helps.  Please let me know if you have any other questions or if i've misunderstood you in any way
 
AR

OK, great!

I am on the right track, but my only problem is I want to join one field and the other field I want to do a left join but can't.  Here is what I have as my SQL query:

SELECT [assembly-output-v2].[Record Type], [assembly-output-v2].Company, [assembly-output-v2].Employee, [assembly-output-v2].[Process level], [assembly-output-v2].Dept, [assembly-output-v2].[Check ID], [assembly-output-v2].[Pay Code], [assembly-output-v2].[DED Code], [assembly-output-v2].[TR-Date], [assembly-output-v2].[Check Date], [assembly-output-v2].Hours, [assembly-output-v2].[Wage-Amt], [assembly-output-v2].[Ded-Amt], [assembly-output-v2].[Taxable-Wages], [assembly-output-v2].Excess, [assembly-output-v2].[Job Code], [assembly-output-v2].[Work State], [assembly-output-v2].[Ded End Date], [assembly-output-v2].[Check Number], [assembly-output-v2].[Case Number], [assembly-output-v2].[File Number], [assembly-output-v2].EDM, [assembly-output-v2].Expr4, IIf([ytd-tax-wage].[SumOfTaxable Wages]>=28900,[SumOfTaxable Wages]-28900,0) AS Excess1, IIf([ytd-tax-wage].[SumOfTaxable Wages]>28900,[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2
FROM [assembly-output-v2] INNER JOIN [ytd-tax-wage] ON ([assembly-output-v2].Employee = [ytd-tax-wage].Employee) AND ([assembly-output-v2].[DED Code] =  [ytd-tax-wage].[DED Code])
ORDER BY [assembly-output-v2].[Check ID], [assembly-output-v2].[DED Code], Assembly.[Pay Code];

Is there a way to do a left join or something with this line of SQL code?
([assembly-output-v2].[DED Code] =  [ytd-tax-wage].[DED Code])

VBBrett,
What table are you trying to left join in ?
Assembly-output-v2 is already part of your query, as is ytd-tax-wage
 
The syntax of a left join is:
LEFT JOIN TABLENAME
ON TABLENAME.FIELDNAME = SOMEOTHERTABLENAME.SOMEOTHERFIELDNAME
 
Let me know
 
AR
 

Also - I just noticed this part of your ORDER BY clause:
 
Assembly.[Pay Code];
 
Doesn't look like you have a table called Assembly as part of your query so this might cause you problems

I'm trying to do a nested iif statement but I can't get it to work, can you help out?  Here is what I have:

 IIf
(IIf([ytd-tax-wage].[SumOfTaxable Wages]>=28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN') ,[SumOfTaxable Wages]-28900,0) as Excess1,
 (IIf[ytd-tax-wage].[SumOfTaxable Wages]>=106800 AND ( [assembly-output-v2].[DED Code]='TXSC' OR  [assembly-output-v2].[DED Code]='TXSE') ,[SumOfTaxable Wages]-106800,0) as Excess1,

I pretty much have everything working so far except for the nexted iif statement.  Here is the original iif statement that works:
 IIf([ytd-tax-wage].[SumOfTaxable Wages]>=28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN') ,[SumOfTaxable Wages]-28900,0) AS Excess1

I also want to combine it with this statement:
 IIf([ytd-tax-wage].[SumOfTaxable Wages]>=106800 AND ( [assembly-output-v2].[DED Code]='TXSC' OR  [assembly-output-v2].[DED Code]='TXSE') ,[SumOfTaxable Wages]-106800,0) AS Excess1

How about this one?

 IIf([ytd-tax-wage].[SumOfTaxable Wages]>28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

and I also want to combine it with this:

 IIf([ytd-tax-wage].[SumOfTaxable Wages]>106800 AND ( [assembly-output-v2].[DED Code]='TXSC' OR  [assembly-output-v2].[DED Code]='TXSE'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

How do I combine the two so that they work together just like the first one?

Anybody have an idea on how to do this in Access?

 IIf([ytd-tax-wage].[SumOfTaxable Wages]>28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

and I also want to combine this iif statement with the following:

 IIf([ytd-tax-wage].[SumOfTaxable Wages]>28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

Please let me know how to complete this

Whoops, ignore the previous comment, this is what I want to combine:

IIf([ytd-tax-wage].[SumOfTaxable Wages]>28900 AND ( [assembly-output-v2].[DED Code]='TXFE' OR  [assembly-output-v2].[DED Code]='TXUN'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

and I also want to combine it with this:

 IIf([ytd-tax-wage].[SumOfTaxable Wages]>106800 AND ( [assembly-output-v2].[DED Code]='TXSC' OR  [assembly-output-v2].[DED Code]='TXSE'),[SumOfTaxable Wages]-[Excess1],0) AS TaxableWages2

This query worked very nicely.  Thank you!

Thanks for the points!  Were you able to get your second IIF statement to work?
 
AR