Help with something similar to join?

[lagunov]

Hi! I need to make a query that returns two fields from diferent tables, in a new table.
For example, I have the following two tables. Their fields have the same amount of records:



Table 1, Field 1
1
5
6
8
12

Table2, Field 1
4
8
9
10
15

Having that, I need to get the following query:

Query 1, Field 1
1
5
6
8
12

Query 1, Field 2
4
8
9
10
15

Can anybody help me with this, please?
Thanks.

[NTC]

your post has them stacked but I think your result that you seek is this:
1 4
5 8
6 9
8 10
12 15

which is 2 fields per record. And so the question is; how does the db know that 1 goes with 4, and that 5 goes with 8?

You imply it by displaying them in that order. However a database doesn't have any real order. So you need another field that is common to the two. Try an add
an autonumber field to both table. In doing so it will immediately apply a value to each record. Autonumber isn't easily controllable so this may or may not work. But hopefully it will start with 1 for each - and presuming your table is sorted correctly then they will be both numbered in a way that the autonumber field value then is common in both tables per record.

You can then make a join on the autonumber field and get your results.

Hope this helps.

[lagunov]

Hey! Thanks for your idea. I think it is going to work. I'm sorry for answering so late.
But although I think its a good idea I think that adding an autonumber field is as difficult as adding any other kind of field. So, if I could do it, I would directly add the Field 1 from the Table 2 into the Table 1.
So, what I'm saying is that I don't know how to do what you suggest. Could you help me with that too?

Thanks again.

[NTC]

Adding an autonumber field requires simply and literally adding it to the table when the table is in design view.

The key issue is the point that to get the correct 2 values in the same record in the query result - that there must be a common, unique value appearing in both tables that allows you to join them correctly in the query.

Autonumber is one way to consider if one must add a large quantity of values to existing tables. Another way is simply doing it manually. Then you must also view this issue, maybe, in terms of modifying the existing data - vs - going forward process. Because in your going forward process you don't want to be adding data in 2 separate tables that are not getting at the same time the correct cross referencing value (and you don't want to rely on separate autonumber fields either). But you haven't mentioned any going forward concerns so maybe it isn't an issue and you are only working with existing data.

Adding an autonumber field is such a basic task - that you really need to get an Access textbook. One can not really design or maintain a database without a fundamental textbook covering all the key tasks. Easily found at Amazon or any big book store.

[lagunov]

Ok. Let me explain you.
I know how to add an autonumber field in design mode.
I thought you were suggesting to do it in the query. So I asked for help to do that.

I need to do the mentioned operation many times, so autonumeric should be the right kind of field. But here's something else. The two tables that I'm talking about are actually the same table. So, I don't have two tables, I have two queries. And that's a problem because there're never going to be two diferente records with the same Id (the autonumeric value).

To make an example, I could say that from the following table, I get two queries like theese:

Table 1
1
2
4
8
10
15
20

Query 1 (all records excepting the last)
1
2
4
8
10
15

Query 2 (all records excepting the first)
2
4
8
10
15
20

And I finally need to get the following query, which has two fields (and this is when I need help):
1 and 2
2 and 4
4 and 8
8 and 10
10 and 8
15 and 10
20 and 15

[weekend00]

as NTC point out in his first reply #2: how does the db know that 1 goes with 4, and that 5 goes with 8?

in your original post, you put 1 goes with 4, and then in your last post you put 1 goes with 2?

in your last post, query 1 has 6 rows, query2 has 6 rows, and you want 7 rows without telling us the logic of how to match them.

Maybe you know the logic clearly, but I am very confused.

[NTC]

well - in the end - the fundamentals are that to join (whether or not a self join) there must be an identical crossreferencing value in the 2 records to join upon.

so that's really the issue. per my first post: how does the db know that 1 goes with 4, and that 5 goes with 8? It all comes down to some identifying value in both records that you can join them together with....

Hope this helps a little.

[lagunov]

Thanks to both. Everything helps!

1. weekend00: I gave two different examples so, the only thing they have in common is the act of joining records that have the same ordinal position i.e. the first record of the query 1 with the first record of the query 2, the second record of the query 1 with the second record of the query 2, and so on.

2. NTC: I understand your idea, but I don't know how to do that. My problem is that everything comes from the same table. In terms of my last example, I only have the table 1 and the rest are just queries. So, how do I get that value in both records so I can join them together?