Hi! In Access 2007: I'm getting the following message executing the
query below. The From clause specifies a query that has no where
criteria either. I'm not understanding how this happens when the
query clearly has no "criteria" :

SELECT Max(vwClaimDetail.clm_uniqueID) AS MaxOfclm_uniqueID,
Max(vwClaimDetail.Match_Policy) AS MaxOfMatch_Policy,
Max(vwClaimDetail.client_num) AS MaxOfclient_num,
Max(vwClaimDetail.accid_num) AS MaxOfaccid_num,
Max(vwClaimDetail.cov_cd) AS MaxOfcov_cd,
Max(vwClaimDetail.claim_seq_num) AS MaxOfclaim_seq_num,
Max(vwClaimDetail.trans_seq) AS MaxOftrans_seq,
Max(vwClaimDetail.cme_trans_dt) AS MaxOfcme_trans_dt,
Max(vwClaimDetail.dt_type) AS MaxOfdt_type,
Max(vwClaimDetail.ext_claim_id) AS MaxOfext_claim_id,
Max(vwClaimDetail.policy_num) AS MaxOfpolicy_num,
Max(vwClaimDetail.cme_policy_effective_dt) AS
MaxOfcme_policy_effective_dt,
Max(vwClaimDetail.cme_policy_expiration_dt) AS
MaxOfcme_policy_expiration_dt, Max(vwClaimDetail.claim_status) AS
MaxOfclaim_status, Max(vwClaimDetail.cme_status_dt) AS
MaxOfcme_status_dt, Max(vwClaimDetail.cme_accident_dt) AS
MaxOfcme_accident_dt, Max(vwClaimDetail.day_of_accident) AS
MaxOfday_of_accident, Max(vwClaimDetail.cme_claim_dt) AS
MaxOfcme_claim_dt, Max(vwClaimDetail.accident_type_code) AS
MaxOfaccident_type_code, Max(vwClaimDetail.accident_source_code) AS
MaxOfaccident_source_code, Max(vwClaimDetail.accident_cause_code) AS
MaxOfaccident_cause_code, Max(vwClaimDetail.body_part_cd) AS
MaxOfbody_part_cd, Max(vwClaimDetail.loss_nature_pd_cd) AS
MaxOfloss_nature_pd_cd, Max(vwClaimDetail.loss_nature_bi_cd) AS
MaxOfloss_nature_bi_cd, Max(vwClaimDetail.reserve_type_cd) AS
MaxOfreserve_type_cd, Max(vwClaimDetail.method_of_settlement) AS
MaxOfmethod_of_settlement, Max(vwClaimDetail.convert_code) AS
MaxOfconvert_code, Max(vwClaimDetail.ncci_loss_cov_cd) AS
MaxOfncci_loss_cov_cd, Max(vwClaimDetail.loss_desc1) AS
MaxOfloss_desc1, Max(vwClaimDetail.loss_desc2) AS MaxOfloss_desc2,
Max(vwClaimDetail.yr_last_exposed) AS MaxOfyr_last_exposed,
Max(vwClaimDetail.accident_st) AS MaxOfaccident_st,
Max(vwClaimDetail.accident_country) AS MaxOfaccident_country,
Max(vwClaimDetail.benefit_st) AS MaxOfbenefit_st,
Max(vwClaimDetail.benefit_country) AS MaxOfbenefit_country,
Max(vwClaimDetail.exposure_st) AS MaxOfexposure_st,
Max(vwClaimDetail.exposure_country) AS MaxOfexposure_country,
Max(vwClaimDetail.state_claim_num) AS MaxOfstate_claim_num,
Max(vwClaimDetail.cme_employer_notif_dt) AS
MaxOfcme_employer_notif_dt, Max(vwClaimDetail.cme_original_open_dt) AS
MaxOfcme_original_open_dt, Max(vwClaimDetail.cme_reported_dt) AS
MaxOfcme_reported_dt, Max(vwClaimDetail.legal_firm_id) AS
MaxOflegal_firm_id, Max(vwClaimDetail.in_suit_flag) AS
MaxOfin_suit_flag, Max(vwClaimDetail.independent_adjuster_id) AS
MaxOfindependent_adjuster_id, Max(vwClaimDetail.vin) AS MaxOfvin,
Max(vwClaimDetail.driver_chargeable) AS MaxOfdriver_chargeable,
Max(vwClaimDetail.surgery_flag) AS MaxOfsurgery_flag,
Max(vwClaimDetail.gb_branch_num) AS MaxOfgb_branch_num,
Max(vwClaimDetail.employer_premises_flag) AS
MaxOfemployer_premises_flag, Max(vwClaimDetail.adjuster_num) AS
MaxOfadjuster_num, Max(vwClaimDetail.reviewer_num) AS
MaxOfreviewer_num, Max(vwClaimDetail.adjuster_review) AS
MaxOfadjuster_review, Max(vwClaimDetail.reviewer_review) AS
MaxOfreviewer_review, Max(vwClaimDetail.num_dependents) AS
MaxOfnum_dependents, Max(vwClaimDetail.original_damage_est) AS
MaxOforiginal_damage_est, Max(vwClaimDetail.billing_cd) AS
MaxOfbilling_cd, Max(vwClaimDetail.name_type) AS MaxOfname_type,
Max(vwClaimDetail.name) AS MaxOfname, Max(vwClaimDetail.address1) AS
MaxOfaddress1, Max(vwClaimDetail.address2) AS MaxOfaddress2,
Max(vwClaimDetail.address3) AS MaxOfaddress3, Max(vwClaimDetail.city)
AS MaxOfcity, Max(vwClaimDetail.state) AS MaxOfstate,
Max(vwClaimDetail.country) AS MaxOfcountry,
Max(vwClaimDetail.zip_code) AS MaxOfzip_code, Max(vwClaimDetail.age)
AS MaxOfage, Max(vwClaimDetail.sex) AS MaxOfsex,
Max(vwClaimDetail.ssn) AS MaxOfssn, Max(vwClaimDetail.weekly_wage) AS
MaxOfweekly_wage, Max(vwClaimDetail.ncci_job_cd) AS MaxOfncci_job_cd,
Max(vwClaimDetail.len_of_svc) AS MaxOflen_of_svc,
Max(vwClaimDetail.controverted_case_flag) AS
MaxOfcontroverted_case_flag, Max(vwClaimDetail.employment_status_flag)
AS MaxOfemployment_status_flag, Max(vwClaimDetail.cme_hire_dt) AS
MaxOfcme_hire_dt, Max(vwClaimDetail.cme_birth_dt) AS
MaxOfcme_birth_dt, Max(vwClaimDetail.cme_death_dt) AS
MaxOfcme_death_dt, Max(vwClaimDetail.marital_status) AS
MaxOfmarital_status, Max(vwClaimDetail.num_dependents2) AS
MaxOfnum_dependents2, Max(vwClaimDetail.employee_id) AS
MaxOfemployee_id, Max(vwClaimDetail.dept) AS MaxOfdept,
Max(vwClaimDetail.occupation) AS MaxOfoccupation,
Max(vwClaimDetail.compensation_rate) AS MaxOfcompensation_rate,
Max(vwClaimDetail.ncci_injury_code) AS MaxOfncci_injury_code,
Max(vwClaimDetail.claimant_phone_num) AS MaxOfclaimant_phone_num,
Max(vwClaimDetail.cme_disability_dt) AS MaxOfcme_disability_dt,
Max(vwClaimDetail.cme_return_work_dt) AS MaxOfcme_return_work_dt,
Max(vwClaimDetail.cme_max_med_improv_dt) AS
MaxOfcme_max_med_improv_dt, Max(vwClaimDetail.attorney_rep_flag) AS
MaxOfattorney_rep_flag, Max(vwClaimDetail.post_injury_weekly_wage) AS
MaxOfpost_injury_weekly_wage, Max(vwClaimDetail.pct_impairment) AS
MaxOfpct_impairment, Max(vwClaimDetail.accident_city_code) AS
MaxOfaccident_city_code, Max(vwClaimDetail.full_state_claim_no) AS
MaxOffull_state_claim_no, Max(vwClaimDetail.insurer_name) AS
MaxOfinsurer_name, Max(vwClaimDetail.reduced_amt_benefit_cd) AS
MaxOfreduced_amt_benefit_cd, Max(vwClaimDetail.occupation_50) AS
MaxOfoccupation_50, Max(vwClaimDetail.wc_claim_type) AS
MaxOfwc_claim_type, Max(vwClaimDetail.other_weekly_payments) AS
MaxOfother_weekly_payments, Max(vwClaimDetail.accident_zip_code) AS
MaxOfaccident_zip_code, Max(vwClaimDetail.cme_attorney_disclosure_dt)
AS MaxOfcme_attorney_disclosure_dt,
Max(vwClaimDetail.salvage_recover_flag) AS MaxOfsalvage_recover_flag,
Max(vwClaimDetail.subrogation_recovery_flag) AS
MaxOfsubrogation_recovery_flag,
Max(vwClaimDetail.statefund_recovery_flag) AS
MaxOfstatefund_recovery_flag, Max(vwClaimDetail.other_recovery_flag)
AS MaxOfother_recovery_flag, Max(vwClaimDetail.cme_rept_basis_dt) AS
MaxOfcme_rept_basis_dt, Max(vwClaimDetail.rept_basis_cd) AS
MaxOfrept_basis_cd, Max(vwClaimDetail.cme_occurrence_dt) AS
MaxOfcme_occurrence_dt, Max(vwClaimDetail.client_num_multi_claim) AS
MaxOfclient_num_multi_claim, Max(vwClaimDetail.mc_occur_flag) AS
MaxOfmc_occur_flag, Max(vwClaimDetail.death_result_cd) AS
MaxOfdeath_result_cd, Max(vwClaimDetail.ncci_method_settlement) AS
MaxOfncci_method_settlement, Max(vwClaimDetail.ss_offset_flag) AS
MaxOfss_offset_flag, Max(vwClaimDetail.unemployment_offset_flag) AS
MaxOfunemployment_offset_flag, Max(vwClaimDetail.pension_offset_flag)
AS MaxOfpension_offset_flag, Max(vwClaimDetail.other_offset_flag) AS
MaxOfother_offset_flag, Max(vwClaimDetail.deductible_flag) AS
MaxOfdeductible_flag, Max(vwClaimDetail.program_layer_id) AS
MaxOfprogram_layer_id, Max(vwClaimDetail.loss_program_num) AS
MaxOfloss_program_num, Max(vwClaimDetail.claim_period_num) AS
MaxOfclaim_period_num, Max(vwClaimDetail.employee_num) AS
MaxOfemployee_num, Max(vwClaimDetail.cme_claim_denial_dt) AS
MaxOfcme_claim_denial_dt, Max(vwClaimDetail.federal_tax_id) AS
MaxOffederal_tax_id, Max(vwClaimDetail.weekly_benefit_amt) AS
MaxOfweekly_benefit_amt, Max(vwClaimDetail.product_name) AS
MaxOfproduct_name, Max(vwClaimDetail.ncci_type_of_loss_flag) AS
MaxOfncci_type_of_loss_flag, Max(vwClaimDetail.ncci_ppo_flag) AS
MaxOfncci_ppo_flag, Max(vwClaimDetail.ncci_fraud_indicator) AS
MaxOfncci_fraud_indicator, Max(vwClaimDetail.ncci_act) AS
MaxOfncci_act, Max(vwClaimDetail.ss_other_off_amt) AS
MaxOfss_other_off_amt, Max(vwClaimDetail.tot_lost_work_days) AS
MaxOftot_lost_work_days, Max(vwClaimDetail.sic_code) AS MaxOfsic_code,
Max(vwClaimDetail.degree_of_loss_flag) AS MaxOfdegree_of_loss_flag,
Max(vwClaimDetail.appraised_eligibility) AS
MaxOfappraised_eligibility, Max(vwClaimDetail.client_claim_cd1) AS
MaxOfclient_claim_cd1, Max(vwClaimDetail.client_claim_cd2) AS
MaxOfclient_claim_cd2, Max(vwClaimDetail.client_claim_cd3) AS
MaxOfclient_claim_cd3, Max(vwClaimDetail.client_claim_cd4) AS
MaxOfclient_claim_cd4, Max(vwClaimDetail.catastrophe_code) AS
MaxOfcatastrophe_code, Max(vwClaimDetail.client_accid_cd1) AS
MaxOfclient_accid_cd1, Max(vwClaimDetail.client_accid_cd2) AS
MaxOfclient_accid_cd2, Max(vwClaimDetail.client_accid_cd3) AS
MaxOfclient_accid_cd3, Max(vwClaimDetail.client_accid_cd4) AS
MaxOfclient_accid_cd4, Max(vwClaimDetail.driver_name_format) AS
MaxOfdriver_name_format, Max(vwClaimDetail.driver_last_name) AS
MaxOfdriver_last_name, Max(vwClaimDetail.driver_first_name) AS
MaxOfdriver_first_name, Max(vwClaimDetail.driver_age) AS
MaxOfdriver_age, Max(vwClaimDetail.driver_sex) AS MaxOfdriver_sex,
Max(vwClaimDetail.driver_ssn) AS MaxOfdriver_ssn,
Max(vwClaimDetail.external_key) AS MaxOfexternal_key,
Max(vwClaimDetail.unit_name_type) AS MaxOfunit_name_type,
Max(vwClaimDetail.unit_last_name) AS MaxOfunit_last_name,
Max(vwClaimDetail.unit_first_name) AS MaxOfunit_first_name,
Max(vwClaimDetail.unit_address1) AS MaxOfunit_address1,
Max(vwClaimDetail.unit_address2) AS MaxOfunit_address2,
Max(vwClaimDetail.unit_address3) AS MaxOfunit_address3,
Max(vwClaimDetail.unit_city) AS MaxOfunit_city,
Max(vwClaimDetail.unit_state) AS MaxOfunit_state,
Max(vwClaimDetail.unit_country) AS MaxOfunit_country,
Max(vwClaimDetail.unit_zip) AS MaxOfunit_zip,
Max(vwClaimDetail.vehicle_yr) AS MaxOfvehicle_yr,
Max(vwClaimDetail.vehicle_make) AS MaxOfvehicle_make,
Max(vwClaimDetail.loss_payee_name_type) AS MaxOfloss_payee_name_type,
Max(vwClaimDetail.loss_payee_last_name) AS MaxOfloss_payee_last_name,
Max(vwClaimDetail.loss_payee_first_name) AS
MaxOfloss_payee_first_name, Max(vwClaimDetail.accd_loc_city) AS
MaxOfaccd_loc_city, Max(vwClaimDetail.beneficiary_cd1) AS
MaxOfbeneficiary_cd1, Max(vwClaimDetail.beneficiary_bday1) AS
MaxOfbeneficiary_bday1, Max(vwClaimDetail.beneficiary_cd2) AS
MaxOfbeneficiary_cd2, Max(vwClaimDetail.beneficiary_bday2) AS
MaxOfbeneficiary_bday2, Max(vwClaimDetail.beneficiary_cd3) AS
MaxOfbeneficiary_cd3, Max(vwClaimDetail.beneficiary_bday3) AS
MaxOfbeneficiary_bday3, Max(vwClaimDetail.beneficiary_cd4) AS
MaxOfbeneficiary_cd4, Max(vwClaimDetail.beneficiary_bday4) AS
MaxOfbeneficiary_bday4, Max(vwClaimDetail.beneficiary_cd5) AS
MaxOfbeneficiary_cd5, Max(vwClaimDetail.beneficiary_bday5) AS
MaxOfbeneficiary_bday5, Max(vwClaimDetail.companion_accident) AS
MaxOfcompanion_accident, Max(vwClaimDetail.companion_cov_cd) AS
MaxOfcompanion_cov_cd, Max(vwClaimDetail.companion_cov_seq_num) AS
MaxOfcompanion_cov_seq_num, Max(vwClaimDetail.cdt_dt_last_worked) AS
MaxOfcdt_dt_last_worked,
Max(vwClaimDetail.full_pay_day_of_injury_flag) AS
MaxOffull_pay_day_of_injury_flag,
Max(vwClaimDetail.cdt_released_rtw_dt) AS MaxOfcdt_released_rtw_dt,
Max(vwClaimDetail.rtw_flag) AS MaxOfrtw_flag,
Max(vwClaimDetail.salary_cont_flag) AS MaxOfsalary_cont_flag,
Max(vwClaimDetail.claim_ever_controverted) AS
MaxOfclaim_ever_controverted, Max(vwClaimDetail.lost_time_case_flag)
AS MaxOflost_time_case_flag,
Max(vwClaimDetail.cdt_dt_of_representation) AS
MaxOfcdt_dt_of_representation,
Max(vwClaimDetail.cdt_salary_cont_st_dt) AS
MaxOfcdt_salary_cont_st_dt, Max(vwClaimDetail.cdt_salary_cont_end_dt)
AS MaxOfcdt_salary_cont_end_dt,
Max(vwClaimDetail.cdt_rtw_full_duty_dt) AS MaxOfcdt_rtw_full_duty_dt,
Max(vwClaimDetail.loss_client_num) AS MaxOfloss_client_num,
Max(vwClaimDetail.loss_ref_num) AS MaxOfloss_ref_num,
Max(vwClaimDetail.trading_prtnr_cd) AS MaxOftrading_prtnr_cd,
Max(vwClaimDetail.trading_prtnr_cd_type) AS
MaxOftrading_prtnr_cd_type, Max(vwClaimDetail.loss_claimant_seq_num)
AS MaxOfloss_claimant_seq_num,
Max(vwClaimDetail.cdt_medical_to_indemnity_dt) AS
MaxOfcdt_medical_to_indemnity_dt
FROM vwClaimDetail;

w wrote:
Jaysus! Did you really think showing us all that would do any good?! ;-)

First step. Run the following and tell us if you get the same error:

SELECT * FROM vwClaimDetail;

Well, just to save you and us some time, if it does run without error, try

SELECT Max(vwClaimDetail.clm_uniqueID) AS MaxOfclm_uniqueID
FROM vwClaimDetail;

Then ... well ... do you see where I'm going here? If the above runs without
error, put in the first half of the however many there are MAX expressions
and try to run the query.
If you get the error, then remove half and try again. Keep going until you
isolate the offending expression
No error? Try the next quarter. Then the next eighth. Keep going until you
can find out which expression produces the error.

If simply selecting * from the query produces the error, or a single MAX
expression produces the error, then it's time to examine the sql in
vwClaimDetail.

PS. The "criteria expression" does not have to be in a WHERE clause - it
can also be in an ON clause (part of a JOIN).

Thats one hell of a select statement!
--
Cheers

The Frog

On 18/08/2011 14:34:04, The Frog wrote:
My guess is that there are about 190 Max clauses, I loose track after fingers
& toes are exhausted, but I guess that each one represents a sub query, and
that system or Access resources are being exceeded.
Under these conditions, you don't always get the correct error message.
Phil

On Aug 18, 8:34*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:
That is a true statement.

But if you think about it, it'd take no time at all to write. Create
a new query, add the table, click/highlite all the fields and add to
the columns row, hit the totals button, and select Max for all of the
columns. Then save the file.

Is there a length limit on a SQL statement? I'm not going to google
on it.

I'd probably create 2-4 Totals query on that table based on segments,
all with the primary key. Then I'd create another query with just the
primary key. Then I'd create another query and link the table with
just the primary key to all of the other totals queries. And see if
that runs. Remove segments as needed if there is a problem.

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

Or, perhaps, "The Select Statement from Hell". <GRIN>

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access