Results 1 to 10 of 10
  1. #1
    N3rdyByN4tur3 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    5

    Query Creating Duplicates


    I am fairly new to Access so please bare with me. I have a database of our shipping information and need to calculate the average process time of shipments. The data table with the information I need is just a running collection of processes (not my design), so incoming is one line and outgoing is a different line etc and for everything that has been processed. I have figured the best approach is create 2 queries, one looking for the in coming and changing the system date to Time In, and the other with the outgoing to Time Out. Then I have a third query that takes the information so Time In and Time Out can be on the same row with a new column to calculate the difference. The issue I am having is that I have a few items that have come through multiple times and the query seems to be listing every combination. For example I have an item that has been here twice (2 separate dates in TimeIn query and TimeOut query), but the final query has 4 instances (first time in with both time outs and the second in with both outs). I tried using DISTINCT in SQL for all 3 queries, but that doesn't help. Can anyone help?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Probably need to adjust JOIN clause. Post the query SQL statement. Provide sample data. 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.

  3. #3
    N3rdyByN4tur3 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    5
    What I have currently is:

    SELECT DISTINCT
    qryTimeIn.[Part Number],
    qryTimeIn.[Serial Number],
    qryTimeIn.[Time In],
    qryTimeOut.[Time Out],
    FROM qryTimeIn INNER JOIN qryTimeOut ON qryTimeIn.[Serial Number] = qryTimeOut.[Serial Number];

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please give us an example with a few items showing TimeIn and TimeOut and your proposed result so we understand your situation in context. Let readers understand the "proposed logic" by describing the "process" in plain English. We have no idea what queryTimeIn and queryTimeOut actually consist of, nor what they do exactly.

  5. #5
    N3rdyByN4tur3 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    5
    So we have a database that keeps track of incoming and out going material. Unfortunately it does so as a continuous list so you may have a listing like Incoming, Incoming, Outgoing, Incoming so the data on one part may be spread across the main table. I have to do a report to determine the average processing times. So I have a table with Part Number, Serial Number, System Time, and a process code that can be identified as In or Out that is jumbled as it were. I need a report that will have Part Number, Serial Number, Time In, Time Out, and a calculated Process Time. I created the TimeIn query to pull the codes I know to be In and renamed the System Time as Time In. Giving me Part Number, Serial Number and Time In. I did the same for the TimeOut query, but as Time out. I figured this was the easiest way to split the one table into In and Out and give me the ability to combine into one line. Only issue now seems to be matching the dates as close as possible.

  6. #6
    N3rdyByN4tur3 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    5
    My current issue is that some Serial Numbers come through multiple times. So matching just on Serial Number means each time the Serial Number comes up in Time In it matches with each possible Time Out for that Serial Number. For example I have one that is in TimeIn and TimeOut twice (we have processed it twice on 2/16 and 3/20) so now in my final query to combine them it listed 4 times: 2/16 to 2/16, 2/16 to 3/20, 3/20 to 2/16 and 3/20 to 3/20. This is what I am having problems fixing

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My current issue is that some Serial Numbers come through multiple times
    What are these serial numbers "attached to"? If you're dealing with shipments, how does the same serial number come through multiple times?
    You are telling us about your current solution/approach that doesn't work, but what is it exactly that you are dealing with and what exactly are you measuring?

  8. #8
    N3rdyByN4tur3 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    5
    Some parts get repaired and come back through our system. So we may get a part, it may sit a little before ordered, and then go out. It then may come back to be sent for repair, then back to us even. So the date range on each serial number may span years as it comes in, goes out, comes in, and goes for repair. As stated the system (created before I started) lists everyone of these consecutively as they happen with a system date. So I now have to identify the Ins and Outs, get the system times with dates matched (system date is date and time in one field), and calculate the processing time for each so I can get the average processing time. So if PN1 SN1 came in on 2/10 10:30, left 2/11 9:20 then I need the difference between those.

  9. #9
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    My current issue is that some Serial Numbers come through multiple times.
    from your description it appears that you are making this more complicated than it needs to be.

    if you want average shipping time, then every shipment counts. and it really makes no difference if you are shipping the same part back and forth multiple times. each individual shipment contributes to your average shipping time.

    for this calculation you do not need to know what was shipped, you just need to know whether the shipment was in or out, and how long it took.

    total time divided by total shipments is average shipping time.


    good luck with your project,


    Cottonshirt

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you considered some concept of Repair Ticket? or WorkOrder?
    Consider a customer brings car in for service May1/2020 --LicenseNo, Owner etc to have Oil and Filter replaced.
    Same Owner, same car May 25 to get a flat tire fixed... etc Different workorders, different dates, but tracking the workorder not the car or owner specifically. Just a thought for consideration. You know your environment better than any reader, so you may want to adjust/tweak your model/design.

    NOTE: Just saw cottonshirt's post and agree if that's all you're really trying to do.

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

Similar Threads

  1. Replies: 7
    Last Post: 11-20-2019, 11:30 AM
  2. Replies: 6
    Last Post: 10-23-2013, 08:06 AM
  3. Append query is creating duplicates
    By D4WNO in forum Database Design
    Replies: 3
    Last Post: 12-10-2012, 10:47 AM
  4. Append query creating duplicates
    By dhicks19 in forum Queries
    Replies: 1
    Last Post: 05-14-2012, 06:36 AM
  5. Replies: 10
    Last Post: 03-01-2012, 11:31 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