Page 5 of 6 FirstFirst 123456 LastLast
Results 61 to 75 of 76
  1. #61
    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
    The best option should be the query I offered but since that isn't working with your 1,000,000 records I am at a loss. Not sure any of the options I listed will perform any faster. Some of the performance issue might be your network.



    I think I have contributed all I can to this discussion. You just might have to find a professional VB/VBA programmer who also has an understanding of SQL database and split design running on network.
    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.

  2. #62
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    alright thanks for the help

  3. #63
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Tried to follow this up, but got stuck somewhere in the middle. What is the exact problem with the query approach atm? You don't get them to run, or the performance is to bad? If it doesn't even run, we should get to make it run. If the performance is to bad, than we should investigate the filters used, and see if the tables on the server side have the needed indices set. Would you please repost your current query code or give me a backreference to the current sql code?

  4. #64
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    SELECT Number, MI, [First Name], [Last Name], DOB
    FROM Table1
    WHERE Number & MI & DOB IN (SELECT Number & MI & DOB AS ID FROM Table1 GROUP BY Number, MI, DOB HAVING Count(*) > 1);

  5. #65
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Okay, lets take this down. First of all we need to get rid of the calculated field. It destroys any possibility for the sql server to even use indices. As you can't use the IN operator on multible values we will need to change this to a join. The resulting query would be something like this:

    SELECT Table1.Number, Table1.MI, Table1.[First Name], Table1.[Last Name], Table1.DOB
    FROM Table1 INNER JOIN (SELECT Number, MI, DOB FROM Table1 GROUP BY Number, MI, DOB HAVING Count(*) > 1) AS Table1Counts ON Table1.Number = Table1Counts.Number AND Table1.MI = Table1Counts.MI AND Table1.DOB = Table1Counts.DOB;
    Lets see how this performs. If it doesn't work, add an index to Number, MI, DOB on the server side.
    Last edited by hapm; 06-02-2014 at 08:38 AM. Reason: wrote can where it should be can't

  6. #66
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I am sorry I am new to SQL can you explain what that statement is suppose to do?

  7. #67
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Do you work with sets in mathlab? What you do here, is defining 2 sets: Table1 and Table1Counts, where Table1Counts contains all unique combinations of Number, MI, DOB, where there are more than 1 record for them in Table1. Then you join them with a relation. The relation uses the comparision of the Number, MI and DOB fields as the relation function. The result is an intersection of the two sets, containing only the records of Table1, where there are more then one records in Table1, that contain the same combination of Number, MI and DOB.

    Hope this doesn't confuse you more than it helps. The thing is, when using a relational database, you don't normally try to handle each record in a table on its own, but you try to see the table as a set, and find a functional description between your sets to generate new sets. As I understood your problem you want to do something with all records that have the same combination of Number, MI and DOB. If so, the query you got here, is a good point to start with, as it gives you exactly this records.

  8. #68
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    It seems that when i apply this statment in my actual query the results I get are not what i expected it is because the number and MI are not always the same but the DOB is always the same is there a way in the SQl statement to say same combinations in MI or Number?

  9. #69
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Well, than we need to adjust the sub query for Table1Count, as it currently groups over the combination of Number, MI and DOB. You want to group by DOB and (MI or Number) to get the number of records that are the same. This is a little tricky and you'll need an sql UNION for this. As you have a sql server as backend you could be happy, as it is much better in handling unions then access. Here is a try:
    SELECT DISTINCT Table1.Number, Table1.MI, Table1.[First Name], Table1.[Last Name], Table1.DOB
    FROM Table1 INNER JOIN (SELECT DOB, Number, NULL AS MI FROM Table1 GROUP BY DOB, Number HAVING Count(*) > 1 UNION SELECT DOB, NULL AS Number, MI FROM Table1 GROUP BY DOB, MI HAVING Count(*)) AS Table1Counts ON Table1.DOB = Table1Counts.DOB AND (Table.Number = Table1Counts.Number OR Table1.MI = Table1Counts.MI);
    Let me know if it works and how it performs.

  10. #70
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    This does not give me the expected table all the records are people with different DOB so they can not be the same person and some of the MI that are listed are the same and some are not it should be same combination of DOB and (MI or Number)

  11. #71
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Sure you added the ( ) in the expressions of the ON clause?

  12. #72
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    (Table1.Number = Table1Counts.Number OR Table1.MI = Table1Counts.MI); That is how i put it in for that section

  13. #73
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Ou sry, I lost a > 1 some how. Stupid me...

    Code:
    SELECT DISTINCT Table1.Number, Table1.MI, Table1.[First Name], Table1.[Last Name], Table1.DOB
    FROM Table1 INNER JOIN (SELECT DOB, Number, NULL AS MI FROM Table1 GROUP BY DOB, Number HAVING Count(*) > 1 UNION SELECT DOB, NULL AS Number, MI FROM Table1 GROUP BY DOB, MI HAVING Count(*) > 1) AS Table1Counts ON Table1.DOB = Table1Counts.DOB AND (Table.Number = Table1Counts.Number OR Table1.MI = Table1Counts.MI);

  14. #74
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    This did not give me the desired result it was mostly data from different people no matches were displayed.

  15. #75
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Okay, before this gets to exausting searching the error on your side, I have set up a small test db with your data from the first post. Works fine for me now with some error corrections in my query, resulting in:
    Code:
    SELECT DISTINCT Table1.[Number], Table1.MI, Table1.[First Name], Table1.[Last Name], Table1.DOB
    FROM Table1 INNER JOIN (SELECT DOB, [Number], NULL AS MI FROM Table1 GROUP BY DOB, [Number] HAVING Count(*) > 1 UNION SELECT DOB, NULL AS [Number], MI FROM Table1 GROUP BY DOB, MI HAVING Count(*) > 1) AS Table1Counts ON Table1.DOB = Table1Counts.DOB;
    See attached db.
    Attached Files Attached Files

Page 5 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering Data from a specified column
    By mr879 in forum Programming
    Replies: 16
    Last Post: 05-15-2014, 10:59 AM
  2. Multiple choice in a single field
    By fabads in forum Access
    Replies: 6
    Last Post: 05-21-2013, 11:45 AM
  3. Replies: 2
    Last Post: 08-10-2012, 03:42 PM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Filtering dupe Data in a single field
    By label027 in forum Queries
    Replies: 4
    Last Post: 10-25-2011, 01:18 PM

Tags for this Thread

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