Results 1 to 9 of 9
  1. #1
    SuperDude_123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11

    New to access


    I'm trying to program access to find values where the same fields are equal. My SQL code is as follows:

    Code:
    SELECT Database.Description, Database.[Part Number], Database.Manufacturer, Database.Symbol, Database.[Job Number], Database.File, Database.Directory
    FROM [Database]
    WHERE (( Database.[Job Number] = Database.[Job Number] ) AND ((Database.Symbol)="CB1" AND (Database.Symbol)="DA1") ) ;
    Also, is it possible through a window/popup for the user to enter the search values instead of hard coding it with the Database.Symbol values?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use OR instead of AND

    Code:
    SELECT Database.Description, Database.[Part Number], Database.Manufacturer, Database.Symbol, Database.[Job Number], Database.File, Database.DirectoryFROM [Database]
    WHERE (((Database.[Job Number] = Database.[Job Number])) AND ((Database.Symbol)='CB1') OR (Database.Symbol)='DA1'));
    NOTE: I've also modified brackets & changed to single quotes in the WHERE clause

    If this is your actual query then you are using SEVERAl Access reserved words which WILL cause you problems
    All of these names need altering: Database, Description, File, Directory and I think Symbol as well
    These should be OK as alternatives: tblParts, ItemDescription, FileName, FolderName, PartSymbol
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't see the point of this bit of the criteria

    Database.[Job Number] = Database.[Job Number]

    since they are in the same record it will always return true

    Also, is it possible through a window/popup for the user to enter the search values instead of hard coding it with the Database.Symbol values?
    yes

    ((Database.Symbol)=[Enter first symbol]) OR (Database.Symbol)=[Enter second symbol]));

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Ajax View Post
    I don't see the point of this bit of the criteria

    Database.[Job Number] = Database.[Job Number]

    since they are in the same record it will always return true
    Oops - missed that
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    As you self describe as Novice - - I urge you to develop your query using the standard query design view. Once it returns the correct set of records - then change it so SQL View and you can see the SQL text.

    As to this note:
    Also, is it possible through a window/popup for the user to enter the search values instead of hard coding it with the Database.Symbol values? - - yes, one can call in a query's criteria from a text field in a Form. OR there is a feature called a Parameter Prompt - which can be put in the query design itself.


  6. #6
    SuperDude_123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11
    Quote Originally Posted by Ajax View Post
    I don't see the point of this bit of the criteria

    Database.[Job Number] = Database.[Job Number]

    since they are in the same record it will always return true

    yes

    ((Database.Symbol)=[Enter first symbol]) OR (Database.Symbol)=[Enter second symbol]));

    Thanks for all the info!

    The purpose of
    Code:
    Database.[Job Number] = Database.[Job Number]
    is that I am trying to achieve (unsuccessfully I might add) is that I'm looking to make the job numbers of all the entries that return both Database.Symbol have the same Database.[Job Number].

    For example, if we had the following data:

    Job # Symbol
    123 Light-1
    123 Temp-Sensor
    123 Light-2
    111 Light-1
    111 Light-7
    333 Temp-Sensor
    222 Light-1
    222 Temp-Sensor

    What I'm trying to get to is, if I searched for entries with Light-1 and Temp-Sensor, I'm trying to have it only show me the following:

    123 Light-1
    123 Temp-Sensor
    222 Light-1
    222 Temp-Sensor

    Where even tho the other job #'s had some of the fields, they did not have both and were automatically filtered out.

    If I was programming an if statement, I would write the Job # value to a variable, and if it matches, great, and if it didn't, I would drop it and keep looking. Is it possible to get similar results with access?

    In regards to being a complete newbie, any good recommendations on tutorials and the such?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    there are a number of ways to achieve what you want. This is one of them

    1. create a query, we'll call it qry1

    Code:
    SELECT myTable.JobNumber
    FROM myTable
    GROUP BY myTable.JobNumber
    HAVING (((Sum([Symbol]=[Enter1] Or [symbol]=[enter2]))=-2))
    2. create a second query based on qry1
    Code:
    SELECT myTable.*
    FROM myTable INNER JOIN qry1 ON myTable.JobNumber = qry1.JobNumber
    WHERE (((myTable.Symbol)=[Enter1])) OR (((myTable.Symbol)=[enter2]));

  8. #8
    SuperDude_123 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    11
    Thanks for the instructions!

    I ended up doing a mix and match of what you suggested and what I found online in to one sql sequence:

    Code:
    select t1.id, t1.Description, t1.[Part Number], t1.Manufacturer, t1.Symbol, t1.[Job Number], t1.File, t1.Directoryfrom [Database] AS t1
    INNER JOIN [Database] AS t2 ON  (t1.Symbol = t2.Symbol) 
                     and t1.[Job Number] = t2.[Job Number]
                     AND t1.Id <> t2.Id
    WHERE ((((t1.Symbol)=[Enter1])) OR (((t1.Symbol)=[Enter2])));
    The two problems I am having now is, the entry box [Enter1] and [Enter2] pop-up twice, and after I enter my filters, I get a lot of duplicate results. Is there a clean way to trim those out? I'm guessing these two problems are related some how.

    Furthermore, what does the =-2 or =-5 in the previous reply?


    Quote Originally Posted by Ajax View Post
    there are a number of ways to achieve what you want. This is one of them

    1. create a query, we'll call it qry1

    Code:
    SELECT myTable.JobNumber
    FROM myTable
    GROUP BY myTable.JobNumber
    HAVING (((Sum([Symbol]=[Enter1] Or [symbol]=[enter2]))=-2))
    2. create a second query based on qry1
    Code:
    SELECT myTable.*
    FROM myTable INNER JOIN qry1 ON myTable.JobNumber = qry1.JobNumber
    WHERE (((myTable.Symbol)=[Enter1])) OR (((myTable.Symbol)=[enter2]));

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what does the =-2 or =-5 in the previous reply?
    don't know about the -5. The -2 is the sum of booleans to make sure you have both of them.

    any criteria or join is a boolean expression - either something is true or it is not, there is no in between. Either a symbol equals a specified value, or it doesn't. In access a Boolean is stored -1=true, 0=false. You are looking for a situation where two (and only two) matches are true.

    I ended up doing a mix and match
    The two problems I am having now is,
    Your solution isn't working because it doesn't return the right answer - why not just use mine? If you want to make it one query use this

    Code:
    SELECT myTable.*
    FROM myTable INNER JOIN (SELECT myTable.JobNumber
    FROM myTable
    GROUP BY myTable.JobNumber
    HAVING (((Sum([Symbol]=[Enter1] Or [symbol]=[enter2]))=-2))) qry1 ON myTable.JobNumber = qry1.JobNumber
    WHERE (((myTable.Symbol)=[Enter1])) OR (((myTable.Symbol)=[enter2]));

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

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