Results 1 to 2 of 2
  1. #1
    MZobrist is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    1

    Calculated column

    Hello,

    I've been searching the internet for quite some time now but can't find anything on this specific question i have. How do I create a "calculated" column that tells me if the following statement is true or false: If column "C" record 1 = "Submitted to department" and column "A" record 1 is greater than or less than column "A" record 2, return "TRUE" as the value.

    In excel this formula would look be "=and(C1="Submitted to department", A1<>A2)"

    Any help with this would be much appreciated. This is my first time posting on a message board so please forgive me if i didn't use the correct verbiage.

    Thanks in advance,


    Matt

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    access (any database) is not excel, you need to think differently. data is not stored in any order (not even the order it was entered), so you need one or more columns to define that order - might be a timestamp, or an autonumber or something else - would your excel formula still work if you were to sort the data into a different order?

    so to refer to the next record, you need to be able to define next in what order? and want happens if there is no next record?

    Note that autonumbers are not guaranteed to be contiguous or indicate order of input, purely to be unique, and you can have records with the same timestamp - particularly if appended via an import or batch process.

    so to answer your question, for the purposes of this example, we'll use an autonumber field and I'm assuming you have this in a query

    Code:
    SELECT *, C1='Submitted to department' and A1<>(SELECT Top 1 A1 FROM MyTable AS T WHERE AutoN>myTable.AutoN ORDER BY AutoN) AS truefalsefield
    FROM myTable

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  2. Replies: 41
    Last Post: 05-29-2015, 02:58 PM
  3. IRR Formula in VBA
    By Mpike926 in forum Access
    Replies: 2
    Last Post: 05-19-2015, 02:48 PM
  4. Please help with formula
    By JeanZander in forum Database Design
    Replies: 2
    Last Post: 01-07-2013, 04:39 PM
  5. Formula
    By Ray67 in forum Queries
    Replies: 53
    Last Post: 08-09-2012, 01:56 AM

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