|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
1200+ fellow developers rate and compare features of the top IDEs, like Visual Studio, Eclipse, RAD, Delphi and others, across 13 categories. Enjoy this FREE Download of the IDE User Satisfaction Study by Evans Data Corporation. Download Now!
|
|
#1
May 7th, 2008, 01:53 PM
|
|||
|
|||
|
Access SQL Code -- Ambiguous Outer Joins
If I could get somoene to look over this code for me I'm not sure why I'm getting the AOJ error but I've been
 for 3 days now. When I run the joins as a 1-1 join it works but I don't get the data to come out proper, if I get rid of the joins I overload my Lock Space. Please helpCode:
INSERT INTO tblSaleHistoryData ( PropertyIndex, RecordID, OldRecordID, SaleDate, Grantor, Grantee, SalePrice, EffectiveSalePrice, DeedBookVolume, DeedBookPage, MonthsOnMarket, [Memo], VerificationDate, EmployeeID, LandValueAtSale ) SELECT tblPropertyMain.PropertyIndex, tblChecklist.RecordID, ESI_PLIST.RECORD_ID, ESI_SALES_DATA.DATE_OF_SALE, ESI_SALES_DATA.GRANTOR, ESI_SALES_DATA.GRANTEE, ESI_SALES_DATA.CONSIDERATION, ESI_SALES_DATA.ACTUAL_CONSIDERATION, ESI_SALES_DATA.DBV, ESI_SALES_DATA.DBV_PAGE, ESI_SALES_DATA.MONTHS_ON_MARKET, IIf(IsNull([CLASSIFICATION]),Null,[CLASSIFICATION] & " ") & IIf(IsNull([SALES_COMMENT]),Null,[SALES_COMMENT] & " ") & IIf(IsNull([DESCRIPTION_1]),Null,[DESCRIPTION_1] & " ") & IIf(IsNull([DESCRIPTION_2]),Null,[DESCRIPTION_2] & " ") & IIf(IsNull([DESCRIPTION_3]),Null,[DESCRIPTION_3] & " ") & IIf(IsNull([SALES_HISTORY]),Null,[SALES_HISTORY] & " ") & IIf(IsNull([INCOME_EXPENSE_STABILIZED_DATE]),Null,[INCOME_EXPENSE_STABILIZED_DATE] & " ") & IIf(IsNull([OTHER_COMMENT]),Null,[OTHER_COMMENT] & " ") & IIf(IsNull([OTHER_COMMENT2]),Null,[OTHER_COMMENT2]) AS New, ESI_BUILDING_SALES.SURVEY_DATE, TEMP_Employee.ID, ESI_BUILDING_SALES.LAND_VALUE_AT_SALE FROM ESI_INDUSTRIAL_SALES, ((((tblPropertyMain INNER JOIN (ESI_PROPERTY INNER JOIN ESI_PLIST ON ESI_PROPERTY.PROPERTY_ID = ESI_PLIST.PROPERTY_ID) ON tblPropertyMain.OldPropertyID = ESI_PROPERTY.PROPERTY_ID) INNER JOIN ESI_SALES_DATA ON ESI_PLIST.RECORD_ID = ESI_SALES_DATA.RECORD_ID) LEFT JOIN ESI_BUILDING_SALES ON ESI_PLIST.RECORD_ID = ESI_BUILDING_SALES.RECORD_ID) LEFT JOIN TEMP_Employee ON ESI_BUILDING_SALES.SURVEYOR = TEMP_Employee.Field1) INNER JOIN tblChecklist ON tblPropertyMain.PropertyIndex = tblChecklist.PropertyIndex WHERE ((([ESI_PROPERTY].[Block])=No) AND ((ESI_PROPERTY.PROPERTY_TYPE)="ind")) ORDER BY ESI_SALES_DATA.ACTUAL_CONSIDERATION DESC;
|
|
#2
May 8th, 2008, 06:07 AM
|
||||
|
||||
|
the weird nesting of joins might be the problem
also, you are not joining ESI_INDUSTRIAL_SALES properly, so every row from that table is going to match with every row from the other tables what does "When I run the joins as a 1-1 join" mean?
|
|
#3
May 8th, 2008, 07:56 AM
|
|||
|
|||
|
I was meaning that it will display all records from both tables... didn't mean 1-1 sorry...
Edit: Also of note the query itself was built using the Design View so any weird nesting could have to do with that.
|
|
#5
May 8th, 2008, 08:35 AM
|
|||
|
|||
|
when I modify the query like so
Code:
INSERT INTO tblSaleHistoryData ( PropertyIndex, RecordID, OldRecordID, SaleDate, Grantor, Grantee, SalePrice, EffectiveSalePrice, DeedBookVolume, DeedBookPage, MonthsOnMarket, [Memo], VerificationDate, EmployeeID, LandValueAtSale ) SELECT tblPropertyMain.PropertyIndex, tblChecklist.RecordID, ESI_PLIST.RECORD_ID, ESI_SALES_DATA.DATE_OF_SALE, ESI_SALES_DATA.GRANTOR, ESI_SALES_DATA.GRANTEE, ESI_SALES_DATA.CONSIDERATION, ESI_SALES_DATA.ACTUAL_CONSIDERATION, ESI_SALES_DATA.DBV, ESI_SALES_DATA.DBV_PAGE, ESI_SALES_DATA.MONTHS_ON_MARKET, IIf(IsNull([CLASSIFICATION]),Null,[CLASSIFICATION] & " ") & IIf(IsNull([SALES_COMMENT]),Null,[SALES_COMMENT] & " ") & IIf(IsNull([DESCRIPTION_1]),Null,[DESCRIPTION_1] & " ") & IIf(IsNull([DESCRIPTION_2]),Null,[DESCRIPTION_2] & " ") & IIf(IsNull([DESCRIPTION_3]),Null,[DESCRIPTION_3] & " ") & IIf(IsNull([SALES_HISTORY]),Null,[SALES_HISTORY] & " ") & IIf(IsNull([INCOME_EXPENSE_STABILIZED_DATE]),Null,[INCOME_EXPENSE_STABILIZED_DATE] & " ") & IIf(IsNull([OTHER_COMMENT]),Null,[OTHER_COMMENT] & " ") & IIf(IsNull([OTHER_COMMENT2]),Null,[OTHER_COMMENT2]) AS New, ESI_BUILDING_SALES.SURVEY_DATE, TEMP_Employee.ID, ESI_BUILDING_SALES.LAND_VALUE_AT_SALE FROM ESI_INDUSTRIAL_SALES, ((((tblPropertyMain INNER JOIN (ESI_PROPERTY INNER JOIN ESI_PLIST ON ESI_PROPERTY.PROPERTY_ID = ESI_PLIST.PROPERTY_ID) ON tblPropertyMain.OldPropertyID = ESI_PROPERTY.PROPERTY_ID) INNER JOIN ESI_SALES_DATA ON ESI_PLIST.RECORD_ID = ESI_SALES_DATA.RECORD_ID) INNER JOIN ESI_BUILDING_SALES ON ESI_PLIST.RECORD_ID = ESI_BUILDING_SALES.RECORD_ID) INNER JOIN TEMP_Employee ON ESI_BUILDING_SALES.SURVEYOR = TEMP_Employee.Field1) INNER JOIN tblChecklist ON tblPropertyMain.PropertyIndex = tblChecklist.PropertyIndex WHERE ((([ESI_PROPERTY].[Block])=No) AND ((ESI_PROPERTY.PROPERTY_TYPE)="ind")) ORDER BY ESI_SALES_DATA.ACTUAL_CONSIDERATION DESC; So that there are no Outer Joins The query will run but will lock more than 100,000 records which is where I reset my limit to in the registry
|
|
#6
May 8th, 2008, 08:39 AM
|
||||
|
||||
|
you are not joining ESI_INDUSTRIAL_SALES properly, so every row from that table is going to match with every row from the other tables
|
|
#7
May 8th, 2008, 08:59 AM
|
|||
|
|||
|
This boys and girls is why I come here, becuase when I come here one of you always seems to kick me in the head and turn my brain on... that particular table I noticed didn't have any joins on it from the beginning and I didn't know why but as this was used by my predecessor here I figured that it was set up properly... needless to say it wasn't and now that query is working... onto the next sticking point which hopefully I won't need to stop here for... Thank you r937 that was very helpful
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Access SQL Code -- Ambiguous Outer Joins |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
