Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete query syntax (WIN XP/Access 97)

    I have a table that has a 'RecordNum" field, a name and an activity code (and about 10 other non-relevant fields for this query)
    The records are imported with the record# so that if I sort the table by the record number, the records are in the order that they were intended to be. (Basically, a 'hard-coded' record# - client's doing)
    There are 2 activity codes. ADD, and TERM.
    Each person has 2,3,4 records. I want to query only those with 2 records where One is ADD, and the other is TERM.
    I already have the part where I select the members with 2 records:
    In (SELECT [MEMNAME FROM [CURRENT MONTH MEMBER COUNT HMO] As Tmp GROUP BY [MEMNAME] HAVING Count(*) =2 )

    Depending on the order (by ascending record#) I either want to keep the ADD record, or delete BOTH records for that person.
    I only want to keep the record where the person has 2 records and the first one is ADD.
    Example:
    1 John Smith ADD
    2 John Smith TERM
    3 Jane Doe TERM
    4 Jane Doe ADD
    5 Mark Smith ADD
    6 Mark Smith TERM

    I want to run a query that will leave Records 1&5 and delete records 2,3,4,6

    Is this possible?

    Thank you very much.

    Michael

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

    Re: Delete query syntax (WIN XP/Access 97)

    This is going to take a series of queries, and it might be slow if you have a large number of records. Let's say that the query that selects the members who have exactly two records is named qrySelect2. Create a query qryMinNum to select the minimum RecNum for each member in this query:

    SELECT Min(RecordNum) AS MinOfRecordNum, MemName
    FROM qrySelect2
    GROUP BY MemName;

    Next, create a query qryMinActivity to select the members for whom the first ActivityCode is TERM:

    SELECT qrySelect2.MemName
    FROM qryMinNum INNER JOIN qrySelect2 ON (qryMinNum.MinOfRecordNum = qrySelect2.RecordNum) AND (qryMinNum.MemName = qrySelect2.MemName)
    WHERE qrySelect2.ActivityCode="TERM";

    Create a query qryMaxNum to select the highest RecNum for each member in qrySelect2

    SELECT Max(RecordNum) AS MaxOfRecordNum, MemName
    FROM qrySelect2
    GROUP BY MemName;

    and also a query qryMaxActivity to select the RecordNums of members whose "highest" ActivityCode is TERM:

    SELECT qrySelect2.RecordNum
    FROM qryMaxNum INNER JOIN qrySelect2 ON (qryMaxNum.MemName = qrySelect2.MemName) AND (qryMaxNum.MaxOfRecordNum = qrySelect2.RecordNum)
    WHERE qrySelect2.ActivityCode="TERM";

    Finally, create a query based on the table to delete the appropriate records:

    DELETE T.RecordNum, T.MemName
    FROM [CURRENT MONTH MEMBER COUNT HMO] AS T
    WHERE (((T.RecordNum) In (SELECT RecordNum FROM qryMaxActivity))) OR (((T.MemName) In (SELECT MemName FROM qryMinActivity)));

    Before trying this, make a backup copy of the database!

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete query syntax (WIN XP/Access 97)

    Thank you very much Hans. I will trying it out first thing Monday morning.

    I appreciate your patience and knowledge.

    Sincerely,
    Michael

Posting Permissions

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