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

    Tough query (WIN 2000 Acc 97)

    A company sends us a table of members with a column for
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Tough query (WIN 2000 Acc 97)

    Are you importing the new records into a separate table? If not, that would help your process, as you don't have to worry about the records you already have. Unfortunately your boss' assumption is not correct, as order isn't strictly maintained when you import a table, so using the higher number may ultimately cause you grief.

    You should be able to get the results you want with a query - but I don't quite understand the process you are going through so these suggestions may not completely fit. One is to use an unmatched query to find new records that exist in imported table but don't exist in the permanent table (or is there one?). Such queries can be constructed using the query wizard. The other is to use the query property that lets you get only unique values - that would eliminate duplicate records. If this isn't sufficient, post more details about your table structure and what functions you need to support.
    Wendell

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

    Re: Tough query (WIN 2000 Acc 97)

    I import the file using specs.
    The file could have up to 10,000 records. It is imported into a table called Permanent Table.
    I then create a new table for the "Active" members only. (Leaving the Permanent table 'unscathed' !!)
    It is a member roster, with members having 1,2,3 or 4 'transaction lines'.
    There are 5 possible Activity Codes.
    ADD
    TERM
    CHG ADD
    CHG TERM
    BLANK (Not the word blank, just no Activity Code)

    For every member who has a CHG ADD transaction line, there is always a CHG TERM transaction line.

    The table includes members who are "Active" as well as "Inactive" (based on the below criteria)
    My goal is to create a table, from this table, of only "ACTIVE" members.

    The definition, here, of an "ACTIVE" member is the following:

    1-Any member with just one transaction line.
    2-Any member with just 2 transaction lines which are CHG ADD & CHG TERM (I want to keep
    only the record that is the CHG ADD )
    3- Any member with just 2 transaction lines where the higher record number is "ADD" or is blank
    (I want to keep the ADD or the Blank)
    4- Now it becomes more difficult to explain, so I have added members with 3 and 4 records to
    the attached spreadsheet to show which ones must remain.
    (If there are 3 transaction lines, ignore the CHG ADD/CHG TERM lines and then keep only members
    with ADD or blank. If there are 4 transaction lines, ignroe the CHG ADD ADD/CHG TERM lines and keep
    only members where the higher record number of the remaining 2 transactions is ADD or blank)

    I can do this with a query, with code, whatever it takes.The bottom line is to take the table from
    the company, and convert it into only what is considered active members.

    Whewwww !! A bit winded there !! Thanks for looking at this. I hope it makes sense.

    Michael
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Tough query (WIN 2000 Acc 97)

    Well, I can see why you have some challenges! I also have a question - are you rebuilding the whole table of active members each time you run the process?

    I want to think about this a bit, but I am inclined to suggest that you create 4 (or 5) tables, and put each of the different kinds of records in a separate table, and then use joins to determine who is active. It seems off the top of my head that might be a pretty quick way to figure out who is active. What is your reaction?
    Wendell

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

    Re: Tough query (WIN 2000 Acc 97)

    "Well, I can see why you have some challenges <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    That was a great line Wendell !!!!

    This is how I am currently doing this.
    I import the table. Call it "Permanent Table".
    Then I copy & paste it and call the new table "Active Members".
    Then I have a query based on the Active Members table:
    In (SELECT [Member #] FROM [ACTIVE MEMBERS] As Tmp GROUP BY [Member #] HAVING Count(*) =1 )
    When this query is run, I delete some members right from the query, based on a date field. (The date is irrelevant to those with MORE than 1 transaction)
    After that, I change the query to read Count(*) = 2
    When the query is run, the first thing I do is filter (right from the query results) for CHG TERM. I delete all of these.
    Now, when I unfilter the query, the CHG ADDs of those members with 2 transactions don't show now because it is querying for 2 transactions.
    Of these members now, with 2 transactions, I manually delete the ones where the Rec# order shows the member
    as ADD then TERM or Blank then TERM. I delete both records.
    Then, of what is left, (which will be members with TERM then ADD, or TERM then Blank) I filter for the TERMs and delete them.
    Now I run the query again, and the only ones with 2 records left should be: BLANK with BLANK or BLANK with ADD. These I leave as Active (twice)
    Then I run the query with Count (*) = 3
    I manually delete all of the CHG ADD & CHG TERMS because they cancel each other out (unlike those with 2 transactions)
    Then I delete the TERMs.
    Then, of what is left, I need to again, only keep those where the highest record number is ADD or Blank (Just the one record)
    The others, I delete all 3 records.
    Then I run the query with Count(*) = 4
    This is the most complicated.
    If the member has 2 CHG ADD & CHG TERM , I keep 1 of the CHG ADDs
    If the member has 1 CHG ADD & CHG TERM, I delete them both.
    This will bring me back to the 2 transaction deal.
    I manually delete the ones where the Rec# order shows the member
    as ADD then TERM or Blank then TERM. I delete both records.
    Then, of what is left, (which will be members with TERM then ADD, or TERM then Blank) I filter for the TERMs and delete them.
    Now I run the query again, and the only ones with 2 records left should be: BLANK with BLANK or BLANK with ADD. These I leave as Active (twice)

    The amazing thing is that the above works perfectly for our needs. Like I said, although the records don't actually fall into the order of the file, there was no other way to attempt this. It isn't perfect, but it is consistent.

    I have to do the above for 7 tables (7 different clients) and it does take about 20 minutes to 1/2 hour for each.

    To be honest, I was thrilled just to obtain the code from the forum here to be able to query by transaction count !

    Thanks again for your help !! It is much appreciated.

    Michael
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Tough query (WIN 2000 Acc 97)

    You indicate that there is a date in the record - is it something that you could use to enforce an order? I'm still playing with the concept of using joins and multiple tables, or with self-joins. Your use of Count() does something similar, but I think it may be possible to really nail things in just a few simple steps.
    Wendell

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

    Re: Tough query (WIN 2000 Acc 97)

    The date only relates to single records. (It is a term date) The date is used to delete single records where the date field
    is not null. (Easy one for me !!)

    After going over this 100's of time with my co-worker, I thought I'd sit down and write out all the
    different combinations for 2,3 and 4 transaction lines. For the 2s, there are 9 combinations.
    There are more than 20 for 3 transactions, and I didn't bother with the 4 transactions. There are so
    few of these, that I can do them manually.
    It is really the 2 transactions that I am concerned with.

    I could make a table of the 2s and work them from there with a query.

    Does this make the task a bit simpler if we just concern ourselves with the 2 transactions?
    (See new attachment)

    Thank you so much Wendell. I have my co-worker following this along too.

    Michael
    Attached Files Attached Files

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

    Re: Tough query (WIN 2000 Acc 97)

    I have attached a database with queries that handle the 1, 2 and 3 records per person cases - I hope <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>. I left out the 4 records per person case since your description in <post#=305164>post 305164</post#> is not consistent with that in <post#=305145>post 305145</post#>, and since you state that the number of these is small anyway.
    Attached Files Attached Files

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

    Re: Tough query (WIN 2000 Acc 97)

    Hans,
    I am beside myself. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    We have tried to automate this process for three or four years now.
    All I did was 'click' the union query and the results popped up as desired.

    You have helped me out before, as well as helped hundreds of posters here. I wish I knew of a way to
    repay you for all you have done and continue to do here.

    Thank you so much. You are a very nice fellow! I appreciate it very, very much Hans.

    Sincerely,
    Michael Abrams

    *********
    And thank you to Wendell, who got me thinking of a better way to describe the situation.
    You have helped me in the past also, and I want to publicly thank you for offering your
    knowledge to all of us. It is truly appreciated.
    Michael

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

    Re: Tough query (WIN 2000 Acc 97)

    I have tweaked the queries to suit our process here, and have one question:
    I have a total of 8 queries plus the union query. They all produce the desired results. THANK YOU SO MUCH !!

    Can you see why this particular query takes forever to run? (The others pop up immediately)
    I have indexes on Rec#, Member # and Activity.

    SELECT [02 TWO TRANSACTIONS].*
    FROM [02 TWO TRANSACTIONS]
    WHERE ((([02 TWO TRANSACTIONS].[REC#])=(SELECT Max([Rec#]) FROM [02 TWO TRANSACTIONS] As Q WHERE Q.[MEMBER #] = [02 TWO TRANSACTIONS].[MEMBER #])) AND (([02 TWO TRANSACTIONS].Activity)="ADD" Or ([02 TWO TRANSACTIONS].Activity) Is Null));

    The ABOVE query is based on the following query:
    SELECT [HMO-ELIGIBILITY].*
    FROM [HMO-ELIGIBILITY]
    WHERE ((([HMO-ELIGIBILITY].[Member #]) In (SELECT [MEMBER #] FROM [HMO-ELIGIBILITY] As Tmp GROUP BY [MEMBER #] HAVING Count(*)=2)));

    The FIRST query takes a very long time, which forces the Union query to take a long time.

    Is there anything I should do?


    Thank you for looking !
    Michael

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

    Re: Tough query (WIN 2000 Acc 97)

    It is the nested query in the first query that slows things down. Try replacing this query by two others; this allows Access to optimize each.

    1. A query that selects the maximum Rec# for each Member #:

    SELECT [MEMBER #], Max([Rec#]) AS [MaxRec#]
    FROM [02 TWO TRANSACTIONS]
    GROUP BY [MEMBER #];

    Let's say you store this query as 02_MaxRec.

    2. A query based on

    SELECT [02 TWO TRANSACTIONS].[Rec#], [02 TWO TRANSACTIONS].[MEMBER #], [02 TWO TRANSACTIONS].Activity
    FROM [02_MaxRec] INNER JOIN [02 TWO TRANSACTIONS] ON ([02_MaxRec].[MaxRec#] = [02 TWO TRANSACTIONS].[Rec#]) AND ([02_MaxRec].[MEMBER #] = [02 TWO TRANSACTIONS].[MEMBER #])
    WHERE [02 TWO TRANSACTIONS].Activity="ADD" Or [02 TWO TRANSACTIONS].Activity Is Null;

    This query executes very fast. Check carefully that it returns the correct results before using it.

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

    Re: Tough query (WIN 2000 Acc 97)

    I have taken all of the information you provided and what used to take us a half hour per client,
    now takes less than 15 minutes total for all 9 clients !!

    Thanks so much for your guidance and expertise.

    Michael Abrams

Posting Permissions

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