Need an idea only: How to combine 2 tables

[banem2]

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!

 

[Jeff Boyce]

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
1
2
3
4
5

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
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.

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
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'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?


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[banem2]

See comments in-line below:





If you have more than 2 GB of data, consider using a SQL-Server or other
more robust "back-end" for your data.




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
...?





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.





This may be a technical quibble, but "<>" is NOT "Not Like", it is "Not
Equal To".




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.





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?




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".


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?

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

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.

 

[Jeff Boyce]

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