RIGHT OUTER JOIN

[Rick4077]

I spen the last hour delving into the book ACCESS 2003 Bible but for the life of me I can't figure it our.

I have two simply tables.

A matching number field, ie., 448-342-98 in both tables.

I want to add ALL of the records form a smaller table to the large table. I KNOW FOR A FACT that the above field with the number will not have a duplicate record in either table.

Any ideas . . . Rick

 

[Remou]

What SQL did you try?

 

[WildHare]

Do you mean a DUPLICATE record ( > 1 in any one table ) or a MATCHING record from table to table?

If your new, smaller table is, as you say, NOT DUPLICATED in the larger table, then it's just a simple INSERT query, right?

IF, on the other hand, you're looking for matches from the small table, INTO the larger table, to do UPDATES to existing records, then it's a straightforward UPDATE query with the two tables, joined on that matching field you mentioned.

Your thread title "Right Outer Join", doesn't adequately identify either of those thoughts, so I'm a bit confused... ? (not entirely unique experience, I must say..



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[Rick4077]

WildHare . . .

This is really an Append query, correct?

MS Access 2003 Help Files do not list an INSERT QUERY as a separate. Only an INSERT INTO which looks like an SQL.

The following when run states that . . ."You are about to append 0 rows . . ."

INSERT INTO [Copy of RICKSFARM]
SELECT [Copy of RICKSFARM].*
FROM [Copy of RicksData] INNER JOIN [Copy of RICKSFARM] ON ([Copy of RicksData].[Lot SQ FT] = [Copy of RICKSFARM].LotSize) AND ([Copy of RicksData].[SQ FT Structure] = [Copy of RICKSFARM].SqFt) AND ([Copy of RicksData].[Sale Value] = [Copy of RICKSFARM].SalePrice) AND ([Copy of RicksData].[Sale Date] = [Copy of RICKSFARM].Enddt) AND ([Copy of RicksData].[Telephone #] = [Copy of RICKSFARM].Phone) AND ([Copy of RicksData].[Site Address Street Name] = [Copy of RICKSFARM].SitusStName) AND ([Copy of RicksData].[Site Address House Number] = [Copy of RICKSFARM].SitusStNo) AND ([Copy of RicksData].[Mailing Address Zip] = [Copy of RICKSFARM].MailZip) AND ([Copy of RicksData].[Mailing Address State] = [Copy of RICKSFARM].MailState) AND ([Copy of RicksData].[Mailing Address City] = [Copy of RICKSFARM].MailCity) AND ([Copy of RicksData].[Mailing Address] = [Copy of RICKSFARM].MailStreet) AND ([Copy of RicksData].[First Owner Full  Name] = [Copy of RICKSFARM].OwnerName) AND ([Copy of RicksData].APNO = [Copy of RICKSFARM].APNO);

The fields are all text same length.

What am I missing here?

Thanks . . . Rick

 

[WildHare]

Yes, an APPEND query - it does an SQL INSERT..INTO..my bad.

It looks like your query is attempting to append records into CopyOfRicksFarm, where the INCOMING RECORDS match already existing ones..

USUALLY, in an APPEND query, where you're in effect copying records from one table to another you don't put the TARGET table in your query grid. Just bring the SOURCE table into the query designer, move the fields you want down into the grid, change to an APPEND query, and specify the table you want these records appended to. If the field names are alike, you'll automatically get them into the right spots.

Since (I assume) these records don't already exist in your target table, there's NOTHING to match against, hence your "0 Records Appended"....

Appending and Matching usually don't go together...

"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

I want to add ALL of the records form a smaller table to the large table
INSERT INTO [large table]
SELECT *
FROM [smaller table]

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

 

[WildHare]

PVH's example will work perfectly, assuming that field names are the same from table to table, and you want to append ALL fields in the record to the large table...It's all you'll ever need.



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

assuming that field names are the same from table to table
NO !
Same number of fields of compatible data type is sufficient.

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

 

[WildHare]

If the field names don't match, how will Access know which field in Table B to push in to which field in Table A?



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

By ordinal position.

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

 

[WildHare]

I just tried it. Got the "Unknown field name" error.



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

works for me (ac2003)

 

[WildHare]

Odd.. I have a table. Made an empty copy of it. Change the field names to xFieldName throughout. Wrote this SQL:

INSERT INTO TableB
SELECT [Main Address List].*
FROM [Main Address List];

and I get :

"The INSERT INTO statement contains the following unknown field name : PREFIX. Make sure you have typed the name correctly and try again."

That would make sense, since the first field in the EMPTY table is "xPrefix"...

Also using A/2003



"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

OOps, sorry :~/
Didn't realize that my test tables had SAME field names ...

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

 

[WildHare]

Happens to the best of us..


"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[Rick4077]

HEY EVERYONE . . . your postings were EXCELLENT.

I tried PHV's example.

INSERT INTO [large table]
SELECT *
FROM [smaller table]

Ah the simple things in life.

I then ran into the named field MUST be the same error.

I found this strange as Paradox 8 doesn't care about the fields names. It's the field structure that counts in Paradox

So I got the error referrences you found and then renamed the fields names exactly and placed them in the same order.

I had six additional fields in the larger table but they are last and beyond the structure of the small table so were untouched during the query. PERFECT!

Thanks everyone. Thanks ALOT!

Rick

 

[PHV]

Forgot to mention that this limitation doesn't apply to the real life databases I work with (like Informix) ...

 

[WildHare]

PHV - I'm not suprised - Access/Jet is certainly not what you'd call an "industrial strength" database - it has many limitations that I've come to grips with over the years. Having first learned my way around mainframe RDBMS's back in the 80s, I wish there was more to Jet than what M/Soft has managed to develop in 10 years. However, I suppose they don't want to get too close to SQL Server, and Access will pretty much always remain a minor league, desktop DMBS that has some nice features, but also many limitations.



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman