Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: Query (if or where logic)

    Hello Everyone,

    I am trying to run a query. My table name is tblPartInfo.

    Fields in my table (tblpartinfo)
    GrpNum - Group Number (ABC group and all 20,000 employees have same group number)
    PlanNum
    SSN
    Name
    DOB
    DOH
    DOT
    DOR
    YOS

    This table contain over 400,000 records. I would like to run a query where group number is same and have more than 10,000 records with the same group number. For example, Lets say I have ABC company's retirement plan and I have all the participant information in my database (13,000 employee information). I also have XYZ and their 15,000 employees informaiton in my database. and so on.

    So, all those abc should have same group number and xyz employees should have same group number.

    When I run the query i should see both of this group on my query.

    PROMPT HELP WILL BE APPRECIATED.

  2. #2
    Join Date
    Jan 2003
    Posts
    46
    Doran x 2,

    Since you say both groups should have the same group number, then use the group number for the query's criteria. Or perhaps I am missing the intent of yur question.

    Cheers,
    zambezibill

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This solution worked for me on a small scale so it must work for counts greater than 10,000. The solution only includes 3 of your fields in the output so you need to edit for the fields you want to include.

    This exercize was trial and error for me, by creating 2 queries to get the desired results, then by viewing and copying the SQL code, I constructed the following into a single SQL statement:

    SELECT TblPartInfo.GrpNum, TblPartInfo.PlanNum, TblPartInfo.[Name]
    FROM TblPartInfo AS FIRSTQ INNER JOIN TblPartInfo ON FIRSTQ.GrpNum = TblPartInfo.GrpNum
    GROUP BY TblPartInfo.GrpNum, TblPartInfo.PlanNum, TblPartInfo.[Name]
    HAVING (((Count(FIRSTQ.GrpNum))>10000));

    Jerry Dale

  4. #4
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Did not work...

    Hi Jerry,

    The sql script did not work. It executes and return zero records. Where I know for fact that I have 9 groups that exceeds over 10,000 records. That means 9 groups have over 10,000 employees in the tblpartinfo table. Each groups have same groups number for all the employees.

    grpNum Plannum name ssn dob doh
    12345 02 abc 645-11-2222 4/5/55 2/2/01
    12345 02 xyz 111-22-3333 2/12/48 12/1/02

    I did not see anyone when I ran the query. It does go through and returns zero records.

    Any suggession. Also, thanks for your help.

    NOTE: Group number is unique for one company.

    for example:

    Ice Cream Company, GrpNum 12345 (all employess for this company will have 12345 for their grpnum)

    Steel Mgfg, GrpNum 45678 (all employees for this company will have 45678 for their grpnum)

    Airborn Pipe, Grpnum 99978 (all 10,000 employees for this company will have 99978 grpnum)
    Last edited by doran_doran; 03-13-03 at 12:48.

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here's a small table (attached) with several of your fields. The query will display only records where the count of GrpNum is greater than 5. The SQL code is:

    SELECT TblPartInfo.GrpNum, TblPartInfo.PlanNum, TblPartInfo.Name, TblPartInfo.SSN
    FROM TblPartInfo AS FIRSTQ INNER JOIN TblPartInfo ON FIRSTQ.GrpNum = TblPartInfo.GrpNum
    GROUP BY TblPartInfo.GrpNum, TblPartInfo.PlanNum, TblPartInfo.Name, TblPartInfo.SSN
    HAVING (((Count(FIRSTQ.GrpNum))>5));

    Jerry
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Talking It worked....

    Thank you very much. It worked on a small database like the one you sent. I have already ran the query for the big database (over 500,000 records) and still runing. and I am sure it will once it finish performing query.

    Thanks again.

  7. #7
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Did not work for large number of records...

    Actually, the sql script worked for the small number of records. However, I ran the script for 3 hours and it never finished the job. I think thats because I have over 500,000 records. I think it calculating and keeping stuff in temporary memory. Any suggessions....

  8. #8
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    You'll need someone with more experience to help you with never-ending queries against mega-tables, but I do have an idea, assuming that this is a periodic task, and not just one time only. It also assumes that querying hundreds of thousands of records, using a 2-step query that counts, then selects, normally takes an unreasonable amount of time to complete.

    Before doing anything else I would try two separate queries instead of one to see 1) how long it takes to count records by GrpNum and put the results (a count for each GrpNum) into a temporary table. and 2) using the temporary table in step #1, how long it takes to pull all records of groups where the count exceeds 10,000. This should give you some insight on which part (or both) requires a lot of processing time. If this experiment completes in an acceptable amount of time, then I would use this technique, creating a temporary table and using it as a basis for the final query, and forget about my second idea which follows.

    To speed up the process, I would use numbers in a new field. I would add a long integer dummy field that I would update with a procedure that assigns sequential numbers within each GrpNum. The dummy field must be updated (by running the procedure) if records are added or deleted from the table, Assuming your table is sorted by GrpNum for this to work, your GrpNum and dummy field would look like this:

    GrpNum DummyField
    A 1
    A 2
    A 3
    ...etc.
    B 1
    C 1
    C 2
    ...etc.

    A query would select GrpNum where DummyField = 10001
    which identifies which GrpNum's you want to pull. You can use the query results in a second query, so you would add your table and #1 query into a second query, linking the field GrpNum, and adding the table fields in the query grid that you want in your results.

    This may not be how the professionals do it, but sometimes you do what you have to do to accomplish what looks impossible, or takes too long to complete because of the huge volume of records.

    I've used "number indexes" on a smaller scale, so I could only hope that my solution leads to completing the queries in a reasonable amount of time.

    Good luck.

Posting Permissions

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