Results 1 to 2 of 2
  1. #1
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24

    query returning too many results

    I am pulling data from 3 tables via a query. The query works however I am getting too many results. The biggest issue is each business might have more than one contact person. When there is more than one contact person I get a record for each contact. I only need the first contact.

    SELECT DISTINCTROW Left(tblBusinessLicenseContactItems.fldContactPers on,InStr(tblBusinessLicenseContactItems.fldContact Person, " ")-1) AS FirstName, Right(tblBusinessLicenseContactItems.fldContactPer son,Len(tblBusinessLicenseContactItems.fldContactP erson)-InStr(tblBusinessLicenseContactItems.fldContactPer son," ")) AS LastName, tblBusinessLicenseContactItems.fldEmailAddress, tblBusinessLicense.fldBusinessName, tblBusinessLicense.fldBusinessPhone, tblBusinessLicense.fldBusinessAddress, tblBusinessLicense.fldBusinessCity, tblBusinessLicense.fldBusinessZip, tblBusinessLicense.fldBusinessState, 'US' AS Country, tblBusinessLicense.fldBusinessDescription
    FROM (tblBusinessLicense INNER JOIN tblBusinessLicenseItems ON tblBusinessLicense.fldBusinessID = tblBusinessLicenseItems.fldBusinessID) INNER JOIN tblBusinessLicenseContactItems ON tblBusinessLicense.fldBusinessID = tblBusinessLicenseContactItems.fldBusinessID
    WHERE (tblBusinessLicenseItems.fldBusinessLicenseNumber> =Format(Now(),"yyyy") & "0000")
    ORDER BY tblBusinessLicense.fldBusinessDescription, tblBusinessLicense.fldBusinessName;

    I have to use distinctrow as not all of the businesses have contact person information entered correctly. Some only have 1 name while other have no information at all. I will be having a chat with our data entry person about this. If I use distinct and there is only 1 name I get an error (invalid procedure call). If there is no contact person the the record is not returned.

    So I have 3 issues:
    1) only return 1 record for each business regards of the number contacts.
    2) don't error if the contact name is a single name only like Jeff or Barb.
    3) return records that don't have a contact person.



    What am I doing wrong?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in Q1 , pull the Min contact in tContact. select Min(contactID), company from tContacts
    then in Q2 , pull the rest of the data needed using Q1

    Q2: select tData.*, Q1.ContactID from tData, Q1 where Q1.Company = tData.Company

    to only get data for the 1 contact.

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

Similar Threads

  1. Query no longer returning results
    By dwheatley in forum Queries
    Replies: 1
    Last Post: 12-01-2017, 09:25 AM
  2. Like query not returning all results
    By robbeh in forum Queries
    Replies: 3
    Last Post: 10-10-2014, 02:32 PM
  3. Query not returning expected results
    By MarcieFess in forum Queries
    Replies: 3
    Last Post: 10-18-2013, 05:28 PM
  4. Query Bug? Not returning consistent results
    By trb5016 in forum Queries
    Replies: 4
    Last Post: 06-15-2010, 12:00 PM
  5. Query not returning all of the results
    By velvettiger in forum Queries
    Replies: 4
    Last Post: 03-11-2010, 06:56 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