Joining two tables

I imported two excel spreadsheets from excel to Access. Invoice Register 2005 table and Invoice Register 2006 table. These excel files were exports from peachtree.

Each client can have multiple invoices in each of these registers. Each client though does have a unique client numbers but in these tables there are duplicate client numbers because off the multiple invoices created throughout the year.

I created a query linking these two tables together and what is happening when I link the two tables using the Client ID numbers the invoices are listing more than once.

Example there is a client with 3 invoices in 2005 and 3 invoices in 2006 (different invoice numbers) so in stead of listing six invoices for this one client there are nine records. The query shows nine invoices but there are only six for this one client - it is duplicating some of these invoices twice.

What I am trying to show is the client ID and Client name and the invoice register for 2005 and 2006. Is this possible?

Lborshard

I think what you need is to UNION the two table instead of JOIN them.|||

I will read up on UNION and give it a try.

Thank you.

lborshard

|||

phe,

Thanks for the help the Union did work - what a learning experience.

The only thing now, is that I need the 2005 Invoice Column/field and the 2006 Invoice column/ field to be seperate columns as I started with in the import.

The query combined the 2005 and 2006 invoices underneath each other. Is there any way I can list these two columns in the union query?

lborshard

Originally when I linked these two seperate tables together it seemed to copy/duplicate some of the invoice. For example: one client had 3 invoices in 2005 and 3 invoices in 2006 - the client ID was the link - Instead of showing 6 individual invoice for that specific client it showed nine.

Join using LIKE ?

I have been given two tables in Excel which I've imported into SQL Server
and I need to join them.
Each of them has a unique Product but there is a bit of inconsistency in
that in Table A, some (but not all) of the Products have been prefixed with
the Supplier Name - I don't have a master list of supplier names yet,
building that will be Stage 2. The Products in Table B are exactly the same
as Table A without the Supplier Name
In other words, the contents of the tables are something like:
Table_A Table_B
Widgets Ltd Special Gizmo Special Gizmo
Widgets Ltd Standard Gizmo Standard Gizmo
Inhouse Special Inhouse Special
Joe Bloggs Standard Gadget Standard Gadget
Joe Bloggs Special Gadget Special Gadget
One Off Product One Off Product
I was thinking of sometthing along lines of
Select *
From Table_A Join Table_B on Table_A.Product LIKE ('%'+TableB.Product)
I can't seem to get the syntax quite right and Googling on using joins with
LIKE can be dodgy.
Any tips or suggestions?here you go
create Table TableA( Product varchar(49))
insert into TableA values ('Widgets Ltd Special Gizmo')
insert into TableA values ('Widgets Ltd Standard Gizmo')
insert into TableA values ('Inhouse Special')
insert into TableA values ('Joe Bloggs Standard Gadget')
insert into TableA values ('Joe Bloggs Special Gadget')
insert into TableA values ('One Off Product')
create Table TableB( Product varchar(49))
insert into TableB values ('Special Gizmo')
insert into TableB values ('Standard Gizmo')
insert into TableB values ('Inhouse Special')
insert into TableB values ('Standard Gadget')
insert into TableB values ('Special Gadget')
insert into TableB values ('One Off Product')
select * from tableB b join TableA a on a.Product like '%' +b.product
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145638327.074438.26100@.t31g2000cwb.googlegroups.com...
> here you go
Thks|||Thx