 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Need an idea only: How to combine 2 tables
Hello. I have already create a program using test records and it is working (partially thanks to help I got here). Using larger set of data it soon hits limit of 2 GB in size. Basic idea is to use one table and connect each ID with each other ID's without repeating. Simplified table is fairly simple: ID 1 2 3 4 5 From this table I need to have another table created, like: ID1, ID2 1, 2 2, 5 3, 4 4, 2 5, 1 Appearance of second ID's is random and condition is ID1 <> ID2 (ID1 Not Like ID2). To make it simple and to have best speed I have solved this by creating a copy of first table and by creating query with both tables in without relation. That query produce all possible combinations (5*5-5 records, that is X*X-X) records and then pick first 5 (that is X) records and write them in second table. In this case 20 records. Problem is with "large" amount of records, say with 10.000. Then it will try to create 10.000*10.000-10.000 records which is too much for Access. If I change parameter of query from VBA (thanks to advice from here) to have Top(Count of records in first table) I can have only 10.000 records in temp table, but this query takes equal long time to run. Fairly said, if I can solve it other way, 10k records is not much to handle in Access. Well, I run out of ideas, but I am interested if anyone here had similar problem? I will rather use VBA for this, but for some reason client insist on SQL (queries). Are there any solution to use same table twice in query, find only first n records with condition ID1 <> ID2 which will work at acceptable speed? I will appreciate any ideas. Thanks! See comments in-line below: > Hello. I have already create a program using test records and it is > working (partially thanks to help I got here). > > Using larger set of data it soon hits limit of 2 GB in size. If you have more than 2 GB of data, consider using a SQL-Server or other more robust "back-end" for your data. > Basic idea is to use one table and connect each ID with each other > ID's without repeating. Why? Are you saying you want all possible combinations of IDs (1 with 2, 1 with 3, 1 with 4, ... 1 with 'n', 2 with 1, 2 with 3, ...) ... and are you talking about unique combinations (1 with 2 is NOT "same as" 2 with 1) or ...? > Simplified table is fairly simple: > ID [quoted text clipped - 5 lines] > > From this table I need to have another table created, like: Why do you need another table? If you are trying to get the combinations, you could use a Cartesian Product in a query to do that, without having to build/create another table. > ID1, ID2 > 1, 2 [quoted text clipped - 5 lines] > Appearance of second ID's is random and condition is ID1 <> ID2 (ID1 > Not Like ID2). This may be a technical quibble, but "<>" is NOT "Not Like", it is "Not Equal To". > To make it simple and to have best speed I have solved this by > creating a copy of first table and by creating query with both tables > in without relation. That query produce all possible combinations > (5*5-5 records, that is X*X-X) records and then pick first 5 (that is > X) records and write them in second table. In this case 20 records. Again, why? Why do you feel you need a second table? We aren't there, so we can't see what you're working with. > Problem is with "large" amount of records, say with 10.000. Then it > will try to create 10.000*10.000-10.000 records which is too much for [quoted text clipped - 6 lines] > Well, I run out of ideas, but I am interested if anyone here had > similar problem? I'm not clear what problem you are trying to solve. Is this a "math problem", or is there a reason you are using the combinations? > I will rather use VBA for this, but for some reason client insist on > SQL (queries). If you have VBA in mind, what is it? As a matter of performance, if you iterate through a recordset in VBA and take action on each row, it will generally be MUCH slower than using queries, since these are "set-oriented". >Are there any solution to use same table twice in > query, find only first n records with condition ID1 <> ID2 which will > work at acceptable speed? Is this the statement of the problem to be solved? Are you trying to find some number (n) of records that combine two copies of a list of numbers, where the numbers don't match?  SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > I will appreciate any ideas. Thanks! > See comments in-line below: > [quoted text clipped - 93 lines] > > > I will appreciate any ideas. Thanks! I think I have explained well. Many "why" does not help much, as I cannot ask buyer "why". Goal is to create from one table ID's random self-connections with same ID with one condition: ID1 in second table must be different than ID2 (as on sample above) and queries cannot create more records than the number of records in starting table. If you have any ideas, I will appreciate that. Rest of requirements will just complicate my question, but here it is: from table with ID, users, passwords and links, program should generate random connections between links with different users and passwords (there are other data too: servers, groups, etc). Basically I don't have idea how to create that recordset from one table to populate second table. Simple and fair answer is an SQL which will create records in second table using ID's only without VBA. Please remember that this is a "volunteer" newsgroup. Most of the folks who offer help here do so on their own time. If you are asked to explain/describe further what you are trying to do (and why), it probably is because the person reading your description doesn't understand enough to offer specific suggestions. Perhaps someone else reading your post will have a better "picture" of what you are trying to do... You can always choose to ignore requests for clarification, but you risk getting answers that are more general. Best of luck on your project... Regards Jeff Boyce Microsoft Office/Access MVP > > See comments in-line below: > > > > <ban...@gmail.com> wrote in message news:7517d1d7-c13f-4faf-b202-106dafc4bcc5@d50g2000hsf.googlegroups.com... > > > Hello. I have already create a program using test records and it is > > > working (partially thanks to help I got here). [quoted text clipped - 104 lines] > Simple and fair answer is an SQL which will create records in second > table using ID's only without VBA. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.