Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: The specified field could refer to more than one table in the from clause of your sql

    Hi
    I cannot figure out how to fix the problem I have with the access database. If anyone could help it would be greatly appreciated. I will give the SQL to produce the form / queries. Thanks


    SELECT DISTINCTROW [Environmental Aspect/Impact Assesment].Reference, [Environmental Aspect/Impact Assesment].Issue, [Environmental Aspect/Impact Assesment].Aspect, [Environmental Aspect/Impact Assesment].Activity, Sum([Environmental Aspect/Impact Assesment].Total) AS [Sum Of Total], [Environmental Aspect/Impact Assesment].Energy, [Environmental Aspect/Impact Assesment].Severity, [Environmental Aspect/Impact Assesment].Legislation, [Environmental Aspect/Impact Assesment].Quantity, [Environmental Aspect/Impact Assesment].Noise, ((([energyvalue]+[LegislationValue]+[Noise]+[QuantityValue]+[SeverityValue]+[SensitivityValue])*[likelyhoodvalue])*[controlsvalue])*[level] AS [sub Total], controlsdata.controlsvalue, [Environmental Aspect/Impact Assesment].Controls, [Environmental Aspect/Impact Assesment].[Cost Benefit/Viability], [Environmental Aspect/Impact Assesment].Frequency, [sub total]*[costvalue]*[assessmentvalue] AS Total, [Environmental Aspect/Impact Assesment].date, [Environmental Aspect/Impact Assesment].Impact, [Environmental Aspect/Impact Assesment].[Legislative Control], [Environmental Aspect/Impact Assesment].Sensitivity, [Legislation data].legislationvalue, [Noise Data].Noise, [Quantity data].QuantityValue AS Expr1, [severity data].SeverityValue AS Expr2, [Sensitivity data].SensitivityValue AS Expr3, energydata.EnergyValue, [Environmental Aspect/Impact Assesment].[Assessment Type], [Environmental Aspect/Impact Assesment].assessment, [assessment status data].level, [Environmental Aspect/Impact Assesment].[Current Controls], [Environmental Aspect/Impact Assesment].[Remedial Actions Required], [Environmental Aspect/Impact Assesment].[Completion Date], [Environmental Aspect/Impact Assesment].[Potential Impacts], [assessment type data].AssessmentValue, [Environmental Aspect/Impact Assesment].[Latest Revison], [Environmental Aspect/Impact Assesment].[Date of issue], [Environmental Aspect/Impact Assesment].[Current Controls], [Environmental Aspect/Impact Assesment].[Remedial Actions Required], [Environmental Aspect/Impact Assesment].[Potential Impacts]
    FROM ([assessment status data] INNER JOIN (likelyhooddata INNER JOIN (costdata INNER JOIN (controlsdata INNER JOIN ([Sensitivity data] INNER JOIN ([Noise Data] INNER JOIN ([Quantity data] INNER JOIN ([Legislation data] INNER JOIN ([severity data] INNER JOIN ([energy data] AS energydata INNER JOIN [Environmental Aspect/Impact Assesment] ON energydata.efficiency = [Environmental Aspect/Impact Assesment].Energy) ON [severity data].Severity = [Environmental Aspect/Impact Assesment].Severity) ON [Legislation data].legislation = [Environmental Aspect/Impact Assesment].Legislation) ON [Quantity data].Quantity = [Environmental Aspect/Impact Assesment].Quantity) ON [Noise Data].Noise = [Environmental Aspect/Impact Assesment].Sensitivity) ON [Sensitivity data].Sensitivity = [Environmental Aspect/Impact Assesment].Sensitivity) ON controlsdata.controls = [Environmental Aspect/Impact Assesment].Controls) ON costdata.cost = [Environmental Aspect/Impact Assesment].[Cost Benefit/Viability]) ON likelyhooddata.likelyhood = [Environmental Aspect/Impact Assesment].Frequency) ON [assessment status data].status = [Environmental Aspect/Impact Assesment].assessment) INNER JOIN [assessment type data] ON [Environmental Aspect/Impact Assesment].[Assessment Type] = [assessment type data].[assessment Type]
    GROUP BY [Environmental Aspect/Impact Assesment].Reference, [Environmental Aspect/Impact Assesment].Issue, [Environmental Aspect/Impact Assesment].Aspect, [Environmental Aspect/Impact Assesment].Activity, [Environmental Aspect/Impact Assesment].Energy, [Environmental Aspect/Impact Assesment].Severity, [Environmental Aspect/Impact Assesment].Legislation, [Environmental Aspect/Impact Assesment].Quantity, [Environmental Aspect/Impact Assesment].Noise, ((([energyvalue]+[LegislationValue]+[Noise]+[QuantityValue]+[SeverityValue]+[SensitivityValue])*[likelyhoodvalue])*[controlsvalue])*[level], controlsdata.controlsvalue, [Environmental Aspect/Impact Assesment].Controls, [Environmental Aspect/Impact Assesment].[Cost Benefit/Viability], [Environmental Aspect/Impact Assesment].Frequency, [Environmental Aspect/Impact Assesment].date, [Environmental Aspect/Impact Assesment].Impact, [Environmental Aspect/Impact Assesment].[Legislative Control], [Environmental Aspect/Impact Assesment].Sensitivity, [Legislation data].legislationvalue, [Noise Data].Noise, [Quantity data].QuantityValue, [severity data].SeverityValue, [Sensitivity data].SensitivityValue, energydata.EnergyValue, [Environmental Aspect/Impact Assesment].[Assessment Type], [Environmental Aspect/Impact Assesment].assessment, [assessment status data].level, [Environmental Aspect/Impact Assesment].[Current Controls], [Environmental Aspect/Impact Assesment].[Remedial Actions Required], [Environmental Aspect/Impact Assesment].[Completion Date], [Environmental Aspect/Impact Assesment].[Potential Impacts], [assessment type data].AssessmentValue, [Environmental Aspect/Impact Assesment].[Latest Revison], [Environmental Aspect/Impact Assesment].[Date of issue], [Environmental Aspect/Impact Assesment].[Current Controls], [Environmental Aspect/Impact Assesment].[Remedial Actions Required], [Environmental Aspect/Impact Assesment].[Potential Impacts], costdata.costvalue, likelyhooddata.likelyhoodvalue
    HAVING ((([Environmental Aspect/Impact Assesment].Reference) Like [AIA Ref#]));
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    You get that message if you are referring to a field that is in more than one table i.e. a field with the same name. To rectify the problem you must refer to the field by it's tablename as well.

    I think this line could be the problem:

    [energyvalue]+[LegislationValue]+[Noise]+[QuantityValue]+[SeverityValue]+[SensitivityValue])

    You should refer to the field names like you have in the rest of the code i.e. [Noise] should be [Environmental Aspect/Impact Assesment].Noise or whichever table it's from.
    Regards
    JD

    Software-Matters

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •