CODEWITHSUNDEEP
sqlms-access
tksy
tksy

Reputation: 3529

Query in Access

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.

Upvotes: 1

Views: 291

Answers (3)

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 0

achinda99
achinda99

Reputation: 5078

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.

Upvotes: 1

BIBD
BIBD

Reputation: 15384

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.

Upvotes: 1

Related Questions