I am confused on a querry

  • Thread starter Thread starter mario
  • Start date Start date
M

mario

Lets say following is a table:

DatabaseID InventoryID
MM-1-TE-001 21301405
MM-1-TE-002 21302406
MM-1-TE-003 21302405
MM-1-TE-004 21306540
MM-1-TE-005 21302406

I want to write a querry that
1. shows all the records
2. In another column show the databaseID's if the
InvertoryID is the same.

I would like the querry result to be:


DatabaseID InventoryID DuplicateID
MM-1-TE-001 21301405 MM-1-TE-003
MM-1-TE-002 21302406 MM-1-TE-005
MM-1-TE-003 21301405 MM-1-TE-001
MM-1-TE-004 21306540
MM-1-TE-005 21302406 MM-1-TE-002

Also, being a novice SQL user, I am having a tough time
understanding the types of joins, "Group By" and "HAving"
clauses. Can anybody refer me to good resources (really
simple for ordinary minds like me.)

Thanks
Mario
 
Getting little bit more ambitious. Lets say following is
the table

DatabaseID InventoryID
MM-1-TE-001 21301405
MM-1-TE-002 21302406
MM-1-TE-003 21301405
MM-1-TE-004 21306540
MM-1-TE-005 21301405


and following is the result

DatabaseID InventoryID NoOfOccurance OverLapID
MM-1-TE-001 21301405 3 MM-1-TE-
003, MM-1-TE-003
MM-1-TE-002 21302406 1
MM-1-TE-003 21301405 3 MM-1-TE-
001, MM-1-TE-005
MM-1-TE-004 21306540 1
MM-1-TE-005 21301405 3 MM-1-TE-
001, MM-1-TE-003

what would be my querry

Please help.
 
Getting little bit more ambitious. Lets say following is
the table

DatabaseID InventoryID
MM-1-TE-001 21301405
MM-1-TE-002 21302406
MM-1-TE-003 21301405
MM-1-TE-004 21306540
MM-1-TE-005 21301405


and following is the result

DatabaseID InventoryID NoOfOccurance OverLapID
MM-1-TE-001 21301405 3 MM-1-TE-
003, MM-1-TE-003
MM-1-TE-002 21302406 1
MM-1-TE-003 21301405 3 MM-1-TE-
001, MM-1-TE-005
MM-1-TE-004 21306540 1
MM-1-TE-005 21301405 3 MM-1-TE-
001, MM-1-TE-003
Click to expand...

This calls for a "Self Join" query. Stringing the matching OverlapID's
into a comma separated string will require some VBA code so let's take
it step by step:

1. Simply count duplicates

Create a Query based on Table1. Change it to a Totals query by
clicking the Greek Sigma icon (like a sideways M); Group By
InventoryID and Count DatabaseID.

2. Display the DatabaseID and the OverlapID for each overlap

Create a "Self Join" query by adding Table1 to the query window
*twice*. Access will alias the second instance by adding _1 to its
name. Join Table1.InventoryID to Table1_1.InventoryID, make the Join a
Left Join (click on the join line and select Option 2) to see even
those records that don't have duplicates, and select the DatabaseID
field from Table1_1 and alias it to OverlapID. Put a criterion on it
of
[Table1].[DatabaseID] OR IS NULL
Click to expand...

The SQL would look like

SELECT [Table1].[DatabaseID], [Table1].[InventoryID],
[Table1_1].[DatabaseID] AS OverlapID
FROM Table1 LEFT OUTER JOIN Table1 AS Table1_1
ON Table1.InventoryID = Table1_1.InventoryID
WHERE Table1_.DatabaseID > Table1.DatabaseID OR Table1_1.DatabaseID IS
NULL;

3. Display the Count and a comma-separated text string: modify the
query in 1) using VBA code adapted from

http://www.mvps.org/access/modules/mdl0004.htm
 
Back
Top