Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68

    How to pull records that do not have a specific value

    I have a table of Service Locations. Each Service location can have multiple services (1-4). The table has a record for every service. So the data would look like


    Locat# Service Type
    134 1
    134 2
    134 3
    258 1
    258 2
    654 1
    654 2
    654 3

    I need to pull out all location numbers that would not a record with a 3 in the service type field. Another way of putting it I need to determine all locations where service type 3 doesn't exist for them.

    I'm not great with sql so most everything I build is through access query design.

    Thanks for the help!

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    One way would be to first create a query that concatenates all services in one field separated by commas or any other character (http://allenbrowne.com/func-concat.html or JoinFromArray on my stie http://forestbyte.com/vba-code-samples/) then in another you would use Instr("LocationAllServices","3")=0 where LoacationAllServices is the new field you created in the first one.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is your records based on a count? As in all the Locat# that do not have a 3 will always have 2 records for Service Type 1 and 2 in the table? Or can you have say Service Type 1 and 3 for a specific Locat#? If it does follow 1,2 3 records and no skipping, you could do a count of each Locat# and if the total is 2, that would show you which ones do not have a 3. In the query, click on the Totals button, then in that row select Groupby for Locat#, Count for ServiceType and in the criteria for ServiceType select 2. Should then give you all counts of 2 that do not have the 3 Service Type.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was curious if a subquery could handle this, assuming that this
    I need to determine all locations where service type 3 doesn't exist for them.
    means if a location has a value, omit all of those locations and that there won't be a set number of location records for a type. This seems to work:

    Code:
    SELECT tbl1.Fld1 FROM tbl1 
    WHERE (((tbl1.Fld1) Not In (SELECT tbl1.[Fld1] FROM tbl1 WHERE tbl1.FldValue = 3)))
    ORDER BY tbl1.Fld1;
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or you can use a left join

    Code:
    SELECT DISTINCT T1.Location
    FROM myTable T1 LEFT JOIN (SELECT Location FROM myTable WHERE ServiceType=3) T2 ON T1.Location=T2.Location
    WHERE T2.Location is Null

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why make this so complicated?
    Code:
    SELECT [Locat#], ServiceType
    FROM TableName
    WHERE ServiceType<>3;
    
    BTW Using special characters such as # in field names is not recommended and the name has to be enclosed in []
    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

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @Colin

    OP want's locations which do not have service type 3. Your query will return all locations whether or not they have service type 3

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops. Thanks. My mistake.
    I've read it again properly this time. Should have checked the supplied data in post #1 before posting.
    Both the solution supplied by Micron and that by Ajax should do the job perfectly
    Last edited by isladogs; 01-12-2020 at 10:08 AM.
    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

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's time for OP to respond.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    At start, don't use special characters (# and space included) in field names. Use e.g. Location and ServiceType instead.

    The query will be like (on fly):
    Code:
    SELECT DISTINCT yt.Location
    FROM YourTable yt
    WHERE yt.Location Not In (SELECT yt0.Location FROM YourTable yt0 WHERE yt0.ServiceType = 3)
    Last edited by ArviLaanemets; 01-13-2020 at 08:25 AM.

  11. #11
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Quote Originally Posted by Gicu View Post
    One way would be to first create a query that concatenates all services in one field separated by commas or any other character (http://allenbrowne.com/func-concat.html or JoinFromArray on my stie http://forestbyte.com/vba-code-samples/) then in another you would use Instr("LocationAllServices","3")=0 where LoacationAllServices is the new field you created in the first one.

    Cheers,

    No 1 equates to electric
    2 equates to water
    3 equates to sewer
    4 equates to sanitation

    Any given location could have 1 or all services. So the problem for me is having multiple records in 1 table for the same location (1 per service) and querrying out the locations that do not have a 3 or maybe that have a 2 but no 3 record. So for every location select the locations that do not have a 3 record anywhere in the table.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have responded to the first response you have had, but not the other 8

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by ArviLaanemets View Post
    At start, don't use special characters (# and space included) in field names. Use e.g. Location and ServiceType instead.

    The query will be like (on fly):
    Code:
    SELECT DISTINCT yt.Location
    FROM YourTable yt
    WHERE yt.Location Not In (SELECT yt0.Location FROM YourTable yt0 WHERE yt0.ServiceType = 3)
    Will that perform differently from what I wrote in post 4?

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    Will that perform differently from what I wrote in post 4?
    Your query may return same location several times (with high probability), instead single row for every location.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No, it does not. I tested it. The subquery returns a value, e.g. 3.
    The Not In clause says 'show me locations where there are no values of 3'.

    EDIT - or maybe you mean duplicates of the locations that are not 3? In that case, I'd agree.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-15-2018, 12:37 PM
  2. Replies: 2
    Last Post: 01-03-2017, 03:27 PM
  3. pull data from a specific record in a subform
    By pleshrl in forum Queries
    Replies: 3
    Last Post: 04-21-2013, 05:07 AM
  4. Replies: 1
    Last Post: 08-01-2012, 12:50 AM
  5. Replies: 2
    Last Post: 02-22-2012, 02:36 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