Creating and identifying unique bundle packages

[LorenSmith]

I have data that I imported into access. File is huge. Basically, the file shows items listed at a location. I need to identify all the unique bundles at each location and show each location as a bundle and not as separate items.


For example of what I have:
Table A
Field 1 – Location
Field 2 - Item
Location 1 | Dell Computer
Location 1 | Phone
Location 1 | Fax
Location 1 | Desk
Location 2 | Dell Computer
Location 2 | Phone
Location 2 | Desk
Location 3 | Dell Computer
Location 3 | Phone
Location 3 | Fax
Location 3 | Desk

What I am trying to get to: Build of Materials (BOM)
Table 1
BOM 1 | Dell Computer
BOM 1 | Phone
BOM 1 | Fax
BOM 1 | Desk
BOM 2 | Dell Computer
BOM 2 | Phone
BOM 2 | Desk
Table 2
Location 1 | BOM 1
Location 2 | BOM 2
Location 3 | BOM 1

Thanks
Loren

 

[Golom]

Untested so you may need to correct my syntax and/or typing errors.

Dim SQL As String
Dim BOMNumber As Long
Dim CurrentBOM As Long
Dim rs As Recordset
Dim rsItems As Recordset

Set rs = db.Openrecordset ( "Select DISTINCT Location From tblA " )

Do Until rs.EOF
   SQL = "Select BOMNumber, ItemDescription " & _
         "From Table1 " 
         "Where ItemDescription IN " & _
         "      (Select Item From tblA " & _
         "       Where Location = '" & rs![Location] & "')" & _
         "Group By BOMNumber, ItemDescription " & _
         "HAVING Count(*) = (Select Count(*) From tblA " & _
                             Where  Location = '" & rs![Location] & "')"

   Set rsItems = db.OpenRecordset ( SQL )

   If rsItems.EOF then
      BOMNumber = BOMNumber + 1
      CurrentBOM = BOMNumber
      SQL = "INSERT INTO Table1 (BOMNumber, ItemDescription) " & _
            "Select " & BOMNumber & " As BOMNumber, Item " & _
            "From tblA "
            "Where Location = ' & rs![Location] & "'"
      db.Execute SQL
   Else
      CurrentBOM = rsItems![BOMNumber]
   End If

   SQL = "INSERT INTO Table2 (Location, BOMNumber) " & _
         "VALUES ('" & rs![Location] & "', " & CurrentBOM & ")"
   db.Execute SQL
   
   rs.MoveNext
Loop

This should work as long as each BOM (or location) has only unique items. For example, a location (or BOM) with 2 "Dell Computer" entries will screw up the works.