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.

Joining and grouping using SQL

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,
Ciarn[posted and mailed, please reply in news]

Ciar?n (chudson007@.hotmail.com) writes:
> 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.

SELECT ...
FROM Table1 t1
JOIN (SELECT ProductNumber, InvoiceNumber, Price = SUM(Price)
FROM Table2
GROUP BY ProductNumber, InvoiceNumber) AS t2
ON t1.ProductNumber = t2.ProductNumber
AND t1.InvoiceNumber = t2.InvoiceNumber

This may not be exactly what you need; your request is a bit vague. If
you want more help, I suggest that you include:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'd suggest looking at the design of your tables first.

chudson007@.hotmail.com (Ciar?n) wrote in message news:<7f9b6870.0411040804.3e32e925@.posting.google.com>...
> 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,
> Ciarn

Join Tables that don't have exact matches

I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
The second table can have the same Sales Tax Code with a different Tax Rate
and Effective Date. I want to select only one Tax Rate record from the
second table and that is the one that has the greatest effective date that is
also less than the invoice date.
I have tried the Top 1 method in a join and where statement, but it is not
actually limiting the records from the second table.
can you post some sample records and expected result set out of it.
Vishal Parkar
vgparkar@.yahoo.co.in
"Queryless in SLC" wrote:

> I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
> and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
> The second table can have the same Sales Tax Code with a different Tax Rate
> and Effective Date. I want to select only one Tax Rate record from the
> second table and that is the one that has the greatest effective date that is
> also less than the invoice date.
> I have tried the Top 1 method in a join and where statement, but it is not
> actually limiting the records from the second table.

JOIN Process Order and Performance Comparisons

Hi all,
A common SQL that I do is joining parent and child tables together (1-M
relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
number of rows.
Q1) Compare the two statements (that give the same result) below, from a
programming point of view, which one is more efficient?
Statement 1
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
AND IvoItm.ProductType = 1 --This line is processed in WHERE.
Statement 2
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
AND IvoItm.ProductType = 1 --This line is processed in JOIN.
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
This is something which I have been wondering for quite sometime. After
reading MSDN article "Join Fundamentals" stating, it says the JOIN statement
s
are processed first.
Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
process the JOIN?
Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm,
before doing the JOINS? Therefore improving performance, as the amount of
data to join is reduced in the IvoItm?
Q4) Using the same analogy in Q3, would there be performance gain if I
rewrite the statement using sub-query to do the filtering 1st?
SELECT *
FROM Invoice Ivo
INNER JOIN (
SELECT *
FROM InvoiceItem
WHERE ProductType = 1 --This line is processed in sub-query.
) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
Q5) And would above be efficient than using the Statement 1 and 2?Answers inline:
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:3E92F8CB-3E8A-4A4C-A0D9-71B707658D1A@.microsoft.com...
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
These two statements are probably going to perform equivalently, as they are
mathematically equivalent. If this was an outer join, then it will make a
difference. First thing to do is to check the plan using Query analyzer. It
should be the exact same plan.
Logically, all of the JOIN operators will be dealt with, building the set
with Invoice.* + InvoiceLineItem, eliminating rows where the join criteria
fails. Then for every row in the output you apply the where clause.
However, the optimizer can can reorganize the query to make it execute
better as long as the same results would be the same.
For the rest of your questions, try looking at the plan first. It may answer
the questions for you as the answers may be different based on the number of
rows in each table.

> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN
> statements
> are processed first.
>
This is true logically, but it is not required if the results are the same

> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in
> IvoItm,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?|||As noted by Louis, for Inner Joins it doesn't matter whether you specify
the predicates in the WHERE clause or in the JOIN ON clause. For Outer
Joins the meaning is different.
What the query optimizer will do, is analyse which indexes your tables
have and whether they can be sed or scanned in order to reduce the
I/O needed to retrieve the actual data. Then it will do an access path
analysis to see in which order the joins would be fastest. If there is
an appropriate index, then physically, the (partial) filtering will
occur before the join.
Suppose you have a clustered index on Invoice(Date). Then you will
probably see a clustered index s on table Invoice, regardless whether
you used syntax 1 or 2. BTW: the only way to really tell is check the
query plan.
HTH,
Gert-Jan
HardKhor wrote:
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN stateme
nts
> are processed first.
> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm
,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?