Results 1 to 7 of 7
  1. #1
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20

    query returning multiple records of same User and charge

    What can I do to this query so I don't get multiple records of same charge. I only want UserID and TotalCurrent has 1777 records. I want 1777 records returned. I want the DistrictCode and OfficeCode, if available. But that second table has duplicate UserId as you can see.
    Click image for larger version. 

Name:	query.png 
Views:	14 
Size:	27.3 KB 
ID:	29903

    Click image for larger version. 

Name:	dupResults.png 
Views:	14 
Size:	11.3 KB 
ID:	29904

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What's so special about table name and field name you had to obscure them?

    SearchablePublic has records with no DistrictCode or OfficeCode - why?

    Is UserID unique in tbl_Complete?

    Why a compound primary key in SearchablePublic? Is CellPhone also a field in tbl_Complete?

    Apply filter criteria to DistrictCode: Not Is Null
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20
    Answering your questions:
    1. For business reasons
    2. This is a table linked to another db; not mine to fix
    3. Yes, UserID is unique in Tbl_complete
    4. see answer in #2
    5. That returns 1577 records; because 200 records in the linked table does not have district code or office code.

    Anyway suggestions around that?

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Apply filter criteria to OfficeCode also: Not Is Null

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Answer to Question 4 doesn't address the cellphone part and I will assume correct response is No unless indicated otherwise.

    According to the example data posted, that criteria should remove all duplicate UserID. If it doesn't then the data posted is not representative.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20
    There is no duplicate UserID in tbl_complete it is a unique field

    I have taken the above advice.
    Below is the query; but it is only returning 1577 of 1777 records. I need all the UserIDs from TBL_complete and District & Office codes if they are any, otherwise return a blank.

    Click image for larger version. 

Name:	query with is not null.png 
Views:	11 
Size:	29.3 KB 
ID:	29906

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    But without data I can't figure out why you are getting duplicates.

    Try setting JOIN type to "Include all records from tbl_Complete ..." and then maybe a GROUP BY on your original query.

    Or maybe you better do Query1:

    SELECT UserID, DistrictCode, OfficeCode WHERE DistrictCode Not Is Null;

    If that still returns dups, trying GROUP BY or DISTINCT.

    Then join that query to tbl_Complete with the JOIN type "Include all records from tbl_Complete ..."

    Suggest you set UserID in tbl_Complete as primary key.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Query not returning any records.
    By rebfein in forum Queries
    Replies: 3
    Last Post: 08-09-2016, 02:07 PM
  2. Total charge , creating a query
    By lasts3cond in forum Queries
    Replies: 1
    Last Post: 12-03-2013, 04:57 PM
  3. Query not returning records
    By Daryl2106 in forum Access
    Replies: 2
    Last Post: 02-28-2013, 07:58 PM
  4. Query not returning records
    By Daryl2106 in forum Access
    Replies: 2
    Last Post: 11-28-2012, 09:29 AM
  5. Returning multiple records Isn't what I need
    By frobro390 in forum Queries
    Replies: 6
    Last Post: 09-21-2010, 06:24 PM

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