I am confused on a querry

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
 
G

Guest

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

John Vinson

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
 
Top