Query in Access

Question

I have 2 tables like which I would like to query to form a new table.

table 1

 number    type serial index
1000001     613      3     1
1000001     613      3     1
1000001     613      3     1
1000001     613      3     1
1000001     613      4     1
1000001     613      3     1

table 2

 number    type serial index
1000001     613      3     2
1000001     613      3     3
1000001     613      3     4
1000001     613      3     4
1000001     613      4     2
1000001     613      3     1

I am looking for a query which gives a resultant of indexes and numbers like this, and adds the index, and gives the result:

output table

 number  index
1000001      3
1000001      4 
1000001      5 
1000001      5 
1000001      3
1000001      2

I want to prevent cross joins here. I can't use where clause because the numbers are same in both tables only the index varies. How can I execute this one-to-one and not one-to-many.

Answer 1

I don't think it's possible to do what you are asking. You would have to be able to distinguish each row uniquely to guarantee a 1-to-1 result (this is normally the job of your index column or ID column).

You might be able to jerry-rig something with rownum, however, if the tables are identically ordered, then perhaps you've over-normalised. Table 1 and 2 should really just be one, and you have two index columns (index1 and index2) in the one table.

Answer 2

Based on my understanding of the problem, you want to add the index column on the equivalent rows in each table. Since MS Access has no row number function like database servers, what I can suggest is temporarily add a new autonumber field for each table. This will give you something to inner join on and then add.

Say you call this column tempAuto on both tables.

SELECT t1.number, (t1.index + t2.index) AS index 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.tempAuto = t2.tempAuto;

You can then delete the extra columns after you have performed whatever data manipulation.

Answer 3

You'll need to know how to setup this counter function, Qcntr() : (See http://support.microsoft.com/kb/94397)

Select Counter, number, Max(index) + 1 as new_Index
From
(
  (Select Number, index, Qcntr(number) as Counter from [Table 1])
   Union
  (Select Number, index, Qcntr(number) as Counter from [Table 2])
) as both_tables
Group by Counter, number

You can append this to your output table without the Counter field.