basic Sql Query

[Neerja]

Hello all This is a pretty basic query I guess but I am having trouble solving this.. Can anyone help plsssssssss.
I have two tables
Table1
Code Serial_No Amount1 Amount2
1 ABC 100 200
1 ABC 720 220
4 ABC 150 112
9 PQR 11 25
9 ZZZ 122 44
9 ZZZ 1250 42

Table2
Code Serial_No Amount1 Amount2
1 ABC 24 20
1 PPP 72 22
4 ABC 15 12
9 PQR 11 54
9 ZZZ 22 44
9 ZZZ 150 99
and total of the amount as 844 and 440

I have write a query to select only the Code common to both the tables and add total of amounts for these codes. For eg. I want rows
Outpput
1 ABC 100 200
1 ABC 720 220

1 ABC 24 20

Thanks in advance

 

[Ron_dK]

Neerja,

Why would you have two tables with exactly the same fields.
I would suggest to have one table only with fields : Code , Serial_No , Amount1 , Amount2.

You then can create a query based on that table, something like :
total1: DSum('[Amount1]','TbAmount','[serial] = "ABC" and

="1"')
and total2: DSum('[Amount2]','TbAmount','[serial] = "ABC" and [code]="1"').

If you run the query you should get :

code	serial	Amount1	Amount2	Rem	tot1	tot2
1	ABC	100	200	c1	844	440

etc.

Hope that helps.

 

[Neerja]

Help please..

Thanks for the reply Rak,
My two tables are not exactly similar. These columns are part of the two different tables on which I have to query on.
And because these are two different tables when I join the tables the rows are getting duplicated.
Any suggestions

Thanks!!

Nee

 

[Ron_dK]

If you have different tables, create a query that will grab the relevant data from the various fields from the two tables.
Use the total1: DSum('[Amount1]','Tbl1','[serial] = "ABC" and

="1"') query foTbl1 and total1: DSum('[Amount1]','Tbl2','[serial] = "ABC" and [code]="1"') for Tbl2 and add new calc field something like All : Nz(tot1)+Nz(tot2).


Hth

 

[Neerja]

Union

Hi again,
I created two queries to select the desred records from each table.

Now the problem I am facing is that I have s that from the output of these 2 queries, I have to pick records that do not common values in both the tables.

Any suggestions please......!!

Thanks in advance
Nee