Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Self Joins (Access 2000 >)

    Hi all,

    Does anyone have some sample scenarios of when and where to use self joins in a query. I'm trying to explain the concept to someone and I'm blank with examples of when it is best to use it! I know about the example in the Northwind sample database in office. Please give me another example or two?!?
    Thanx
    Regards,
    Rudi

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

    Re: Self Joins (Access 2000 >)

    Here is an example from the Sample Queries database: you have a table with milages and gallons of fuel used. (see screenshot). One way to retrieve the mileage from the previous record (useful for calculating miles/gallon) is to use a self-join:

    SELECT tblMileage.id, tblMileage.Date, tblMileage.Odometer, tblMileage_1.Odometer AS [Previous Odometer], tblMileage.Gallons
    FROM tblMileage LEFT JOIN tblMileage AS tblMileage_1 ON tblMileage.id-1 = tblMileage_1.id;

    (This query cannot be displayed in design view since it uses a non-standard join.)
    Attached Images Attached Images
    • File Type: png x.png (6.5 KB, 0 views)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Self Joins (Access 2000 >)

    Thanks for your effort Hans, as I know this is a rare type of query. I will look into this one; I have never seen it before!
    Regards,
    Rudi

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Self Joins (Access 2000 >)

    Hans: You referred to "...the Sampe Queries database." Where can I find this database? I tried Microsoft's website, searched the MSKB, but could find only references to sampel forms, sample reports...no sample queries. Maybe I'm asking the wrong question. Thanks!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Self Joins (Access 2000 >)

    You can download it at Access 2000 Sample: Query Topics
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Self Joins (Access 2000 >)

    You have also a form sample mdb at Access 2000 Sample: Database of Sample Forms and a report sample mdb at Access 2000 Sample: Report Topics
    Francois

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Self Joins (Access 2000 >)

    Thank you, Fran

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

    Re: Self Joins (Access 2000 >)

    Francois has been kind enough to provide the links. Here are some other sample databases.

    Sample Applications Download (Developer Solutions and Orders databases)

    And although the following two are for Access 97, they are still useful in later versions:

    ACC97: Neatcd97.mdb Available in Download Center (VBA code samples)
    ACC97: Microsoft Access 97 Sample Graphs Available in Download Center

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanx all, been most helpful!

    <P ID="nt"><font size=-1>(No Text)</font>
    Regards,
    Rudi

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

    Re: Self Joins (Access 2000 >)

    That address takes me to an empty Google page.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Self Joins (Access 2000 >)

    here is a nice real world example

    http://groups.google.com.au/groups?dq=&amp...s&start=100

    I changed the url and tested it and it worked for me but failing that the main parts are as follows.

    Problem
    I have a list of 50,000 records of which two records need
    to be removed. The olny thing I know about the two is that
    their sum = 1160.09. How can I get Access to evaluate the
    sum of any two of these records and determine which two
    sumed together provides this result

    A suggested Solution.

    I suggest you can create a new query, select SQL view and paste the
    following text:


    SELECT [MyTable].[RecordID] AS Record1, [MyTable_1].[RecordID] AS Record2,
    [MyTable].[NumberToSum] AS Value1, [MyTable_1].[NumberToSum] AS Value2,
    [MyTable].[NumberToSum]+[MyTable_1].[NumberToSum] AS SumOfValues
    FROM MyTable, MyTable AS MyTable_1
    WHERE ((([MyTable].[NumberToSum]+[MyTable_1].[NumberToSum])=1160.09));

    Then edit the text just to do the next changes:
    Change "MyTable" to the actual name of your list of records, (and also
    MyTable_1 to ActualName_1 )
    Change "RecordID" to the name of the field you used to identify your records
    (primary key)
    Change "NumberToSum" to the name of the field containing the number to sum

    I'm affraid because the size of your table, and probably the query is gonna
    perform quite slow. I think it would be better if you try it first by
    creating a little test table. Then you can decide if results satisfy your
    needs.

    The query will provide you a complete list of all pair of records that sum
    1160.09

Posting Permissions

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