Joining and grouping using SQL

[Ciar?n]

I have two tables... Table1 and table2 and I need to reconcile them
with each other.

Table1 has the fields Product number, invoice number, price, vat
amount and total.

Table2 has the same data but in a slightly different format...

It has Product Number, invoice number, Price and type.
Type will say Vat or sale and amount will be the vat amount or sale
amount

What is on one row in Table1, will be spread accross 2 rows in Table2.

It means that Invoice number is not unique in Table2.

How do I either group the data in Table2, so I can join it with Table1
or make Table2 the same format as Table1.

If there is something else you can think of to help me, by all means
suggest away.

Regards,
Ciarán

 

[Steve Schapel]

Ciarán

Can you explain a bit more about what you are trying to achieve? On the
face of what you have told us so far, I can't see the reason for the
existence of Table1. It is of an incorrect design for a database table,
and it contains the same information as Table2 so it therefore also
flouts another principle of database design. It seems to me that you
can delete Table1 altogether from your database and your problem will be
solved.

 

[Ciar?n]

Steve,

I know what you are saying, but my problem is that Table 1 and Table2
are from different sources and what I want to validate is if they
contain the same data.

Regards,
Ciarán

 

[Steve Schapel]

Ciarán,

Ok, thanks for the further explanation.

I think I would tackle it like this... Make another table Table3 which
has the same structure as Table2. Then run an Append Query based on
Table1 (Product number, invoice number, total, and type="sale") to
create records in Table3 for Product Number, invoice number, Price,
Type. And then another Append Query for the VAT data. Then table3
should correspond with table2, and you can make a query to compare the
data between table2 and table3. Hope that makes sense... please post
back if you need more detailed help with this.

 

[Ciar?n]

Steve,

Thanks for the help.
I'll try it out and if you don't hear back from me, you can presume it went well.
Cheers again!!

Ciarán