merge two tables by specific criteria 2

[dashusa]

Hello,
i have two tables that i want to join they both have multiple fields and i need to "merge" them together.
Is ther a way i can merge these two table so that the invoice numbers will match on both tables? and display the correct data?

Thanks d

 

[josephwc]

Is there an Invoice Number field in both tables? Is there a one record to one record relationship to these two tables? Do you want to combine the data in the two tables into one table or do you want to create a linked query that combines the data from the two seperate table. Please provide some important field names, version of Microsoft Access, and more detail about what you are trying to do.

 

[dashusa]

thanks for the prompt reply,
Access version is 2003
yes there is an invoice number in both tables
the relationship is one to many and there are unique records in both tables.
as for combing the data either is fine i think unless the is a reason that one is better

 

[lespaul]

what is

the correct data

?

Table1
Invoice Number ..... other fields

Table2
Invoice Number ..... other fields

RESULTS =
Invoice Number .... ?????

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dashusa]

the final result that i want is to display all the data from both tables with out having redundent data.So if
the invoice numbers match it displys that data only once but it also displays the other data
does that help?

Thanks
david

 

[lespaul]

you mean like:

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2

 

[dashusa]

im still learning access so please bear with me..
do i put that into a query?

please use a bit more detail..THANKS

 

[lespaul]

please use a bit more detail..THANKS

That's exactly what I was asking YOU to do.

Switch the query veiw from Query Design Grid to SQL view and paste. Fix the tablenames.

 

[dashusa]

alright sorry i know how fustrating it is to deal with a newbie
let me see if i can give you a better picture.
the first table is called JCOrdersBB and it has the following recrds in it:
ID(autonumber)
Catorgory(110)
Vendors(Sprint)
JCDetail_Invoice(051105SCH)
Amount($920.32)
Description(conf calls)
Gross($920.32)

The table i would like to merge it with is JCOrdersBB

ID (autonumber)
Date (02/11/2005)
Invoice(021105SCH)
Amount()
Memo()
Vendor(Sprint Conferencing Services)
check_Number(37290)

As you can see the only record that matches is the Invoice Number.

 

[lespaul]

Well, both those tables have the same name, so I made one of them '2' and the invoice number for each record doesn't match, but this query will return every field from both tables on the same record.

SELECT * FROM JCOrdersBB
INNER JOIN JCOrdersBB2 on JCOrdersBB.JCDetail_Invoice = JCOrdersBB2.Invoice

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dashusa]

Sorry one of the tables is JCOrdersQB

I tried This:
SELECT * FROM JCOrdersBB,JCOrdersQB
Where JCOrdersBB.Invoice = JCOrdersQB.Invoice
Seems to work ...
any reason why i shoud'nt use it?
Please let me know if its wrong
Thanks VERY much for all your help!!

 

[lespaul]

no, it's not wrong, it's creating a cartesian join (take the number of rows in each table and multiple them, that's the number of results in the query you created).

with the correct table name:

SELECT * FROM JCOrdersBB
INNER JOIN JCOrdersQB on JCOrdersBB.JCDetail_Invoice = JCOrdersQB.Invoice

read up on joins at Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dashusa]

Aaah
i remember something about cartesian products from college
when i try:
SELECT * FROM JCOrdersBB
INNER JOIN JCOrdersQB on JCOrdersBB.JCDetail_Invoice = JCOrdersQB.Invoice

it brings up a screen to enter parameter value??
not sure what to do here...

 

[PHV]

SELECT * FROM JCOrdersBB,JCOrdersQB
Where JCOrdersBB.Invoice = JCOrdersQB.Invoice
The equivalent:
SELECT *
FROM JCOrdersBB
INNER JOIN JCOrdersQB ON JCOrdersBB.Invoice = JCOrdersQB.Invoice

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dashusa]

Yup that works well!
SO just to be sure with the code:
SELECT *
FROM JCOrdersBB
INNER JOIN JCOrdersQB ON JCOrdersBB.Invoice = JCOrdersQB.Invoice
Iwont have A Cartessian Product problem??

 

[PHV]

You didn't have a cartesian product problem due the WHERE clause ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886