Results 1 to 9 of 9
  1. #1
    Join Date
    May 2015
    Posts
    6

    Answered: Combining records

    Great Day!

    I have a database with about 800,000 records. Some of these records, about 30,000 need to be combined into a single record in a query. The first selection field is called "Route_ID", the second is the MR ID and the third is the "Read_Date" field. If the Route ID and MR_ID in record 1 and record 2 match AND the Read_Date in Record 1 is within -3 days (allows for the weekend) of the Read_date in Record 2, I need to combine those two records.

    The Read Date Field would be the record 1 data, I have other fields, like [Hours] that would need to be added. In the dataset below I would want to combine records 2 and 3.

    Route ID MR ID Read Date Download Date Actual Reads Hours Minutes Percentage Complete
    43R1138W C8034 17-May-15 21-May-15 42 43 1 6 7.91
    42R4291W C7937 14-May-15 21-May-15 126 42 2 17 13.64
    42R4291W C7937 15-May-15 21-May-15 639 42 6 6 69.16
    50C1327W C7480 15-May-15 21-May-15 375 50 2 45 54.43


    The combined record should be:
    42R4291W C7937 14-May-15 21-May-15 126 84 8 23 99.80


    The results of this "Merge Query" will be used as the base for further queries. If the minutes exceed 60 that is not an issue.

    Thanks!

    Doug

  2. Best Answer
    Posted by healdem

    "Are you trying to merge rows, then delete the orphan rows) or do you need to keep the ioriginal data alonjg with the merged rows.?


    in any event you need to run a query smilar to :-
    Code:
    SELECT A.RouteID,
     A.MRID,
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     A.Hours + B.Hours as SumHours,
     A.Minutes+ B.Minutes as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you can refine that query to handle the miunutes rollover using something similar to:-
    Code:
    SELECT
     A.RouteID,
     A.MRID, 
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     IIF (A.Minutes+ B.Minutes <60,
      A.Hours + B.Hours,
      A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60
     )  as SumHours,
    IIF (A.Minutes+ B.Minutes <60,
     A.Minutes+ B.Minutes,
     (A.Minutes+ B.Minutes) mod 60
    )  as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you could use that query as a feedstock into a new table using an INSERT INTO query
    then join that new table to the old table, bringing all rows from the new table and only those rows from the old table that didin't have a match in the old table

    the second version looks a lot more complicated, essentailly its not. all it does is test if the combined minutes value is greater than 60 using a pair of IIF statements
    the first IIF
    Code:
    IIF (A.Minutes+ B.Minutes <60, A.Hours + B.Hours,  A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60)  as SumHours,
    tests if the new sum minutes is less than 60, if so it simply adds the hours element from both rows, if its 650 or more it adds the number of hours from the new combined minutes
    the second IIF calculates the new minutes figure, using simple addition if its less than 60, or the modulus of that addition to give the new minutes. effectivley we have already 'carried' the hours in the earlier IIF expression"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Are you trying to merge rows, then delete the orphan rows) or do you need to keep the ioriginal data alonjg with the merged rows.?


    in any event you need to run a query smilar to :-
    Code:
    SELECT A.RouteID,
     A.MRID,
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     A.Hours + B.Hours as SumHours,
     A.Minutes+ B.Minutes as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you can refine that query to handle the miunutes rollover using something similar to:-
    Code:
    SELECT
     A.RouteID,
     A.MRID, 
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     IIF (A.Minutes+ B.Minutes <60,
      A.Hours + B.Hours,
      A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60
     )  as SumHours,
    IIF (A.Minutes+ B.Minutes <60,
     A.Minutes+ B.Minutes,
     (A.Minutes+ B.Minutes) mod 60
    )  as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you could use that query as a feedstock into a new table using an INSERT INTO query
    then join that new table to the old table, bringing all rows from the new table and only those rows from the old table that didin't have a match in the old table

    the second version looks a lot more complicated, essentailly its not. all it does is test if the combined minutes value is greater than 60 using a pair of IIF statements
    the first IIF
    Code:
    IIF (A.Minutes+ B.Minutes <60, A.Hours + B.Hours,  A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60)  as SumHours,
    tests if the new sum minutes is less than 60, if so it simply adds the hours element from both rows, if its 650 or more it adds the number of hours from the new combined minutes
    the second IIF calculates the new minutes figure, using simple addition if its less than 60, or the modulus of that addition to give the new minutes. effectivley we have already 'carried' the hours in the earlier IIF expression
    Last edited by healdem; 05-29-15 at 06:42.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    May 2015
    Posts
    6

    Merging Records

    Data flow is like this....

    1. Original data imported from client database into master record set stored on a network server (I have access but cannot change any tables or the import function will not work). Main table required is called [CNTRANS].

    2. I have a made a query to append this table (and new records as added) to a locally stored table called [CNT_REAL_LOCAL]. This table is an identical copy of the [CNTRANS] plus a couple of calucalted fields I added in the query.

    3. From this data I need to merge similar rows as described previously, once this is done I need to anaylze the query results for reporting purposes.

    Should I run the query to merge the records and append to a new table or should the query be the bases of the reports? I would like to make the query the bases of the reports but then how do I omit the duplicate records? I can add a field to flag the "deleted" records and use a "Is Null" function to omit these from the search...?





    Quote Originally Posted by healdem View Post
    Are you trying to merge rows, then delete the orphan rows) or do you need to keep the original data along with the merged rows.?


    in any event you need to run a query smilar to :-
    Code:
    SELECT A.RouteID,
     A.MRID,
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     A.Hours + B.Hours as SumHours,
     A.Minutes+ B.Minutes as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you can refine that query to handle the miunutes rollover using something similar to:-
    Code:
    SELECT
     A.RouteID,
     A.MRID, 
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     IIF (A.Minutes+ B.Minutes <60,
      A.Hours + B.Hours,
      A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60
     )  as SumHours,
    IIF (A.Minutes+ B.Minutes <60,
     A.Minutes+ B.Minutes,
     (A.Minutes+ B.Minutes) mod 60
    )  as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you could use that query as a feedstock into a new table using an INSERT INTO query
    then join that new table to the old table, bringing all rows from the new table and only those rows from the old table that didin't have a match in the old table

    the second version looks a lot more complicated, essentailly its not. all it does is test if the combined minutes value is greater than 60 using a pair of IIF statements
    the first IIF
    Code:
    IIF (A.Minutes+ B.Minutes <60, A.Hours + B.Hours,  A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60)  as SumHours,
    tests if the new sum minutes is less than 60, if so it simply adds the hours element from both rows, if its 650 or more it adds the number of hours from the new combined minutes
    the second IIF calculates the new minutes figure, using simple addition if its less than 60, or the modulus of that addition to give the new minutes. effectivley we have already 'carried' the hours in the earlier IIF expression

  5. #4
    Join Date
    May 2015
    Posts
    6

    Merging Records

    Data flow is like this....

    1. Original data imported from client database into master record set stored on a network server (I have access but cannot change any tables or the import function will not work). Main table required is called [CNTRANS].

    2. I have a made a query to append this table (and new records as added) to a locally stored table called [CNT_REAL_LOCAL]. This table is an identical copy of the [CNTRANS] plus a couple of calucalted fields I added in the query.

    3. From this data I need to merge similar rows as described previously, once this is done I need to anaylze the query results for reporting purposes.

    Should I run the query to merge the records and append to a new table or should the query be the bases of the reports? I would like to make the query the bases of the reports but then how do I omit the duplicate records? I can add a field to flag the "deleted" records and use a "Is Null" function to omit these from the search...?





    Quote Originally Posted by healdem View Post
    Are you trying to merge rows, then delete the orphan rows) or do you need to keep the original data along with the merged rows.?


    in any event you need to run a query smilar to :-
    Code:
    SELECT A.RouteID,
     A.MRID,
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     A.Hours + B.Hours as SumHours,
     A.Minutes+ B.Minutes as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you can refine that query to handle the miunutes rollover using something similar to:-
    Code:
    SELECT
     A.RouteID,
     A.MRID, 
     A.ReadDate,
     A.DownloadDate,
     A.Actual,
     A.Reads+B.Reads as SumReads,
     IIF (A.Minutes+ B.Minutes <60,
      A.Hours + B.Hours,
      A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60
     )  as SumHours,
    IIF (A.Minutes+ B.Minutes <60,
     A.Minutes+ B.Minutes,
     (A.Minutes+ B.Minutes) mod 60
    )  as SumMinutes,
     A.PercentComplete + B.PercentComplete as SumPercentComplete
    FROM MyTable AS A
    INNER JOIN
     MyTable AS B ON A.RouteID = B.RouteID AND A.MRID = B.MRID
    WHERE
     A.ReadDate<>B.ReadDate
    And
     A.ReadDate<B.ReadDate;
    you could use that query as a feedstock into a new table using an INSERT INTO query
    then join that new table to the old table, bringing all rows from the new table and only those rows from the old table that didin't have a match in the old table

    the second version looks a lot more complicated, essentailly its not. all it does is test if the combined minutes value is greater than 60 using a pair of IIF statements
    the first IIF
    Code:
    IIF (A.Minutes+ B.Minutes <60, A.Hours + B.Hours,  A.Hours + B.Hours + (A.Minutes+ B.Minutes)\60)  as SumHours,
    tests if the new sum minutes is less than 60, if so it simply adds the hours element from both rows, if its 650 or more it adds the number of hours from the new combined minutes
    the second IIF calculates the new minutes figure, using simple addition if its less than 60, or the modulus of that addition to give the new minutes. effectivley we have already 'carried' the hours in the earlier IIF expression

  6. #5
    Join Date
    May 2015
    Posts
    6

    Combining Records

    Code:
    SELECT A.EXCLUDE_PD, A.MR_ID, A.CNT_REAL_ROUTE, A.Route_ID, Right$([A.Route_ID],1) AS RouteType, A.RD_DT, B.RD_DT, A.FirstReadTime, A.LastReadTime, B.FirstReadTime, B.LastReadTime, [SumTotalReads]/([SumHours]+([SumMinutes]/60)) AS ReadRate, [A].[ActualReads]+[B].[ActualReads] AS SumReads, [A].[TotalSkipReads]+[B].[TotalSkipReads] AS SumSkipReads, [SumReads]+[SumSkipReads] AS SumTotalReads, IIf([A].[MinS]+[B].[Mins]<60,[A].[Hours]+[B].[Hours],[A].[Hours]+[B].[Hours]+([A].[Mins]+[B].[Mins])\60) AS SumHours, IIf([A].[Mins]+[B].[Mins]<60,[A].[Mins]+[B].[Mins],([A].[Mins]+[B].[Mins]) Mod 60) AS SumMinutes, ([SumHours]+([SumMinutes]/60)) AS RouteTime, [A].[SC01]+[B].[SC01] AS SumSC01, A.SC02+B.SC02 AS SumSC02, A.SC03+B.SC03 AS SumSC03, A.SC04+B.SC04 AS SumSC04, A.SC05+B.SC05 AS SumSC05, A.SC06+B.SC06 AS SumSC06, A.SC07+B.SC07 AS SumSC07, A.SC08+B.SC08 AS SumSC08, A.SC09+B.SC09 AS SumSC09, A.SC10+B.SC10 AS SumSC10, A.SC11+B.SC11 AS SumSC11, A.SC12+B.SC12 AS SumSC12, A.SC13+B.SC13 AS SumSC13, A.SC14+B.SC14 AS SumSC14, A.SC15+B.SC15 AS SumSC15, A.SC16+B.SC16 AS SumSC16, A.SC17+B.SC17 AS SumSC17, A.SC18+B.SC18 AS SumSC18, A.SC19+B.SC19 AS SumSC19, A.SC20+B.SC20 AS SumSC20, A.SC21+B.SC21 AS SumSC21, A.SC22+B.SC22 AS SumSC22, A.SC23+B.SC23 AS SumSC23, A.SC24+B.SC24 AS SumSC24, A.SC25+B.SC25 AS SumSC25, A.SC26+B.SC26 AS SumSC26
    FROM CNTRANS_REAL_LOCAL AS A INNER JOIN CNTRANS_REAL_LOCAL AS B ON A.Route_ID = B.Route_ID
    WHERE (((A.EXCLUDE_PD)=No) AND ((A.MR_ID)="C5018")) AND (((B.RD_DT)=[A].[RD_DT]+1)) OR (((B.RD_DT)=[A].[RD_DT]+2)) OR (((B.RD_DT)=[A].[RD_DT]+3)) OR (((B.RD_DT)=[A].[RD_DT]+4));
    This works except it eliminates the records that do not have any matching a/b, example below:

    ROUTE_ID CNT_REAL_ROUTE MR_ID RD_DT ROUTE_TIME Read_Rate ActualReads TotalSkipReads Total_Reads
    01B5027W B5027 C7464 02-Jul-14 5.65 46.02 254 6 260
    01B5030W B5030 C7731 01-Apr-15 6.15 56.59 336 12 348
    01B5054T B5054 C6387 03-Jul-14 6.18 21.51 124 9 133
    01B5057T B5057 C6270 01-Apr-14 3.00 12.67 34 4 38
    01B5057T B5057 C6270 02-Apr-14 10.52 11.32 104 15 119


    What is happening now is records 4 & 5 above are being combined but records 1-3 are not displaying in the results since they have no matching route ID...

    I have tried a left join with same results. Since this is an A - B Join, not sure how to keep records 1-3 in the results...
    Last edited by douglasfagerstrom; 06-01-15 at 19:05.

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You wanted a query which merged rows. Youve got one. Now whether that is 'the' solution i dont know.
    how you handle the rest of the process is up to you and your design choices.
    do you move those merged rows yo another table
    do you write back the changed values, then delete the duplicated rows
    do what you think is ghe right approach for your design, your skill set and experience.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    May 2015
    Posts
    6

    Combining Records

    healdem

    Correct, I did get the merge query which works exactly as required. I did not explain the exact issue in my first post, apologies for that.

    In the original records set of about 800,000 records, the "duplicates" which the merge records are from 30,000 down to 15,000 perfect...

    The intention of the query was to have a results with 785,000 records....not just the 15,000 in the current query.

    Cheers!

    Doug

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it were me,
    then assuming that you have no need to retain the original data (as its coming from another system), then I'd probably run a series of queries to sanitise the data
    1st query merges rows (you do need to do some more testing to make certain the supplied query meets all possible erro conditions / data states.. feristance it will work with ywo rows, I suspect (!) it wont work with more than two , there is no checkign for the within 3 days criteria to sapn a weekend and so on).

    use that query to update rows in place using an update query

    then run another query that deletes duplicate rows (as the first row is updated then deelte the row witht he higher read date

    you can improve the 'efficency' of thyese queries by indexing on one or more of the dtae columkns, and only process rows whose date falls intot he known needs reviewign date.

    I don't know your business process, but say these are weekly deliveries, then you can reduce the number of rows processed by addign an additonal item tot he where clause eg
    Code:
    A.ReadDate >= dateadd('"m", 1, date())
    ie process allrows who read date is within 1 month of today, see http://www.techonthenet.com/access/f...te/dateadd.php for details on the dateadd function.

    as said before its your application, your business rules, your design approach and perhaps most importantly your skillset, your experience, your wish to learn. Also you need tobear in mind when the poroicess will run. again if it were me I'd run this as a macro overnight, or whenever you import the data. we dont' know how you import the data.. is it small discrete parcels of data addign to the tabel, is it a periodic while table load... it all makes a difference.

    generally when importign data from another systemk I prefer to use a VBA process, whcih despite being a lot mor elabour intensive (to sdesign, test, debug and so on(, it does allow the flexibility on how to handle errors, how to monitor the system, and perhaps just as importantly how to reprot to the data consumers what has happened. whether thats an email to me or the users sayign what actually happened. naturally Im not dumb enough to send the error messages to the users, usually its:-
    "aren't we clever, we successfully stuffed 13,456 rows into your customer table last night"
    or if im feelign vindicitive (and I usually am with the accoutants
    "your data update failed because you failed to set up the customer masterfiles as you claimed you would. because of your incomeptence 2,345 sales orders were not processed. assuming you can get of your..... and do what you are paid to do then we will have another go tomorrow night"

    Never let it be said I don't have a soft spot for accountants, because its simply not true. I do have a soft spot for them....
    ...its the quick sands off Maltreath, and Ill happily take any accountants wishing to go there free of charge (I'll even chuck in a bit of chain (round their legs) as we approach the quick sands )
    I'd rather be riding on the Tiger 800 or the Norton

  10. #9
    Join Date
    May 2015
    Posts
    6
    Thanks for the reply, it left me laughing on the floor. I have dealt with accountants (and lawyers) with issues in the past and understand why they should take long walks off short piers...

    I'll keep plugging away and do my best to keep this simple and yet provide the reports needed. In this instance I am building a module that might be deployed across the company if they like it. It is not my primary job but if I wait for the in house process to make stuff I'll be retired before the approval makes it through to the developers...

    Ill post more questions as I run into stuff I cannot Google my way out of!

    Cheers!

    Doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •