Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Field name not recognised (2000sp3)

    I'm trying to run a find duplicates query, designed with the wizard and based on a single query, and keep receiving the error message that Jet doesn't recognise one of the field names as a valid field name. This despite the fact that the underlying query runs with no complaint, and no apparent errors. The field name in question is an alias, established in the underlying query, but I don't see why that should be a problem.

    Are there any circumstances in which this is known to happen?

    I've posted the SQL of both queries as an attachment.

    Thanks for your thoughts.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field name not recognised (2000sp3)

    Why don't you tell us which field name is not recognised?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Field name not recognised (2000sp3)

    oops, sorry.

    The unrecognised field is SAfldKI

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field name not recognised (2000sp3)

    Check carefully for typing errors in the field names - the SQL for both queries is OK in itself. For example, does KapIndividual really contain a field named SicknActionInField ?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Field name not recognised (2000sp3)

    OK, more investigation. The problem appears to be the existence of the second grouping field. It doesn't matter whether the second grouping field is an alias in the underlying query, or a native field name, or if the field is on the right hand side of the left join or not. Removing the second field (so the PropertyUID criterion reads: In (SELECT [PropertyUID] FROM [SortSickBehaveMaster] As Tmp GROUP BY [PropertyUID] HAVING Count(*)>1)) results in the query running fine, but trying to add any second duplicate grouping field (even demoting PropertyUID to the second field, as in In (SELECT [Hamlet] FROM [SortSickBehaveMaster] As Tmp GROUP BY [Hamlet],[PropertyUID] HAVING Count(*)>1 And [PropertyUID] = [SortSickBehaveMaster].[PropertyUID])) results in the error message MS jet database doesn't recognise [SortSickBehave Master].[ fieldname ] as a valid field name. If I change SortSickBehaveMaster to a maketable query, and then run this find duplicates query on the resulting table, everything works fine.

    So I presume I'm coming up against some internal complexity limitation, though I'm not quite sure why this should give me this particular error. SortSickBehaveMaster is based on 2 tables (FamilyMembers and KAPindividual) and one query (KHBednetBehaviourSubset) itself based on the KAPhousehold table and a query which sits atop a chain of queries 4 deep with a number of outer joins and predominantly based on some crosstab queries based on, among others, the Family Members table. None of the queries are particularly complicated by themselves, however, although 2 or 3 do include simple user defined functions, and I cannot see how I'm exceeding any of the query limitations set out in the online help.

    I attach the SQL of all the queries involved. All these queries run fine.

    I wanted to post the database, but because subsequent queries make use of column headings generated by crosstab queries as fieldnames, it's a nightmare to reduce the data to create a functional yet postable database, so I attach a sheet with the SQL of the queries underlying KHhouseholdsubset.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field name not recognised (2000sp3)

    Without seeing the database, it is too much work for me to analyse these SQL statements. Perhaps running a make-table query first, then running the find duplicates query against the table is your best bet.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Field name not recognised (2000sp3)

    I suspect you're right, Hans. Thanks. It would similarly be too much work for me to produce a useful cutdown of the database (and it is my problem, after all), so I'll have to stop trying to understand every problem and just work around it.

    Another of those inexplicable access anomalies my haphazard "development process" has a habit of throwing up from time to time.

Posting Permissions

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