Multivalued Field In Access 2016 and query to get the results

[madiso10]

It have part numbers and multiple values for these part numbers. I want to import to access, create a table for this and then create a query based on that table. Part number is the primary key. When I create the query on the table I would like it to come out like this:



Part number | Product File name | Product Filename | Product Filename

These should be separate fields within the query.

I am using Access 2016.

I have attached an example of the excel spreadsheet.

Some part numbers have 1-3 filenames associated with it.

This is an example of the excel file I have:

partNumber	Images filename
TNN608679	9ccc7f3188014fd185049edcdcfc3759.1.png
TNN608679	9ccc7f3188014fd185049edcdcfc3759.2.png
TNN608679	9ccc7f3188014fd185049edcdcfc3759.3.png
ATSZ-1401A	38cba2e6d3244a088477e31c1499a9ff.1.png
ATSZ-1401A	38cba2e6d3244a088477e31c1499a9ff.2.png
EAGN509-0239	38cbc66a92de4db687c475ad784d7257.1.png
EAGN509-0239	38cbc66a92de4db687c475ad784d7257.2.png
AMT121116-13	39da43a85f46400fbea3e339e866420b.1.png
AMT121116-13	39da43a85f46400fbea3e339e866420b.2.png
AML61679A	41423806be434b3bb34299a6cffcbb94.1.png
AML61679A	41423806be434b3bb34299a6cffcbb94.2.png
AML61679A	41423806be434b3bb34299a6cffcbb94.3.png
CKE56314538	83f3352df2434409bbc33d5ab6e687d7.1.png
CKE56314538	83f3352df2434409bbc33d5ab6e687d7.2.png
CKE56314538	83f3352df2434409bbc33d5ab6e687d7.3.png
TNN222384TG	3ca11ebf6b80491cb282de2928f96b72.1.png
TNN222384TG	3ca11ebf6b80491cb282de2928f96b72.2.png
ADV56413761	38fd9962132d465da8cbcb317c1e7a1e.1.png
ADV56413761	38fd9962132d465da8cbcb317c1e7a1e.2.png
ADV56413762	dd376f13340f475599b1b3631bfb40c4.1.png
ADV56413762	dd376f13340f475599b1b3631bfb40c4.2.png
ADV56413766	d56d401af00f48c99afe02b02add4623.1.png
ADV56413766	d56d401af00f48c99afe02b02add4623.2.png

Thank you in advance,

Lee Harris

[ranman256]

this data would import directly into the tImages table. (both columns)
you would also have a tParts master table. (import only the Part# into tParts)

[Micron]

Your post title of "Multivalued field" is misleading. You don't have that, you have single values in each field, some repeated because there are multiple files for a partNumber. A multi-value field looks like this (the separators don't have to be commas).
1,2,3,4
5,6,7
0,8,2,5
Yes you can have partNumber as a PK field if you do as suggested, BUT why do you want the values arranged in columns? That is a spreadsheet approach. Access is not a spreadsheet application; db data is meant to be displayed in rows when multiple records are involved. Unless of course, you are performing aggregate functions over groupings.

You can try a Crosstab query when you get your data normalized as suggested, but you will likely encounter issues with respect to trying to control the number of columns that are output. If the current data set limits the number of filenames to 2, that is all the columns you'll get - not the 3 or 4 you had the previous time.

[madiso10]

I'm going to take the query and export back into excel to load up to my website. It gives me an option for 3 image files, but I need them in order to get them into the spreadsheet.

[madiso10]

I did this, created a relationship between the 2 tables. One containing only the part number and the other containing the part number and filenames. How would I set up the query to do this into seperate columns with each part number having 3 separate part numbers for each filename?

[Micron]

If you're starting in xl and ending in xl, why not just stay there? I'm no xl expert, but perhaps you can use a pivot table. Is there too much data to simply drag and drop spreadsheet data from rows into columns? My guess is that to Paste Special > Transpose is no good either (you would get a partnumber/value column for each pairing). Thing is, you've shown what you have but not what you want.

with each part number having 3 separate part numbers for each filename?

I have no idea on what happens when there are not 3 values, but 1 or 4 or 5. This was mentioned, but not commented on by you. Nor do I know what you'd call these fields since they have to have names.
BTW, a crosstab query needs 3 or more fields to work with - you appear to only have 2 so that doesn't appear to be an option. Sorry for the misleading suggestion.
A code solution might be your only option, but I've thought that before in this forum and others come up with wonderful query solutions. Maybe one of those gurus will chime in.