Results 1 to 8 of 8
  1. #1
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20

    Records Repeating

    I have data that is being processed through multiple queries that needs to be in a specific final product. All has been going well until I stumbled upon a problem. My goal is to take these two queries and essentially join them together. The ID and Date column are the same and should be together, so I made a union query to handle that, but the meter, and odometer columns need to be separate. When there is a meter reading, the odometer column should have a zero and vice versa. Instead, the Asset ID column duplicates itself and we get hundreds of records.

    Here are the three queries:

    Click image for larger version. 

Name:	example1.PNG 
Views:	8 
Size:	22.0 KB 
ID:	35534
    Click image for larger version. 

Name:	example2.PNG 
Views:	8 
Size:	51.7 KB 
ID:	35533
    Click image for larger version. 

Name:	example.PNG 
Views:	8 
Size:	46.0 KB 
ID:	35535


    Here is the place I start trying to get the query to where it needs to be, but have not had much success:

    Click image for larger version. 

Name:	issue.PNG 
Views:	8 
Size:	5.2 KB 
ID:	35538
    Click image for larger version. 

Name:	issue3.PNG 
Views:	8 
Size:	57.3 KB 
ID:	35536


    I've tried many things to get this to show the data as described, but have had no luck.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not seeing any JOINS in that query. Possibly need compound joins.

    How does Vehicle relate to Asset? They are the same?
    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
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    I already attempted to make the joins. after I joined the ID's and Date's from CurrentOdometer and CurrentMeter to UnionDATEandID, I chose the fields Asset ID (UnionDATEandID) , Meter (CurrentMeter), Odometer(CurrentOdometer), and Date (UnionDATEandID) but it returned a blank table.


    yeah, I should go in a change Vehicle to Asset ID. They are exactly the same.

  4. #4
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    With Links
    Click image for larger version. 

Name:	links.PNG 
Views:	8 
Size:	14.7 KB 
ID:	35539
    Click image for larger version. 

Name:	result.PNG 
Views:	8 
Size:	6.5 KB 
ID:	35540


    If I delete one of the 'Current' tables and leave the links, it works how it should, it is only when I have linked both 'current' tables to the union table that the data does not show.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    Meter.zip


    Here is the DB

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Haven't looked at yet but try changing from INNER JOIN to LEFT or RIGHT (whichever is 'all records from UnionDATEandID') for each link.
    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.

  8. #8
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    That... worked! nice, thanks alot!

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

Similar Threads

  1. Repeating Titles
    By Western_Neil in forum Database Design
    Replies: 9
    Last Post: 05-10-2018, 07:43 PM
  2. Import data from excel, with repeating records
    By ali1979 in forum Import/Export Data
    Replies: 8
    Last Post: 02-16-2017, 02:37 AM
  3. Replies: 1
    Last Post: 11-23-2015, 12:11 PM
  4. Data in subform records repeating
    By skatiekat in forum Forms
    Replies: 2
    Last Post: 11-01-2012, 12:50 PM
  5. Repeating Records
    By windwardmi in forum Reports
    Replies: 13
    Last Post: 05-22-2010, 02:54 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