Parts and Models List (1 Viewer)

[MajP]

I still do not really understand what you start with and how you get to the CSV. But I will assume somehow you get a weird CSV in this format with Models in the first row and parts below it.

If you can preprocess outside the database into the correct format it would easier. It should look like this.

I did it in code. This normalize the CSV.

Public Sub FillModelParts()
  Const ImportTable = "models_Parts_Example"
  Const tableName = "tblModelParts"
  Const PartField = "PartNumber"
  Const ModelField = "ModelNumber"
 
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim PartNumber As String
  Dim modelNumber As String
  Dim strSql As String
 
 
 
  Set rs = CurrentDb.OpenRecordset(ImportTable, dbOpenDynaset)
   For Each fld In rs.Fields
  Do While Not rs.EOF
         Debug.Print rs.AbsolutePosition
         If rs.AbsolutePosition = 0 Then
           modelNumber = fld.Value
         Else
            If Not IsNull(fld.Value) Then
              PartNumber = fld.Value
              strSql = "insert into " & tableName & " (" & ModelField & ", " & PartField & ") values ('" & modelNumber & "', '" & PartNumber & "')"
              Debug.Print strSql
              CurrentDb.Execute strSql
            End If
         End If
      
      
      rs.MoveNext
    Loop
    rs.MoveFirst
   Next fld
End Sub

Now you can do any query you want.
I can group by part and count how many model that part is in.

I can query to see which models have two specific parts. These models have both parts in the query.

 

[MadPiet]

I think that attaching a text/csv worked. Hopefully that the final edit

 

[mreinsmith]

This is simply how I organize it in a spreadsheet for other uses. Mostly just copy and paste.

I will do things like removing duplicates or finding common model numbers across the part

 

[MajP]

I provided the answer. Simply build the junction table I showed and you are done.

 

[MadPiet]

Okay, I get it now. No wonder you're so confused! (I would be too!). Here's the problem - to get this to work in your database, you need a different structure. You need basically just two columns, one for PartNumber and the other for ModelNumber. Do you get tons of these files basically in this format? I'm just trying to figure out the best way to solve this problem. Because you can likely clean up the data/unpivot it in Excel, and then move it into Access

In the meantime, I cobbled together some terrible code to create a big fat union query to normalize your stuff:

SELECT 'AN492088' AS ModelNo, AN492088 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'S397095' AS ModelNo, S397095 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'S494035' AS ModelNo, S494035 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'AN495642' AS ModelNo, AN495642 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'S495643' AS ModelNo, S495643 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'AN495644' AS ModelNo, AN495644 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'K495645' AS ModelNo, K495645 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'K499764' AS ModelNo, K499764 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'K499765' AS ModelNo, K499765 AS PartNo FROM qAllModelPartsColumns UNION ALL
SELECT 'K692142' AS ModelNo, K692142 AS PartNo FROM qAllModelPartsColumns;

If you save that as a query, you end up with 2 columns (ModelNo, PartNo), which you can use to solve your problem for now... Ideally, I'd do this in Excel though. Okay, here's the PowerQuery to do it in Excel... The upside to doing it in Excel is that you can do make a minor change and process an entire folder of files all at once, and then you can just link that to Access and import everything. Here's the PowerQuery (no, I'm not expecting you to understand it all... I'm just saying that there are other ways of doing it than using Access, which is not great at data cleaning/reshaping.)

let
    Source = Csv.Document(File.Contents("C:\Users\piete\Downloads\Models_Parts_Example (2).txt"),[Delimiter=",", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"AN492088", type text}, {"S397095", type text}, {"S494035", type text}, {"AN495642", type text}, {"S495643", type text}, {"AN495644", type text}, {"K495645", type text}, {"K499764", type text}, {"K499765", type text}, {"K692142", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Model"}, {"Value", "PartNumber"}})
in
    #"Renamed Columns"