query with duplicated data

[karmela]

Hi,

I'm trying to do my first database and I want to compare 2 tables.

Tbl 1:
Invoice nmr Part nmr Value Price
1001 A 601 100
1001 B 601 500
1001 C 602 200
1002 A 601 100
1003 D 602 550

Tbl2:
Invoice nmr Value SumPrice
1001 601 600 .... (part A+B)
1001 602 200 ..... (part C)
1002 602 100 ..... (part A)
1003 602 550 ...... (part D)

Relations data are invoice nmr, sources of both tables are different, so I
need to compare it.
But I get this query with duplicated values.

Invoice nmr Value Tbl2 Value Tbl1 SumPrice
1001 601 601 600 .... (part
A+B)
1001 602 601 200 ..... (part
C)
1001 601 602 600 .... (part
A+B)
1001 602 602 200 ..... (part
C)
1002 602 601 100 .....
(part A)
1003 602 602 550 ......
(part D)


I think the solution is not so complicated, but I can't get it.

Thanks for any help.

karmela

 

[Biz Enhancer]

Hi,

I'm trying to do my first database and I want to compare 2 tables.

Tbl 1:
Invoice nmr Part nmr Value Price
1001 A 601 100
1001 B 601 500
1001 C 602 200
1002 A 601 100
1003 D 602 550

Tbl2:
Invoice nmr Value SumPrice
1001 601 600 .... (part A+B)
1001 602 200 ..... (part C)
1002 602 100 ..... (part A)
1003 602 550 ...... (part D)

Relations data are invoice nmr, sources of both tables are different, so I
need to compare it.
But I get this query with duplicated values.

Invoice nmr Value Tbl2 Value Tbl1 SumPrice
1001 601 601 600 .... (part A+B)
1001 602 601 200 ..... (part C)
1001 601 602 600 .... (part A+B)
1001 602 602 200 ..... (part C)
1002 602 601 100 ..... (part A)
1003 602 602 550 ...... (part D)


I think the solution is not so complicated, but I can't get it.

Thanks for any help.

karmela

Hi Karmela,

Use a GROUP BY statement to condense duplicate values.

SELECT tbl1.[invoice nmr], tbl2.Value, tbl1.Value, tbl2.SumPrice
FROM tbl1 INNER JOIN tbl2 ON tbl1.[invoice nmr] = tbl2.[invoice nmr]
GROUP BY tbl1.[invoice nmr], tbl2.Value, tbl1.Value, tbl2.SumPrice;

Nick.

 

[karmela]

Hi,

thanks for help, but it doesn't function...
I have this SQL

SELECT Porovnání_úÄtu.[Číslo dokladu], ZaúÄtovano_úÄty.ÚÄet,
Porovnání_úÄtu.Kontace, ZaúÄtovano_úÄty.Částka
FROM Porovnání_úÄtu INNER JOIN ZaúÄtovano_úÄty ON Porovnání_úÄtu.[Číslo
dokladu] = ZaúÄtovano_úÄty.[Číslo dokladu]
GROUP BY Porovnání_úÄtu.[Číslo dokladu], ZaúÄtovano_úÄty.ÚÄet,
Porovnání_úÄtu.Kontace, ZaúÄtovano_úÄty.Částka;


And I get this, just lines with "x" are correct. I cannot make relation
ÚÄet-Kontace, because I compare those values and they can be different in
both sourcetables.

Číslo dokladu ÚÄet Kontace Částka
31122807 601500 601500 302,1 x
31122808 601500 601500 179294 x
31122808 601500 602500 179294
31122808 602500 601500 140972,8
31122808 602500 602500 140972,8 x



Thanks.. karmela

Hi,

I'm trying to do my first database and I want to compare 2 tables.

Tbl 1:
Invoice nmr Part nmr Value Price
1001 A 601 100
1001 B 601 500
1001 C 602 200
1002 A 601 100
1003 D 602 550

Tbl2:
Invoice nmr Value SumPrice
1001 601 600 .... (part A+B)
1001 602 200 ..... (part C)
1002 602 100 ..... (part A)
1003 602 550 ...... (part D)

Relations data are invoice nmr, sources of both tables are different, so I
need to compare it.
But I get this query with duplicated values.

Invoice nmr Value Tbl2 Value Tbl1 SumPrice
1001 601 601 600 .... (part A+B)
1001 602 601 200 ..... (part C)
1001 601 602 600 .... (part A+B)
1001 602 602 200 ..... (part C)
1002 602 601 100 ..... (part A)
1003 602 602 550 ...... (part D)


I think the solution is not so complicated, but I can't get it.

Thanks for any help.

karmela

Hi Karmela,

Use a GROUP BY statement to condense duplicate values.

SELECT tbl1.[invoice nmr], tbl2.Value, tbl1.Value, tbl2.SumPrice
FROM tbl1 INNER JOIN tbl2 ON tbl1.[invoice nmr] = tbl2.[invoice nmr]
GROUP BY tbl1.[invoice nmr], tbl2.Value, tbl1.Value, tbl2.SumPrice;

Nick.