Once I input command it will not let me switch views, so here's the SQL
prior
to putting in the syntax (it's very long, I wanted to make sure you had
everything). Suffice to say, there's only one field that needs to be
appended: AGENCY_PROFILE_ID
Is it just a matter of me manually placing the syntax from the SQL
view?
I'm novice with this, any help you can provide is more than
appreciated.
Thanks.
INSERT INTO [PROFILE-TEST] ( AGENCY_PROFILE_ID, AGENCY_NAME, [LMP
Office],
Regional_Office_Name, City, State, ZIP_CODE, [Reporting_Year_(TAV)],
Personal_TAV, [Small Comm_TAV], [Middle Market_TAV], Marine_TAV,
RMD_TAV,
HFP_TAV, [Bond/Fidelity/Surety_TAV], All_Other_Comm_TAV,
All_Other_Specialty_TAV, [Whsle/Reins_TAV], Commercial_Specialty_TAV,
[Total
P&C_TAV], International_TAV, [Life/Benefits_TAV], Total_TAV,
[Carrier_1_(Name)], [Carrier_1_(Persl_Premium)],
[Carrier_1_(Comml_Premium)],
[Carrier_1_(Total_Premium)], [Carrier_2_(Name)],
[Carrier_2_(Persl_Premium)],
[Carrier_2_(Comml_Premium)], [Carrier_2_(Total_Premium)],
[Carrier_3_(Name)],
[Carrier_3_(Persl_Premium)], [Carrier_3_(Comml_Premium)],
[Carrier_3_(Total_Premium)], [Carrier_4_(Name)],
[Carrier_4_(Persl_Premium)],
[Carrier_4_(Comml_Premium)], [Carrier_4_(Total_Premium)],
[Carrier_5_(Name)],
[Carrier_5_(Persl_Premium)], [Carrier_5_(Comml_Premium)],
[Carrier_5_(Total
Premium)], [Carrier_6_(Name)], [Carrier_6_(Persl_Premium)],
[Carrier_6_(Comml_Premium)], [Carrier_6_(Total_Premium)],
[Carrier_7_(Name)],
[Carrier_7_(Persl_Premium)], [Carrier_7_(Comml_Premium)],
[Carrier_7_(Total_Premium)], [Carrier_8_(Name)],
[Carrier_8_(Persl_Premium)],
[Carrier 8 (Comml_Premium)], [Carrier 8 (Total_Premium)],
[Carrier_9_(Name)],
[Carrier_9_(Persl_Premium)], [Carrier_9_(Comml_Premium)],
[Carrier_9_(Total
Premium)], [Carrier_10_(Name)], [Carrier_10_(Persl_Premium)],
[Carrier_10_(Comml_Premium)], [Carrier_10_(Total_Premium)], Generalist,
Apartments, [Comm_&_Media], Contractors, Food_Processors,
Industrial_Processing, Law_Firms, Manufacturing, Mercantile,
[Metal/Machine_Shop], Office, Personal, Plastic_Fabricators, Printers,
Private_Education, Restaurants, Technology, Wholesalers, Other,
Small_Commercial, Marine, [D&O], [E&O], Professional_Liability,
[Bond/Fidelity/Surety], STREET_ADDRESS_1, STREET_ADDRESS_2,
ZIP_PLUS_FOUR,
AGENCY_STATE, WEBSITE, RESPONDENT_FIRSTNAME, RESPONDENT_LASTNAME,
POSITION_IN_AGENCY, OTHER_POSITION_COMMENT_TXT,
RESPONDENT_PHONE_NUMBER,
RESPONDENT_PHONE_EXT, RESPONDENT_EMAIL_ADDR, DATE_COMPLETED, SCSR,
NMBR_HIG_LICENSED_PRODUCERS, AGENCY_APPOINTED_YEAR, CLASSIFICATION,
[2005_P&C_TAV], 2005_CL_TAV, 2005_PL_TAV, 2005_SC_TAV, [2004_P&C_TAV],
2004_CL_TAV, 2004_PL_TAV, 2004_SC_TAV, AGCY_DEF_SMALL_COMMERCIAL,
NEW_SMALL_COMRCL_ANNL_GEN, [NB Carrier_#1],
[Carrier_#1_pct_sm_comcl_bus],
[Carrier_#1_yrs_apptd_with_carrier], [NB Carrier_#2],
[Carrier_#2_pct_sm_comcl_bus], [Carrier_#2_yrs_apptd_with_carrier], [NB
Carrier_#3], [Carrier_#3_pct_sm_comcl_bus],
[Carrier_#3_yrs_apptd_with_carrier], CARRIER_DIFFERENCE_COMMENT,
SMALL_COMRCL_BUS_STRUCT, NMBR_DEDICATED_CSRS, NMBR_DEDICATED_PRODUCERS,
NMBR_DEDICATED_MGRS, NMBR_DEDICATED_PRINCIPALS, NMBR_DEDICATED_OTHER,
DEDICATED_OTHER_COMMENT, PRODUCER_SMALL_COMRCL_COMM_IND,
PRODUCER_COMMISSION_THRESH_AMT, PRODUCER_NEW_PREM_TARGET_AMT,
AGENCY_ACQUIRED_ANOTHER_IND, AGENCY_BEEN_ACQUIRED_IND,
AGENCY_MERGED_IND,
COMMERCIAL_BBT_IND, PERSONAL_BBT_IND, [CL_Carrier_#1],
[CL_Carrier_#1_pct_SC_TAV], [CL_Carrier_#1_Carrier_PHS],
[CL_Carrier_#2],
[CL_Carrier_#2 pct_SC_TAV], [CL_Carrier_#2_Carrier_PHS],
[CL_Carrier_#3],
[CL_Carrier_#3_pct_SC_TAV], [CL_Carrier_#3_Carrier_PHS],
[CL_Carrier_#4],
[CL_Carrier_#4_pct_SC_TAV], [CL_Carrier_#4_Carrier_PHS],
[CL_Carrier_#5],
[CL_Carrier_#5_pct_SC_TAV], [CL_Carrier_#5_Carrier_PHS],
Business_Professional_Services, Construction, [Finance/Real_Estate],
Printing, Retail, ADVERTISING, [AFFILIATIONS/ASSOCIATIONS], [BANK
RELATIONSHIP/OWNERSHIP], COLD_CALLS, CROSS_SELL,
CROSS_SELL_TO_EMPLOYEE_BENEFITS, DIRECT_MAIL, INTERNET_APPLICATION,
LEAD_GENERATION, [LEADS_ FROM_INSURANCE_COMPANIES], SC_Z_Final2_Other,
REFERRALS_FROM_EXISTING_CUSTOMERS, [REFERRALS_FROM_OTHER_BUS_
PROFESSIONALS],
TELEMARKETING, YELLOW_PAGES, AGENCY_MKTG_COMMENTS, GROWTH_PCT_RANGE,
[SC_Xpand_New_Accts_Sold_Per_Month_Query_Accounts under 25k],
[SC_Xpand_New_Accts_Sold Per_Month_Accounts_25k_to_75k],
SC_Xpand_New_Accts_Sold_Per_Month_Accounts_greater_than_75k,
[SC_Xpand_New_Accts_in_Pipeline _Accounts_under_25k],
[SC_Xpand_New_Accts_in
_Pipeline_Accounts_25k_to_75k],
SC_Xpand_New_Accts_In_Pipeline_Accounts_greater_than_75k,
APP_SUBMISSION_METHOD, [Go_To_Carrier_#1], [Go_To_Carrier_#1_Reason],
[Go_To_Carrier_#2], [Go_To_Carrier_#2_Reason], AVG_NBR_ACCOUNTS_QUOTED,
FINAL_PLACEMENT_DECISION_MAKER, AGCY_DECISION_MAKER_ROLE,
PLACEMENT_COMMENT_TEXT, AVG_NBR_RENEWALS, E_AND_S_VOLUME,
E_AND_S_WHOLESALER_IND, [PL_Carrier_#1], [PL_Carrier_#1_Comment],
[PL_Carrier_#2], [PL_Carrier_#2_Comment], [PL_Carrier_#3],
[PL_Carrier_#3_Comment], [PL_Carrier_#4], [PL_Carrier_#4_Comment],
[PL_Carrier_#5], [PL_Carrier_#5_Comment], POLICY_SALES_RANGE,
LEAD_TYPE,
CURRENT_GROWTH, Account_Executive,
[Customer_Service_Representative_(CSR)],
[Loss_Control/Appraiser], Manager, Marketing_Manager,
[Other_(Comment_Required)], Owner, President, Principal, Producer,
Underwriter, PRINCIPLE_PROVIDES_QUOTES_IND, BUSINESS_STRUCTURE,
Carrier_proprietary_system, Comparative_rating_vendor,
SC_Z_Final4_Other )
SELECT PROFILE.AGENCY_PROFILE_ID, PROFILE.AGENCY_NAME, PROFILE.[LMP
Office],
PROFILE.Regional_Office_Name, PROFILE.City, PROFILE.State,
PROFILE.ZIP_CODE,
PROFILE.[Reporting_Year_(TAV)], PROFILE.Personal_TAV, PROFILE.[Small
Comm_TAV], PROFILE.[Middle Market_TAV], PROFILE.Marine_TAV,
PROFILE.RMD_TAV,
PROFILE.HFP_TAV, PROFILE.[Bond/Fidelity/Surety_TAV],
PROFILE.All_Other_Comm_TAV, PROFILE.All_Other_Specialty_TAV,
PROFILE.[Whsle/Reins_TAV], PROFILE.Commercial_Specialty_TAV,
PROFILE.[Total
P&C_TAV], PROFILE.International_TAV, PROFILE.[Life/Benefits_TAV],
PROFILE.Total_TAV, PROFILE.[Carrier_1_(Name)],
PROFILE.[Carrier_1_(Persl_Premium)],
PROFILE.[Carrier_1_(Comml_Premium)],
PROFILE.[Carrier_1_(Total_Premium)], PROFILE.[Carrier_2_(Name)],
PROFILE.[Carrier_2_(Persl_Premium)],
PROFILE.[Carrier_2_(Comml_Premium)],
PROFILE.[Carrier_2_(Total_Premium)], PROFILE.[Carrier_3_(Name)],
PROFILE.[Carrier_3_(Persl_Premium)],
PROFILE.[Carrier_3_(Comml_Premium)],
PROFILE.[Carrier_3_(Total_Premium)], PROFILE.[Carrier_4_(Name)],
PROFILE.[Carrier_4_(Persl_Premium)],
PROFILE.[Carrier_4_(Comml_Premium)],
PROFILE.[Carrier_4_(Total_Premium)], PROFILE.[Carrier_5_(Name)],
PROFILE.[Carrier_5_(Persl_Premium)],
PROFILE.[Carrier_5_(Comml_Premium)],
PROFILE.[Carrier_5_(Total Premium)], PROFILE.[Carrier_6_(Name)],
PROFILE.[Carrier_6_(Persl_Premium)],
PROFILE.[Carrier_6_(Comml_Premium)],
PROFILE.[Carrier_6_(Total_Premium)], PROFILE.[Carrier_7_(Name)],
PROFILE.[Carrier_7_(Persl_Premium)],
PROFILE.[Carrier_7_(Comml_Premium)],
PROFILE.[Carrier_7_(Total_Premium)], PROFILE.[Carrier_8_(Name)],
PROFILE.[Carrier_8_(Persl_Premium)], PROFILE.[Carrier 8
(Comml_Premium)],
PROFILE.[Carrier 8 (Total_Premium)], PROFILE.[Carrier_9_(Name)],
PROFILE.[Carrier_9_(Persl_Premium)],
PROFILE.[Carrier_9_(Comml_Premium)],
PROFILE.[Carrier_9_(Total Premium)], PROFILE.[Carrier_10_(Name)],
PROFILE.[Carrier_10_(Persl_Premium)],
PROFILE.[Carrier_10_(Comml_Premium)],
PROFILE.[Carrier_10_(Total_Premium)], PROFILE.Generalist,
PROFILE.Apartments,
PROFILE.[Comm_&_Media], PROFILE.Contractors, PROFILE.Food_Processors,
PROFILE.Industrial_Processing, PROFILE.Law_Firms,
PROFILE.Manufacturing,
PROFILE.Mercantile, PROFILE.[Metal/Machine_Shop], PROFILE.Office,
PROFILE.Personal, PROFILE.Plastic_Fabricators, PROFILE.Printers,
PROFILE.Private_Education, PROFILE.Restaurants, PROFILE.Technology,
PROFILE.Wholesalers, PROFILE.Other, PROFILE.Small_Commercial,
PROFILE.Marine,
PROFILE.[D&O], PROFILE.[E&O], PROFILE.Professional_Liability,
PROFILE.[Bond/Fidelity/Surety], PROFILE.STREET_ADDRESS_1,
PROFILE.STREET_ADDRESS_2, PROFILE.ZIP_PLUS_FOUR, PROFILE.AGENCY_STATE,
PROFILE.WEBSITE, PROFILE.RESPONDENT_FIRSTNAME,
PROFILE.RESPONDENT_LASTNAME,
PROFILE.POSITION_IN_AGENCY, PROFILE.OTHER_POSITION_COMMENT_TXT,
PROFILE.RESPONDENT_PHONE_NUMBER, PROFILE.RESPONDENT_PHONE_EXT,
PROFILE.RESPONDENT_EMAIL_ADDR, PROFILE.DATE_COMPLETED, PROFILE.SCSR,
PROFILE.NMBR_HIG_LICENSED_PRODUCERS, PROFILE.AGENCY_APPOINTED_YEAR,
PROFILE.CLASSIFICATION, PROFILE.[2005_P&C_TAV], PROFILE.[2005_CL_TAV],
PROFILE.[2005_PL_TAV], PROFILE.[2005_SC_TAV], PROFILE.[2004_P&C_TAV],
PROFILE.[2004_CL_TAV], PROFILE.[2004_PL_TAV], PROFILE.[2004_SC_TAV],
PROFILE.AGCY_DEF_SMALL_COMMERCIAL, PROFILE.NEW_SMALL_COMRCL_ANNL_GEN,
PROFILE.[NB Carrier_#1], PROFILE.[Carrier_#1_pct_sm_comcl_bus],
PROFILE.[Carrier_#1_yrs_apptd_with_carrier], PROFILE.[NB Carrier_#2],
PROFILE.[Carrier_#2_pct_sm_comcl_bus],
PROFILE.[Carrier_#2_yrs_apptd_with_carrier], PROFILE.[NB Carrier_#3],
PROFILE.[Carrier_#3_pct_sm_comcl_bus],
PROFILE.[Carrier_#3_yrs_apptd_with_carrier],
PROFILE.CARRIER_DIFFERENCE_COMMENT, PROFILE.SMALL_COMRCL_BUS_STRUCT,
PROFILE.NMBR_DEDICATED_CSRS, PROFILE.NMBR_DEDICATED_PRODUCERS,
PROFILE.NMBR_DEDICATED_MGRS, PROFILE.NMBR_DEDICATED_PRINCIPALS,
PROFILE.NMBR_DEDICATED_OTHER, PROFILE.DEDICATED_OTHER_COMMENT,
PROFILE.PRODUCER_SMALL_COMRCL_COMM_IND,
PROFILE.PRODUCER_COMMISSION_THRESH_AMT,
PROFILE.PRODUCER_NEW_PREM_TARGET_AMT,
PROFILE.AGENCY_ACQUIRED_ANOTHER_IND, PROFILE.AGENCY_BEEN_ACQUIRED_IND,
PROFILE.AGENCY_MERGED_IND, PROFILE.COMMERCIAL_BBT_IND,
PROFILE.PERSONAL_BBT_IND, PROFILE.[CL_Carrier_#1],
PROFILE.[CL_Carrier_#1_pct_SC_TAV],
PROFILE.[CL_Carrier_#1_Carrier_PHS],
PROFILE.[CL_Carrier_#2], PROFILE.[CL_Carrier_#2 pct_SC_TAV],
PROFILE.[CL_Carrier_#2_Carrier_PHS], PROFILE.[CL_Carrier_#3],
PROFILE.[CL_Carrier_#3_pct_SC_TAV],
PROFILE.[CL_Carrier_#3_Carrier_PHS],
PROFILE.[CL_Carrier_#4], PROFILE.[CL_Carrier_#4_pct_SC_TAV],
PROFILE.[CL_Carrier_#4_Carrier_PHS], PROFILE.[CL_Carrier_#5],
PROFILE.[CL_Carrier_#5_pct_SC_TAV],
PROFILE.[CL_Carrier_#5_Carrier_PHS],
PROFILE.Business_Professional_Services, PROFILE.Construction,
PROFILE.[Finance/Real_Estate], PROFILE.Printing, PROFILE.Retail,
PROFILE.ADVERTISING, PROFILE.[AFFILIATIONS/ASSOCIATIONS], PROFILE.[BANK
RELATIONSHIP/OWNERSHIP], PROFILE.COLD_CALLS, PROFILE.CROSS_SELL,
PROFILE.CROSS_SELL_TO_EMPLOYEE_BENEFITS, PROFILE.DIRECT_MAIL,
PROFILE.INTERNET_APPLICATION, PROFILE.LEAD_GENERATION, PROFILE.[LEADS_
FROM_INSURANCE_COMPANIES], PROFILE.SC_Z_Final2_Other,
PROFILE.REFERRALS_FROM_EXISTING_CUSTOMERS,
PROFILE.[REFERRALS_FROM_OTHER_BUS_
PROFESSIONALS], PROFILE.TELEMARKETING, PROFILE.YELLOW_PAGES,
PROFILE.AGENCY_MKTG_COMMENTS, PROFILE.GROWTH_PCT_RANGE,
PROFILE.[SC_Xpand_New_Accts_Sold_Per_Month_Query_Accounts under 25k],
PROFILE.[SC_Xpand_New_Accts_Sold Per_Month_Accounts_25k_to_75k],
PROFILE.SC_Xpand_New_Accts_Sold_Per_Month_Accounts_greater_than_75k,
PROFILE.[SC_Xpand_New_Accts_in_Pipeline _Accounts_under_25k],
PROFILE.[SC_Xpand_New_Accts_in _Pipeline_Accounts_25k_to_75k],
PROFILE.SC_Xpand_New_Accts_In_Pipeline_Accounts_greater_than_75k,
PROFILE.APP_SUBMISSION_METHOD, PROFILE.[Go_To_Carrier_#1],
PROFILE.[Go_To_Carrier_#1_Reason], PROFILE.[Go_To_Carrier_#2],
PROFILE.[Go_To_Carrier_#2_Reason], PROFILE.AVG_NBR_ACCOUNTS_QUOTED,
PROFILE.FINAL_PLACEMENT_DECISION_MAKER,
PROFILE.AGCY_DECISION_MAKER_ROLE,
PROFILE.PLACEMENT_COMMENT_TEXT, PROFILE.AVG_NBR_RENEWALS,
PROFILE.E_AND_S_VOLUME, PROFILE.E_AND_S_WHOLESALER_IND,
PROFILE.[PL_Carrier_#1], PROFILE.[PL_Carrier_#1_Comment],
PROFILE.[PL_Carrier_#2], PROFILE.[PL_Carrier_#2_Comment],
PROFILE.[PL_Carrier_#3], PROFILE.[PL_Carrier_#3_Comment],
PROFILE.[PL_Carrier_#4], PROFILE.[PL_Carrier_#4_Comment],
PROFILE.[PL_Carrier_#5], PROFILE.[PL_Carrier_#5_Comment],
PROFILE.POLICY_SALES_RANGE, PROFILE.LEAD_TYPE, PROFILE.CURRENT_GROWTH,
PROFILE.Account_Executive,
PROFILE.[Customer_Service_Representative_(CSR)],
PROFILE.[Loss_Control/Appraiser], PROFILE.Manager,
PROFILE.Marketing_Manager,
PROFILE.[Other_(Comment_Required)], PROFILE.Owner, PROFILE.President,
PROFILE.Principal, PROFILE.Producer, PROFILE.Underwriter,
PROFILE.PRINCIPLE_PROVIDES_QUOTES_IND, PROFILE.BUSINESS_STRUCTURE,
PROFILE.Carrier_proprietary_system, PROFILE.Comparative_rating_vendor,
PROFILE.SC_Z_Final4_Other
FROM PROFILE
WHERE (((PROFILE.AGENCY_PROFILE_ID)="Autonumber"));
:
Hi.
The program seems to give me the error
mentioned and when I go in to troubleshoot the syntax it shows the
extra )
although I never put it in there.
One of the downsides to using the QBE Design Gride is that Jet will
do
things for you to optimize the query, whether you want it to or not.
The
solution is to use the SQL View pane instead, and edit the SQL
directly.
Please post your SQL and I'll try to detect the error that Jet is
injecting
into your query.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact
info.
I'm sorry, I should have clarified. The program seems to give me
the
error
mentioned and when I go in to troubleshoot the syntax it shows the
extra )
although I never put it in there.
I've run it several times with only one set of parenthesis, seems
to want
to
default to this syntax string with the error, even with your
suggestion of
CLng()...good idea, BTW. Any troubleshooting tactics to help me
avoid
this?
Thanks again.
:
Hi.
You have a typo. You have an extra closing parenthesis at the
end.
This:
Expr1: Val([AGENCY_PROFILE_ID]))
Should be this:
Expr1: Val([AGENCY_PROFILE_ID])
My overall objective is to reformat an ID field from text to
autonumber
without autonumber reassigning the numbers.
