?
Solved

Trying to do something in an Access query

Posted on 2009-04-29
14
Medium Priority
?
353 Views
Last Modified: 2012-05-06
I am trying to do something in an Access query.  Let's say there is an amount of 30,000 in column A.  I would like column A to have $28,900 and column B to have $1,100.  Basically anything over $28,900 I want to be transferred into column B and I want column A to be recalculated.  So if column A has $50,000, I want column A to be recalculated to be $28,900 and column B to show $21,100.  I hope this helps.  Thanks!
0
Comment
Question by:VBBRett
  • 8
  • 6
14 Comments
 
LVL 5

Expert Comment

by:ducky801
ID: 24263854
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;

Open in new window

db3.mdb
0
 

Author Comment

by:VBBRett
ID: 24264655
Can you explain your query in detail please?  I am not getting this.
0
 
LVL 5

Expert Comment

by:ducky801
ID: 24264948
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:VBBRett
ID: 24265140
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])
0
 
LVL 5

Expert Comment

by:ducky801
ID: 24265357
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
 
0
 
LVL 5

Expert Comment

by:ducky801
ID: 24265383
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
0
 

Author Comment

by:VBBRett
ID: 24265395
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,
0
 

Author Comment

by:VBBRett
ID: 24265408
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
0
 
LVL 5

Accepted Solution

by:
ducky801 earned 2000 total points
ID: 24265453
Try this:
 
 IIf([ytd-tax-wage].[SumOfTaxable Wages]>=28900 AND [assembly-output-v2].[DED Code]='TXFE', [SumOfTaxable Wages]-28900, IIf([assembly-output-v2].[DED Code]='TXUN', [SumOfTaxable Wages]-28900, IIf([ytd-tax-wage].[SumOfTaxable Wages]>=106800 AND [assembly-output-v2].[DED Code]='TXSC', [SumOfTaxable Wages]-106800, Iif([assembly-output-v2].[DED Code]='TXSE', [SumOfTaxable Wages]-106800, 0)))) as Excess1
 
AR
0
 

Author Comment

by:VBBRett
ID: 24265570
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?
0
 

Author Comment

by:VBBRett
ID: 24268469
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
0
 

Author Comment

by:VBBRett
ID: 24268534
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
0
 

Author Closing Comment

by:VBBRett
ID: 31576101
This query worked very nicely.  Thank you!
0
 
LVL 5

Expert Comment

by:ducky801
ID: 24270494
Thanks for the points!  Were you able to get your second IIF statement to work?
 
AR
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question