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.

What differentiates the records you want to add together? The row number? — achinda99
– achinda99, Commented Feb 17, 2009 at 15:20
n.b.: looks like index_result = index_table_1 + index_table_2 — BIBD
– BIBD, Commented Feb 17, 2009 at 15:24
Yes but link one-to-one based on what condition. The data appears to be based on row number but I wanted to verify that. My answer below should achieve what tksy wants. — achinda99
– achinda99, Commented Feb 17, 2009 at 15:30

BIBD · Accepted Answer · 2009-02-17 15:22:56Z

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.

answered Feb 17, 2009 at 15:22

BIBD

15.5k26 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

achinda99 · Accepted Answer · 2009-02-17 15:29:19Z

1

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.

answered Feb 17, 2009 at 15:29

achinda99

5,0784 gold badges37 silver badges42 bronze badges

Comments

JeffO · Accepted Answer · 2009-02-17 18:04:50Z

0

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.

answered Feb 17, 2009 at 18:04

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Collectives™ on Stack Overflow

Query in Access

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related