0

I need to fill MS access database table with a number of information about certain products. I managed to fill almost everything, but I ran into following issue:

I have a separate record for each part of the same product in a table called Parts. I also have a separate record for each product in a table called Products.

I need to take data from Parts.PartCode to Products.ListOfParts if the Parts.ProductCode and Products.ProductCode is the same, however I have two problems:

  1. I do not know how to make a query that will fill particular row in Products.ListOfParts with data from numerous rows in Parts.PartCode (could be anything between 1 and 10);

  2. For some reason, even though the table Products is filled completely, except for the Products.ListOfParts column, every time I try to make an INSERT query, it asks me to provide value of Products.ProductCode. This is the query I tried running:

    INSERT INTO Products ( ListOfParts )
SELECT Parts.PartCode
FROM Parts
WHERE Parts.ProductCode = Products.ProductCode;

Edit (sample data from tables):

Products:

+----+---------+-------+-------------+-------------+
| Id |  Name   | SpId  | ListOfParts | ProductCode |
+----+---------+-------+-------------+-------------+
|  1 | HWN2007 | Y2271 |             | OP00P0      |
|  2 | SD39HE  | 6G121 |             | NE0301      |
+----+---------+-------+-------------+-------------+

Parts:

+-------+---------+-------------+-----------+
|  Id   |  SpId   | ProductCode | PartCode  |
+-------+---------+-------------+-----------+
| 15321 | 5162F7  | OP00P0      | VC-192919 |
| 15322 | 5172H1  | OP00P0      | VC-888192 |
+-------+---------+-------------+-----------+

Desired result:

+----+---------+-------+----------------------+-------------+
| Id |  Name   | SpId  |     ListOfParts      | ProductCode |
+----+---------+-------+----------------------+-------------+
|  1 | HWN2007 | Y2271 | VC-192919, VC-888192 | OP00P0      |
+----+---------+-------+----------------------+-------------+
June7
  • 19,874
  • 8
  • 24
  • 34
sneakysnek
  • 1
  • 2
  • An example of the contents & structure of your `Parts` table and `Products` table, and the desired result would be helpful here. From your description it sounds like an `UPDATE` query may be more suitable. – Lee Mac Jan 22 '18 at 13:21
  • Apologies, should've thought about that earlier. – sneakysnek Jan 22 '18 at 13:32
  • Your sample tables are almost unreadable. See [how to share a table for MS Access questions](https://meta.stackoverflow.com/q/359195/7296893) – Erik A Jan 22 '18 at 13:38
  • Fixed, sorry again! – sneakysnek Jan 22 '18 at 13:54
  • There are many, many answers describing how to concatenate related fields. The oldest I could find is [this one](https://stackoverflow.com/q/1920552/7296893). However, a good alternate resource is [Allen Browne's ConcatRelated](http://allenbrowne.com/func-concat.html). – Erik A Jan 22 '18 at 14:01
  • Why do you want comma-separated values in one column? In almost every case except presentation, it is never a good idea. This becomes very difficult and inefficient to query such parts and breaks the normalization rule of relational databases. Simply use one-to-many relationship between *Products* and *Parts*. – Parfait Jan 22 '18 at 14:27
  • I do not mind if they are comma-separated or not, all I need is the PartCode from all the parts with matching ProductCode to be included in ListOfParts of the Product with matching ProductCode - I take it that one-to-many relationship is the solution here? – sneakysnek Jan 22 '18 at 14:39

0 Answers0