Need Updatable Query - This is wicked so beware... (1 Viewer)

boblarson · Feb 1, 2011

I haven't done many update queries as I haven't needed to. But those that I've done have been minor.
I have a project right now that uses some pretty extreme queries
and this one is bugging me. I thought I set it up correctly
but it would appear that I didn't as it is telling me it needs an updatable query.

So, here's the update query (in two forms, just for viewing in a couple of different ways -
I've used FMS' tool - LongText/SQL Builder from their Total Visual Code Tools Access add-in to create the second view.
I am also including my append query which works fine. I tried to modify it as there are quite a few fields and calculations in it.
Also, just an FYI - the extra table for this data is required because the users have to modify some of the data manually
before running a report (it is basically building different scenarios and running an Access report off of it, in order to use for prospective sales).

UPDATE QUERY:

Code:

UPDATE ((((tblKeyChar INNER JOIN ((((((((dbo_pselPropertyNarrow LEFT JOIN dbo_wselFiscPerfMaster ON dbo_pselPropertyNarrow.property_id = dbo_wselFiscPerfMaster.property_id) LEFT JOIN dbo_TransSourceUse ON dbo_pselPropertyNarrow.property_id = dbo_TransSourceUse.PropertyFK) LEFT JOIN sbqryKeyCharPermDebtsHard ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharPermDebtsHard.PropertyFK) LEFT JOIN dbo_DSSExternalDevTeam ON dbo_pselPropertyNarrow.property_id = dbo_DSSExternalDevTeam.PropertyFK) LEFT JOIN dbo_TransDevTeamGPs ON dbo_DSSExternalDevTeam.GPOrgFK = dbo_TransDevTeamGPs.DTGenPtpFK) LEFT JOIN sbqryKeyCharTaxCredits ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharTaxCredits.PropertyFK) LEFT JOIN sbqryKeyCharUnitSumm ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharUnitSumm.Property_ID) LEFT JOIN ctqryKeyCharPermHardSoft ON dbo_pselPropertyNarrow.property_id = ctqryKeyCharPermHardSoft.PropertyFK) ON tblKeyChar.PropertyID = dbo_pselPropertyNarrow.property_id) LEFT JOIN sbqrymtKeyCharCap ON dbo_pselPropertyNarrow.property_id = sbqrymtKeyCharCap.Property_ID) INNER JOIN dbo_uselPortfoliosLPDO ON tblKeyChar.PropertyID = dbo_uselPortfoliosLPDO.PropertyFK) INNER JOIN dbo_uselUpperTiers ON dbo_uselPortfoliosLPDO.PortfolioFK = dbo_uselUpperTiers.partner_id) INNER JOIN tblKeyCharScenarioDetails ON (dbo_pselPropertyNarrow.property_id = tblKeyCharScenarioDetails.property_id) AND (dbo_uselUpperTiers.partner_id = tblKeyCharScenarioDetails.partner_id) SET tblKeyCharScenarioDetails.LegalName = [dbo_uselUpperTiers].[LegalName], tblKeyCharScenarioDetails.lt_name = [dbo_pselPropertyNarrow].[lt_name], tblKeyCharScenarioDetails.City_State = ([dbo_pselPropertyNarrow].[Property_city]) & ", " & Trim([dbo_pselPropertyNarrow].[state_code]), tblKeyCharScenarioDetails.state_code = [dbo_pselPropertyNarrow].[state_code], tblKeyCharScenarioDetails.county = [dbo_pselPropertyNarrow].[county], tblKeyCharScenarioDetails.GPProfit = IIf([dbo_TransDevTeamGPs].[NonProfit]=True,"Non-Profit","Profit"), tblKeyCharScenarioDetails.GPNewRepeat = IIf([dbo_pselPropertyNarrow].[New_GP]=-1,"New","Repeat"), tblKeyCharScenarioDetails.Property_Type = [dbo_pselPropertyNarrow].[Property_Type], tblKeyCharScenarioDetails.Tenant_Mix = [dbo_pselPropertyNarrow].[Tenant_Mix], tblKeyCharScenarioDetails.units = [dbo_pselPropertyNarrow].[units], tblKeyCharScenarioDetails.MktUnits = [dbo_pselPropertyNarrow].[MktUnits], tblKeyCharScenarioDetails.TotalDevCost = [dbo_TransSourceUse].[TotalDevCost], tblKeyCharScenarioDetails.TtlDevCostUnit = CCur(([dbo_TransSourceUse].[TotalDevCost]/[dbo_pselPropertyNarrow].[units])), tblKeyCharScenarioDetails.DealStat = IIf([dbo_pselPropertyNarrow].[Deal_Status]="admitted","Admitted",IIf([dbo_pselPropertyNarrow].[Deal_Status]="> 75% likely to sign","LOI Pending",IIf([dbo_pselPropertyNarrow].[Deal_Status]="< 75% likely to sign","LOI Pending",IIf([dbo_pselPropertyNarrow].[Deal_Status]="LOI signed > 60 days to close","LOI",IIf([dbo_pselPropertyNarrow].[Deal_Status]="LOI signed < 60 days to close","LOI",IIf([dbo_pselPropertyNarrow].[Deal_Status]="Short Form Admitted","LOI",0)))))), tblKeyCharScenarioDetails.Hard = [ctqryKeyCharPermHardSoft].[Hard], tblKeyCharScenarioDetails.Soft = [ctqryKeyCharPermHardSoft].[Soft], tblKeyCharScenarioDetails.WAIntRate = [sbqryKeyCharPermDebtsHard].[WAIntRate], tblKeyCharScenarioDetails.AvgBedrooms = [sbqryKeyCharUnitSumm].[AvgBedrooms], tblKeyCharScenarioDetails.AvgSqFt = [sbqryKeyCharUnitSumm].[AvgSqFt], tblKeyCharScenarioDetails.RentPerSqFt = [sbqryKeyCharUnitSumm].[RentPerSqFt], tblKeyCharScenarioDetails.MktRentAdvantage = [sbqryKeyCharUnitSumm].[MktRentAdvantage], tblKeyCharScenarioDetails.RepResPerUnit = [dbo_wselFiscPerfMaster].[Annual_Deposite_Actual]/[dbo_pselPropertyNarrow].[units], tblKeyCharScenarioDetails.DCR = [tblKeyChar].[StabDCR], tblKeyCharScenarioDetails.UTE = [tblKeyChar].[UTCapital], tblKeyCharScenarioDetails.LTE = IIf([tblKeyChar].[LTCapital]=0,[sbqrymtKeyCharCap].[TotAmt],[tblKeyChar].[LTCapital]), tblKeyCharScenarioDetails.OrigCapPNC = [sbqrymtKeyCharCap].[OrigCapPNC], tblKeyCharScenarioDetails.TCType = [tblKeyChar].[CreditType], tblKeyCharScenarioDetails.PricePerCredit = [sbqryKeyCharTaxCredits].[PricePerCredit], tblKeyCharScenarioDetails.RentalAsst = "N"
WHERE (((dbo_pselPropertyNarrow.property_id)=[Forms]![frmMain]![sbfrmKeyCharacteristicsSecnarios].[Form]![sbfrmKeyChars2].[Form]![lstSelectedForScenario]));

FMS OUTPUT:

Code:

  Dim strSQL As String
  strSQL = "UPDATE ((((tblKeyChar INNER JOIN ((((((("
  strSQL = strSQL & "(dbo_pselPropertyNarrow LEFT JOIN "
  strSQL = strSQL & "dbo_wselFiscPerfMaster ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = dbo_wselFiscPerfMaster.property_id) "
  strSQL = strSQL & "LEFT JOIN dbo_TransSourceUse ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "dbo_TransSourceUse.PropertyFK) LEFT JOIN "
  strSQL = strSQL & "sbqryKeyCharPermDebtsHard ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqryKeyCharPermDebtsHard.PropertyFK) LEFT JOIN "
  strSQL = strSQL & "dbo_DSSExternalDevTeam ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = dbo_DSSExternalDevTeam.PropertyFK) "
  strSQL = strSQL & "LEFT JOIN dbo_TransDevTeamGPs ON "
  strSQL = strSQL & "dbo_DSSExternalDevTeam.GPOrgFK = "
  strSQL = strSQL & "dbo_TransDevTeamGPs.DTGenPtpFK) LEFT JOIN "
  strSQL = strSQL & "sbqryKeyCharTaxCredits ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = sbqryKeyCharTaxCredits.PropertyFK) "
  strSQL = strSQL & "LEFT JOIN sbqryKeyCharUnitSumm ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.Property_ID) LEFT JOIN "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft.PropertyFK) ON "
  strSQL = strSQL & "tblKeyChar.PropertyID = dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id) LEFT JOIN sbqrymtKeyCharCap ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqrymtKeyCharCap.Property_ID) INNER JOIN "
  strSQL = strSQL & "dbo_uselPortfoliosLPDO ON tblKeyChar.PropertyID = "
  strSQL = strSQL & "dbo_uselPortfoliosLPDO.PropertyFK) INNER JOIN "
  strSQL = strSQL & "dbo_uselUpperTiers ON dbo_uselPortfoliosLPDO."
  strSQL = strSQL & "PortfolioFK = dbo_uselUpperTiers.partner_id) "
  strSQL = strSQL & "INNER JOIN tblKeyCharScenarioDetails ON "
  strSQL = strSQL & "(dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "tblKeyCharScenarioDetails.property_id) AND "
  strSQL = strSQL & "(dbo_uselUpperTiers.partner_id = "
  strSQL = strSQL & "tblKeyCharScenarioDetails.partner_id) SET "
  strSQL = strSQL & "tblKeyCharScenarioDetails.LegalName = "
  strSQL = strSQL & "[dbo_uselUpperTiers].[LegalName], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.lt_name = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[lt_name], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.City_State = ("
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[Property_city]) & ', ' &"
  strSQL = strSQL & " Trim([dbo_pselPropertyNarrow].[state_code]), "
  strSQL = strSQL & "tblKeyCharScenarioDetails.state_code = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[state_code], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.county = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[county], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.GPProfit = IIf("
  strSQL = strSQL & "[dbo_TransDevTeamGPs].[NonProfit]=True,'Non-"
  strSQL = strSQL & "Profit','Profit'), tblKeyCharScenarioDetails."
  strSQL = strSQL & "GPNewRepeat = IIf([dbo_pselPropertyNarrow]."
  strSQL = strSQL & "[New_GP]=-1,'New','Repeat'), "
  strSQL = strSQL & "tblKeyCharScenarioDetails.Property_Type = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[Property_Type], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.Tenant_Mix = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[Tenant_Mix], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.units = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[units], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.MktUnits = "
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[MktUnits], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.TotalDevCost = "
  strSQL = strSQL & "[dbo_TransSourceUse].[TotalDevCost], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.TtlDevCostUnit = CCur(("
  strSQL = strSQL & "[dbo_TransSourceUse].[TotalDevCost]/"
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[units])), "
  strSQL = strSQL & "tblKeyCharScenarioDetails.DealStat = IIf("
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[Deal_Status]='admitted',"
  strSQL = strSQL & "'Admitted',IIf([dbo_pselPropertyNarrow]."
  strSQL = strSQL & "[Deal_Status]='> 75% likely to sign','LOI "
  strSQL = strSQL & "Pending',IIf([dbo_pselPropertyNarrow]."
  strSQL = strSQL & "[Deal_Status]='< 75% likely to sign','LOI "
  strSQL = strSQL & "Pending',IIf([dbo_pselPropertyNarrow]."
  strSQL = strSQL & "[Deal_Status]='LOI signed > 60 days to close',"
  strSQL = strSQL & "'LOI',IIf([dbo_pselPropertyNarrow].[Deal_Status]="
  strSQL = strSQL & "'LOI signed < 60 days to close','LOI',IIf("
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[Deal_Status]='Short "
  strSQL = strSQL & "Form Admitted','LOI',0)))))), "
  strSQL = strSQL & "tblKeyCharScenarioDetails.Hard = "
  strSQL = strSQL & "[ctqryKeyCharPermHardSoft].[Hard], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.Soft = "
  strSQL = strSQL & "[ctqryKeyCharPermHardSoft].[Soft], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.WAIntRate = "
  strSQL = strSQL & "[sbqryKeyCharPermDebtsHard].[WAIntRate], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.AvgBedrooms = "
  strSQL = strSQL & "[sbqryKeyCharUnitSumm].[AvgBedrooms], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.AvgSqFt = "
  strSQL = strSQL & "[sbqryKeyCharUnitSumm].[AvgSqFt], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.RentPerSqFt = "
  strSQL = strSQL & "[sbqryKeyCharUnitSumm].[RentPerSqFt], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.MktRentAdvantage = "
  strSQL = strSQL & "[sbqryKeyCharUnitSumm].[MktRentAdvantage], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.RepResPerUnit = "
  strSQL = strSQL & "[dbo_wselFiscPerfMaster].[Annual_Deposite_Actual]/"
  strSQL = strSQL & "[dbo_pselPropertyNarrow].[units], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.DCR = [tblKeyChar]."
  strSQL = strSQL & "[StabDCR], tblKeyCharScenarioDetails.UTE = "
  strSQL = strSQL & "[tblKeyChar].[UTCapital], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.LTE = IIf([tblKeyChar]."
  strSQL = strSQL & "[LTCapital]=0,[sbqrymtKeyCharCap].[TotAmt],"
  strSQL = strSQL & "[tblKeyChar].[LTCapital]), "
  strSQL = strSQL & "tblKeyCharScenarioDetails.OrigCapPNC = "
  strSQL = strSQL & "[sbqrymtKeyCharCap].[OrigCapPNC], "
  strSQL = strSQL & "tblKeyCharScenarioDetails.TCType = [tblKeyChar]."
  strSQL = strSQL & "[CreditType], tblKeyCharScenarioDetails."
  strSQL = strSQL & "PricePerCredit = [sbqryKeyCharTaxCredits]."
  strSQL = strSQL & "[PricePerCredit], tblKeyCharScenarioDetails."
  strSQL = strSQL & "RentalAsst = 'N' "
  strSQL = strSQL & "WHERE (((dbo_pselPropertyNarrow.property_id)="
  strSQL = strSQL & "[Forms]![frmMain]!"
  strSQL = strSQL & "[sbfrmKeyCharacteristicsSecnarios].[Form]!"
  strSQL = strSQL & "[sbfrmKeyChars2].[Form]![lstSelectedForScenario]))"
  strSQL = strSQL & ";"

APPEND QUERY:

Code:

INSERT INTO tblKeyCharScenarioDetails ( partner_id, property_id, LegalName, lt_name, City_State, state_code, county, GPProfit, GPNewRepeat, Property_Type, Tenant_Mix, units, MktUnits, TotalDevCost, TtlDevCostUnit, DealStat, Hard, Soft, WAIntRate, AvgBedrooms, AvgSqFt, RentPerSqFt, MktRentAdvantage, RepResPerUnit, DCR, UTE, LTE, OrigCapPNC, TCType, PricePerCredit, RentalAsst, ScenarioID )
SELECT dbo_uselUpperTiers.partner_id, dbo_pselPropertyNarrow.property_id, dbo_uselUpperTiers.LegalName, dbo_pselPropertyNarrow.lt_name, ([dbo_pselPropertyNarrow]![Property_city]) & ", " & Trim([dbo_pselPropertyNarrow]![state_code]) AS City_State, dbo_pselPropertyNarrow.state_code, dbo_pselPropertyNarrow.county, IIf([NonProfit]=True,"Non-Profit","Profit") AS GPProfit, IIf([dbo_pselPropertyNarrow]![New_GP]=-1,"New","Repeat") AS GPNewRepeat, dbo_pselPropertyNarrow.Property_Type, dbo_pselPropertyNarrow.Tenant_Mix, dbo_pselPropertyNarrow.units, dbo_pselPropertyNarrow.MktUnits, dbo_TransSourceUse.TotalDevCost, CCur(([TotalDevCost]/[units])) AS TtlDevCostUnit, IIf([Deal_Status]="admitted","Admitted",IIf([Deal_Status]="> 75% likely to sign","LOI Pending",IIf([Deal_Status]="< 75% likely to sign","LOI Pending",IIf([Deal_Status]="LOI signed > 60 days to close","LOI",IIf([Deal_Status]="LOI signed < 60 days to close","LOI",IIf([Deal_Status]="Short Form Admitted","LOI",0)))))) AS DealStat, ctqryKeyCharPermHardSoft.Hard, ctqryKeyCharPermHardSoft.Soft, sbqryKeyCharPermDebtsHard.WAIntRate, sbqryKeyCharUnitSumm.AvgBedrooms, sbqryKeyCharUnitSumm.AvgSqFt, sbqryKeyCharUnitSumm.RentPerSqFt, sbqryKeyCharUnitSumm.MktRentAdvantage, [Annual_Deposite_Actual]/[units] AS RepResPerUnit, tblKeyChar.StabDCR AS DCR, tblKeyChar.UTCapital AS UTE, IIf([tblKeyChar].[LTCapital]=0,[sbqrymtKeyCharCap].[TotAmt],[tblKeyChar].[LTCapital]) AS LTE, sbqrymtKeyCharCap.OrigCapPNC, tblKeyChar.CreditType AS TCType, sbqryKeyCharTaxCredits.PricePerCredit, "N" AS RentalAsst, [Forms]![frmMain]![sbfrmKeyCharacteristicsSecnarios].[Form]![txtScenarioID] AS Expr1
FROM (((tblKeyChar INNER JOIN ((((((((dbo_pselPropertyNarrow LEFT JOIN dbo_wselFiscPerfMaster ON dbo_pselPropertyNarrow.property_id = dbo_wselFiscPerfMaster.property_id) LEFT JOIN dbo_TransSourceUse ON dbo_pselPropertyNarrow.property_id = dbo_TransSourceUse.PropertyFK) LEFT JOIN sbqryKeyCharPermDebtsHard ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharPermDebtsHard.PropertyFK) LEFT JOIN dbo_DSSExternalDevTeam ON dbo_pselPropertyNarrow.property_id = dbo_DSSExternalDevTeam.PropertyFK) LEFT JOIN dbo_TransDevTeamGPs ON dbo_DSSExternalDevTeam.GPOrgFK = dbo_TransDevTeamGPs.DTGenPtpFK) LEFT JOIN sbqryKeyCharTaxCredits ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharTaxCredits.PropertyFK) LEFT JOIN sbqryKeyCharUnitSumm ON dbo_pselPropertyNarrow.property_id = sbqryKeyCharUnitSumm.Property_ID) LEFT JOIN ctqryKeyCharPermHardSoft ON dbo_pselPropertyNarrow.property_id = ctqryKeyCharPermHardSoft.PropertyFK) ON tblKeyChar.PropertyID = dbo_pselPropertyNarrow.property_id) LEFT JOIN sbqrymtKeyCharCap ON dbo_pselPropertyNarrow.property_id = sbqrymtKeyCharCap.Property_ID) INNER JOIN dbo_uselPortfoliosLPDO ON tblKeyChar.PropertyID = dbo_uselPortfoliosLPDO.PropertyFK) INNER JOIN dbo_uselUpperTiers ON dbo_uselPortfoliosLPDO.PortfolioFK = dbo_uselUpperTiers.partner_id
WHERE (((dbo_uselUpperTiers.partner_id)=[Forms]![frmMain]![cboUpperTierID]) AND ((dbo_pselPropertyNarrow.property_id)=[Forms]![frmMain]![lstPropPorts]))
ORDER BY dbo_uselUpperTiers.partner_id DESC;

FMS OUTPUT:

Code:

  Dim strSQL As String
  strSQL = "INSERT INTO tblKeyCharScenarioDetails ( "
  strSQL = strSQL & "partner_id, property_id, LegalName, lt_name, "
  strSQL = strSQL & "City_State, state_code, county, GPProfit, "
  strSQL = strSQL & "GPNewRepeat, Property_Type, Tenant_Mix, units, "
  strSQL = strSQL & "MktUnits, TotalDevCost, TtlDevCostUnit, DealStat, "
  strSQL = strSQL & "Hard, Soft, WAIntRate, AvgBedrooms, AvgSqFt, "
  strSQL = strSQL & "RentPerSqFt, MktRentAdvantage, RepResPerUnit, DCR,"
  strSQL = strSQL & " UTE, LTE, OrigCapPNC, TCType, PricePerCredit, "
  strSQL = strSQL & "RentalAsst, ScenarioID ) "
  strSQL = strSQL & "SELECT dbo_uselUpperTiers.partner_id, "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id, "
  strSQL = strSQL & "dbo_uselUpperTiers.LegalName, "
  strSQL = strSQL & "dbo_pselPropertyNarrow.lt_name, ("
  strSQL = strSQL & "[dbo_pselPropertyNarrow]![Property_city]) & ', ' &"
  strSQL = strSQL & " Trim([dbo_pselPropertyNarrow]![state_code]) AS "
  strSQL = strSQL & "City_State, dbo_pselPropertyNarrow.state_code, "
  strSQL = strSQL & "dbo_pselPropertyNarrow.county, IIf([NonProfit]="
  strSQL = strSQL & "True,'Non-Profit','Profit') AS GPProfit, IIf("
  strSQL = strSQL & "[dbo_pselPropertyNarrow]![New_GP]=-1,'New',"
  strSQL = strSQL & "'Repeat') AS GPNewRepeat, dbo_pselPropertyNarrow."
  strSQL = strSQL & "Property_Type, dbo_pselPropertyNarrow.Tenant_Mix, "
  strSQL = strSQL & "dbo_pselPropertyNarrow.units, "
  strSQL = strSQL & "dbo_pselPropertyNarrow.MktUnits, "
  strSQL = strSQL & "dbo_TransSourceUse.TotalDevCost, CCur(("
  strSQL = strSQL & "[TotalDevCost]/[units])) AS TtlDevCostUnit, IIf("
  strSQL = strSQL & "[Deal_Status]='admitted','Admitted',IIf("
  strSQL = strSQL & "[Deal_Status]='> 75% likely to sign','LOI "
  strSQL = strSQL & "Pending',IIf([Deal_Status]='< 75% likely to sign',"
  strSQL = strSQL & "'LOI Pending',IIf([Deal_Status]='LOI signed > 60 "
  strSQL = strSQL & "days to close','LOI',IIf([Deal_Status]='LOI "
  strSQL = strSQL & "signed < 60 days to close','LOI',IIf([Deal_Status]"
  strSQL = strSQL & "='Short Form Admitted','LOI',0)))))) AS DealStat, "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft.Hard, "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft.Soft, "
  strSQL = strSQL & "sbqryKeyCharPermDebtsHard.WAIntRate, "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.AvgBedrooms, "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.AvgSqFt, "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.RentPerSqFt, "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.MktRentAdvantage, "
  strSQL = strSQL & "[Annual_Deposite_Actual]/[units] AS RepResPerUnit,"
  strSQL = strSQL & " tblKeyChar.StabDCR AS DCR, tblKeyChar.UTCapital "
  strSQL = strSQL & "AS UTE, IIf([tblKeyChar].[LTCapital]=0,"
  strSQL = strSQL & "[sbqrymtKeyCharCap].[TotAmt],[tblKeyChar]."
  strSQL = strSQL & "[LTCapital]) AS LTE, sbqrymtKeyCharCap.OrigCapPNC,"
  strSQL = strSQL & " tblKeyChar.CreditType AS TCType, "
  strSQL = strSQL & "sbqryKeyCharTaxCredits.PricePerCredit, 'N' AS "
  strSQL = strSQL & "RentalAsst, [Forms]![frmMain]!"
  strSQL = strSQL & "[sbfrmKeyCharacteristicsSecnarios].[Form]!"
  strSQL = strSQL & "[txtScenarioID] AS Expr1 "
  strSQL = strSQL & "FROM (((tblKeyChar INNER JOIN ((((((("
  strSQL = strSQL & "(dbo_pselPropertyNarrow LEFT JOIN "
  strSQL = strSQL & "dbo_wselFiscPerfMaster ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = dbo_wselFiscPerfMaster.property_id) "
  strSQL = strSQL & "LEFT JOIN dbo_TransSourceUse ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "dbo_TransSourceUse.PropertyFK) LEFT JOIN "
  strSQL = strSQL & "sbqryKeyCharPermDebtsHard ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqryKeyCharPermDebtsHard.PropertyFK) LEFT JOIN "
  strSQL = strSQL & "dbo_DSSExternalDevTeam ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = dbo_DSSExternalDevTeam.PropertyFK) "
  strSQL = strSQL & "LEFT JOIN dbo_TransDevTeamGPs ON "
  strSQL = strSQL & "dbo_DSSExternalDevTeam.GPOrgFK = "
  strSQL = strSQL & "dbo_TransDevTeamGPs.DTGenPtpFK) LEFT JOIN "
  strSQL = strSQL & "sbqryKeyCharTaxCredits ON dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id = sbqryKeyCharTaxCredits.PropertyFK) "
  strSQL = strSQL & "LEFT JOIN sbqryKeyCharUnitSumm ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqryKeyCharUnitSumm.Property_ID) LEFT JOIN "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "ctqryKeyCharPermHardSoft.PropertyFK) ON "
  strSQL = strSQL & "tblKeyChar.PropertyID = dbo_pselPropertyNarrow."
  strSQL = strSQL & "property_id) LEFT JOIN sbqrymtKeyCharCap ON "
  strSQL = strSQL & "dbo_pselPropertyNarrow.property_id = "
  strSQL = strSQL & "sbqrymtKeyCharCap.Property_ID) INNER JOIN "
  strSQL = strSQL & "dbo_uselPortfoliosLPDO ON tblKeyChar.PropertyID = "
  strSQL = strSQL & "dbo_uselPortfoliosLPDO.PropertyFK) INNER JOIN "
  strSQL = strSQL & "dbo_uselUpperTiers ON dbo_uselPortfoliosLPDO."
  strSQL = strSQL & "PortfolioFK = dbo_uselUpperTiers.partner_id "
  strSQL = strSQL & "WHERE (((dbo_uselUpperTiers.partner_id)=[Forms]!"
  strSQL = strSQL & "[frmMain]![cboUpperTierID]) AND ("
  strSQL = strSQL & "(dbo_pselPropertyNarrow.property_id)=[Forms]!"
  strSQL = strSQL & "[frmMain]![lstPropPorts])) "
  strSQL = strSQL & "ORDER BY dbo_uselUpperTiers.partner_id DESC;"

vbaInet · Feb 1, 2011

It is quite a brutal query Bob

There's not a lot you can do to make the source query updateable if you have lots of joins.

Here are some suggestions I gave to CBrighton today re a similar situation:

http://www.access-programmers.co.uk/forums/showthread.php?t=204585

gemma-the-husky · Feb 2, 2011

bob

When I get an non-updateable one, I end up going back to dao.

Code:

init progress bar
while not rst.eof
   update progress bar
   strg = "update sometable etc"
   currentdb.exexcute s
   rst.movenext
wend
remove progress bar

one nice thing about this, especially if its a long process, is that you can pre-count the changes, and have an on-screen progress bar.

boblarson · Feb 4, 2011

Just a quick update -

I wound up using DAO to update and it works great. It took me a long time because I kept getting on of the Parameter Expected (1) error and I finally realized that I had some misspellings going on. Once I got those corrected it works like a charm. I am finally able to work on the reports tomorrow and hopefully those won't take too long.

Today was a good day, I got a lot of it working like I wanted. I am ready to go home though. My brain feels like mush.

Thanks vbaInet and Dave for helping out.

vbaInet · Feb 4, 2011

I've found the progress to make things slow down a bit. I only use them if the process isn't so much time dependent.

Bob, don't you think you think you will save "some time" if you used this structure:

Code:

strSQL = "INSERT INTO tblKeyCharScenarioDetails ( " & _
             "partner_id, property_id, LegalName, lt_name, " & _
             "City_State, state_code, county, GPProfit, " & _
             "GPNewRepeat, Property_Type, Tenant_Mix, units, " & _
             "MktUnits, TotalDevCost, TtlDevCostUnit, DealStat, " & _
.
.
.

... etc ...

Or you prefer your current method?

boblarson · Feb 4, 2011

It is working well now and the number of records we're dealing with here is very small (less than 100 at any one time), I think I can live with the current code I have. But isn't the INSERT actually an append query? And that wouldn't update the current records but put them in again and I would need to either update the existing or flush them and re-append. The append is working fine it was the update that couldn't update because of the formulas which form a lot of the fields. (I didn't come up with this stuff it was here long before me so I have limited ability to change certain things, unfortunately).

vbaInet · Feb 4, 2011

Yeah it is an Append Query. I wonder why they didn't just call it Insert Query

What I meant is that the string is reassigned over 50 times, so instead of that a one line building of the string would have been sufficient. Either way works but the second is more effecient.

I bet you're glad this bit is out of the way

boblarson · Feb 4, 2011

vbaInet said:
What I meant is that the string is reassigned over 50 times, so instead of that a one line building of the string would have been sufficient. Either way works but the second is more effecient.

Except that, believe it or not, I hit the "too many line extensions" number.

Need Updatable Query - This is wicked so beware... (1 Viewer)

boblarson

Smeghead

vbaInet

AWF VIP

gemma-the-husky

Super Moderator

boblarson

Smeghead

vbaInet

AWF VIP

boblarson

Smeghead

vbaInet

AWF VIP

boblarson

Smeghead

Similar threads

Users who are viewing this thread