Self Joins (Access 2000 >)

Topic

New Reply

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

Reply | Quote

Replies

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.)

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

You can download it at Access 2000 Sample: Query Topics

Reply | Quote

Thank you, Fran

Reply | Quote

Thank you, Fran

Reply | Quote

You can download it at Access 2000 Sample: Query Topics

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

(No Text)

Reply | Quote

(No Text)

Reply | Quote

That address takes me to an empty Google page.

Reply | Quote

That address takes me to an empty Google page.

Reply | Quote

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

Reply | Quote