How to separate duplicates and triplicates in seperate fields

[uberblick]

Hello Access Community,

I have 2 fields, one called DV_ID and FNAME .

None are primary since some DV_ID have multiple FNAMEs.

I'd like to create a table where I have the 1st field called DV_ID, 2nd
field called filename1, 3rd field called filename2, and 4th field called
filename3.

When I have more than 1 FNAME per DV_ID, I'd like to have them inserted into
filename2 and filename3 respectively.

When DV_ID have a single FNAME, I'd like filename2 and filename3 to be null.

Any help will be very appreciated.

Regards

Stephan

 

[Smartin]

Hello Access Community,

I have 2 fields, one called DV_ID and FNAME .

None are primary since some DV_ID have multiple FNAMEs.

I'd like to create a table where I have the 1st field called DV_ID, 2nd
field called filename1, 3rd field called filename2, and 4th field called
filename3.

When I have more than 1 FNAME per DV_ID, I'd like to have them inserted into
filename2 and filename3 respectively.

When DV_ID have a single FNAME, I'd like filename2 and filename3 to be null.

Any help will be very appreciated.

Regards

Stephan

Hello Stephan,

What you propose is not good database design. It breaks a fundamental
proposition of normalization by creating "repeating fields" of information.

Although I do not know what your reasoning is, I suspect you would be
better off if you stored the filenames in a separate table, using a
foreign key to identify the DV_ID they are related to. You can use an
additional field if necessary to order these filenames.

Hope this helps!

 

[uberblick]

Hello Stephan,

What you propose is not good database design. It breaks a fundamental
proposition of normalization by creating "repeating fields" of information.

Although I do not know what your reasoning is, I suspect you would be
better off if you stored the filenames in a separate table, using a
foreign key to identify the DV_ID they are related to. You can use an
additional field if necessary to order these filenames.

Hope this helps!

The table is provided to me like this.

But I have another table where DV_ID are my primary key.

The purpose of that is that I have for some products more than 1 image
available.

I'd like to regroup them per DV_ID in separate field, so I can have more
flexibility for using products with more than 1 image.

Once I have my filenames per DV_ID I can delete duplicates, so I will have
all my filenames on a single line.

 

[Larry Linson]

Perhaps you did not understand what Smartin said... now, your table is
"normalized", and follows relational database design principles. Those
principles are not "arbitrary" but make your life far, far easier in the
long run. If you change it as you suggest, it will be unnormalized and much
more difficult to use in the future.

There is one very simple way to determine if you have duplicate filenames
for the same DV_ID... that is create a Query in the Query Builder pulling
DVD_ID down to the grid once, and FNAME in two columns, then on the Menu,
View | Totals, and under DVD_ID and the first instance of FNAME, choose
Group By, under the second instance of FNAME choose Count. In the criteria
line, under the FNAME / COUNT enter >1. All you will see are those which are
duplicate FNAMES for the same DV_ID.

If you have a lot of records, you could use the results of this Query as
selection Criteria to delete the extra filenames, but that may not be what
you wish to do.

The DV_ID will be your Foreign Key, relating this table back to the other
table where DV_ID is the Primary Key, but in this table, you will have a
compound Primary Key consisting of both DV_ID and FNAME. Trust us, you will
be glad you did later on, when the occasion arises (and do not doubt that it
will) when you need to query to see which DV_IDs have a particular FNAME
associated.

Please, do NOT create the Record format you first asked about.

Larry Linson
Microsoft Access MVP

 

[uberblick]

Perhaps you did not understand what Smartin said... now, your table is
"normalized", and follows relational database design principles. Those
principles are not "arbitrary" but make your life far, far easier in the
long run. If you change it as you suggest, it will be unnormalized and much
more difficult to use in the future.

There is one very simple way to determine if you have duplicate filenames
for the same DV_ID... that is create a Query in the Query Builder pulling
DVD_ID down to the grid once, and FNAME in two columns, then on the Menu,
View | Totals, and under DVD_ID and the first instance of FNAME, choose
Group By, under the second instance of FNAME choose Count. In the criteria
line, under the FNAME / COUNT enter >1. All you will see are those which are
duplicate FNAMES for the same DV_ID.

If you have a lot of records, you could use the results of this Query as
selection Criteria to delete the extra filenames, but that may not be what
you wish to do.

The DV_ID will be your Foreign Key, relating this table back to the other
table where DV_ID is the Primary Key, but in this table, you will have a
compound Primary Key consisting of both DV_ID and FNAME. Trust us, you will
be glad you did later on, when the occasion arises (and do not doubt that it
will) when you need to query to see which DV_IDs have a particular FNAME
associated.

Please, do NOT create the Record format you first asked about.

Larry Linson
Microsoft Access MVP

Thanks Larry for your input.

Let's say I keep my table like this.

I have 2 tables:

1st table = maintable with DVD_ID as Primary Key/Field
2nd table- dcover with DV_ID and FNAME

FNAME field represent the filename of an image on a server.

Some products can have 2,3,4,5,6 images available and this why I want to be
able to use them by regrouping them.

For example, I have 4 differents FNAME for DV_ID "E 26218", I want to be to
regroup them per DV_ID so I can use all of them.

How can I regroup them so I can practically use all the images available for
each product ?

Also, how do I assign a foreign key to a field in ACCESS ?

Thanks in Advance

uberblick