Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Creating new field with Not In

    I have several append queries that I inherited and I'm trying to add unique identifiers (PK) to each query. I also want to prevent duplicates. I created a macro through Excel that updates 10+ queries each month. I run into errors though when I put my criteria at the append level. I know I can correct this behavior if I create the identifier field in a query before the Append query, but I'd have a lot of different queries to change. Anything else I can do?

    Code:
    INSERT INTO [tbl Admissions Summary] ( FOM, FinancialClassID, Department, CountOfVisitID, Identifier )
    SELECT [qry Admissions].FOM, [qry Admissions].FinancialClassID, [qry Admissions].Department, Count([qry Admissions].VisitID) AS CountOfVisitID, ([FOM] & [FinancialClassID] & [Department]) AS Identifier
    FROM [qry Admissions]
    GROUP BY [qry Admissions].FOM, [qry Admissions].FinancialClassID, [qry Admissions].Department, ([FOM] & [FinancialClassID] & [Department])
    HAVING (((([FOM] & [FinancialClassID] & [Department])) Not In (SELECT [tbl Admissions Summary]![Identifier] FROM  [tbl Admissions Summary])));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The selection criteria applies to all records, not only on those grouped by. You should try using WHERE, not HAVING.
    Have a nice day!

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    I"m still getting a syntax error (missing operator) on the query expression.

    Here is my current version. Where am I going wrong?


    Code:
    INSERT INTO [tbl Admissions Summary] ( FOM, FinancialClassID, Department, CountOfVisitID, Identifier )
    SELECT [qry Admissions].FOM, [qry Admissions].FinancialClassID, [qry Admissions].Department, Count([qry Admissions].VisitID) AS CountOfVisitID, ([qry Admissions].[FOM] & [qry Admissions].[FinancialClassID] & [qry Admissions].[Department]) AS Identifier
    FROM [qry Admissions]
    GROUP BY [qry Admissions].FOM, [qry Admissions].FinancialClassID, [qry Admissions].Department, ([FOM] & [FinancialClassID] & [Department])
    WHERE (((([qry Admissions].[FOM] & [qry Admissions].[FinancialClassID] & [qry Admissions].[Department]) Not In (SELECT [tbl Admissions Summary].[Identifier] FROM  [tbl Admissions Summary])));

Posting Permissions

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