Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Unanswered: downdrill back after grouping

    I have a table X with 18.000 records from which I have created a new table Y of only 2000 records by doing some grouping and summing on some columns. Apart from the columns that are present in table X I have added an autonumber field in table Y. What I now need to be able to do is identify which record from table X has ended up in which grouped record in table Y. Normally I would just do this by building a query and doing an inner join between the two tables on the columns I have grouped on. The problem is however that some of the columns I have grouped on contain Null values making it not usable in a join. Is there any way to solve this problem?
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a look at the different join types available to you
    http://w3schools.com/sql/sql_join.asp

    LEFT
    RIGHT
    FULL
    INNER
    are the main 4
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2003
    Posts
    61
    Quote Originally Posted by georgev
    Have a look at the different join types available to you
    http://w3schools.com/sql/sql_join.asp

    LEFT
    RIGHT
    FULL
    INNER
    are the main 4
    I am not sure how this can solve the problem I have
    Nobody dies a virgin because life screws us all!

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This looks like an instance where database design was checked at the door ... How in the world can you create a dependent table where you don't even bother to MAKE sure that there is an explicit relationship between them? Go back to school and relearn how to build dependent tables ... As for solving your problem: There is nothing SQL-wise that can guarantee retrieving the right record(s) (you said so yourself!) so, you can try to add something on after the fact to at least have a column in common for the 2 tables ...

    In your summary table add a column for a key value then run an UPDATE query to sequence that column.

    In you "detail" table, add a column that will be the relating column to the summary table then figure out the SQL to pull the MOST likely summary record ID # to assign into each detail record.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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