Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    fhickler is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2018
    Posts
    30
    Quote Originally Posted by Ajax View Post
    or do as I suggested in post #13
    Ok - I'll have to study up on your suggestion - as I said, I'm not too strong on SQL.

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can use separate queries to build it up e.g.

    qry1
    SELECT ID, fld1*2 as Num
    FROM myTable

    qry2
    SELECT myTable.*, Num*2 as Num2
    FROM qry1 INNER JOIN myTable ON qry1.ID=myTable.ID
    WHERE Num*4>20

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Criteria work on the original source not alias. Your Alias is a calculation
    Thank you for expressing it better than I apparently was able to.

    You probably should use a function as mentioned; even using any one of multiple query fields. Assuming the result is a long integer, you could write it as
    Code:
    Function ReturnVal (qryFld) As Long
    
    Select Case qryFld
       Case "field1"
           do this math
       Case "field2"
           do other math
       Case ....
    End Select
    
    End Function
    in query grid: Expr1: ReturnVal([field1])

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure the sql approach is all that viable. I got the impression that there are many fields in this query that will be calculated, not just one.
    and I wanted to refer to it many times in other columns
    Wouldn't the sql approach require at least a linked query for every calculation and criteria based on it?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Wouldn't the sql approach require at least a linked query for every calculation and criteria based on it?
    maybe, maybe not, not enough info provided by OP to make a judgement. Either way you would only store the calc in one place. The issue is with wanting to apply criteria to the calculated value(s). As the OP acknowledges databases work in a completely different way to excel, so trying to map an excel calculation process to a database is almost certainly going to fail

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated field prevents new record
    By Chrtalgo in forum Programming
    Replies: 5
    Last Post: 01-24-2018, 04:52 PM
  2. Replies: 3
    Last Post: 06-26-2014, 03:02 PM
  3. Replies: 3
    Last Post: 07-20-2012, 11:41 AM
  4. Query Prevents formatting of field
    By Chelcone in forum Queries
    Replies: 3
    Last Post: 04-20-2012, 11:43 AM
  5. Replies: 1
    Last Post: 05-18-2011, 12:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums