Results 1 to 5 of 5
  1. #1
    sportyaccordy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    7

    Access SQL analog to [@Field] from Excel Table/self-referencing row for criteria?

    I am trying to build a query that references a value within a record's own row. In Excel this is super simple- the CountIF statement would look like this:

    Code:
    COUNTIF([Field1],[@Field1],[Field2],[@Field2]...)
    Referencing a field seems pretty easy; it's referencing the value of said field in the current row that I am struggling with. I feel like it is a very simple syntax thing. Ideas?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Are you using the query designer in Access? It seems the solution is to build a query object, save it, and name it. Then, build a form or report and bind it to your query.

  3. #3
    sportyaccordy is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    7
    I use the designer to create the joins but I prefer building calculated fields with SQL.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It seems you are confusing an expression with an SQL statement. Microsoft Access is best suited for storing data in tables, using SQL statements in Query objects, using Form objects as a GUI, and displaying data in Report objects. You will be best served using Tables, Queries, Forms, and Reports in concert.

    Perhaps you can use the IIF() function in an alias. This IIF would consider another column that uses Count() or Sum() or whatever it is you are trying to accomplish.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Referencing a field seems pretty easy; it's referencing the value of said field in the current row...
    In a query, if you reference a field in an expression, then you are automatically referencing the value in that field. So, if [Field1] contains 3 and [field2] contains 4, then the expression [field1] + [field2] = 7

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

Similar Threads

  1. Replies: 7
    Last Post: 01-14-2016, 02:51 PM
  2. Replies: 1
    Last Post: 09-11-2014, 11:02 AM
  3. Replies: 9
    Last Post: 12-08-2013, 07:04 PM
  4. Replies: 8
    Last Post: 04-26-2012, 10:13 AM
  5. Referencing table data in field validation rule
    By toad848 in forum Database Design
    Replies: 3
    Last Post: 03-19-2009, 07:03 AM

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