Combination of fields must be unique

[Guest]

I have two fields in a table. Neither is required. I want each field to be
unique as well as the combination of both fields.

I have tried doing this with the index table:

Field1: unique
Field2: unique
Field1AndField2: unique

But I am still able to enter the same value in both Field1 and Field2

Thanks for any help,
Seth

 

[Rick Brandt]

I have two fields in a table. Neither is required. I want each field
to be unique as well as the combination of both fields.

I have tried doing this with the index table:

Field1: unique
Field2: unique
Field1AndField2: unique

But I am still able to enter the same value in both Field1 and Field2

Thanks for any help,
Seth

A unique index on two fields does not prevent you from enter the same value in
those fields in the same record. It just prevents more than one record from
having the same combination of entries. What you would neeed is a table level
ValidationRule that says...

Field1 <> Field2

 

[Jeff Boyce]

Seth

I'm not clear on your requirements...

You want Field1 to be "unique", but no value must be entered. So, if in a
couple rows you had no value entered in Field1, you'd have two rows with
Nulls in Field1 -- I don't believe these are unique.

Ditto for Field2.

And your comment:

But I am still able to enter the same value in both Field1 and Field2

seems to imply that you also want to ensure that whatever is entered in
Field1 is never entered in Field2 for the same record.

Perhaps if you explained a bit more about what you hope to accomplish with
this arrangement, rather than how you are trying to do (something
undefined)...?

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff, I hope this explains it better:

I am developing a database to keep track of test reports.

All test reports are available as a hard copy.
Some of these test reports are available as PDF.
If a report is available as a PDF there could be two files Report and Photo
attachment.

I can’t set the fields as required, as there is not always a PDF report. And
if there is a PDF report there is not always a photo attachment.

In my table I have two fields PdfReport and PdfPhotos.
The user enters the path and file name using an open file dialogue box.

I want to ensure that any file is only entered into the database only once,
wether it is into PdfReport or PdfPhotos.

Thanks for your help,
Seth

 

[tina]

you're storing data in field names - PdfReport and PdfPhotos. that's a
common mistake that violates data normalization rules. a single report may
have multiple hardcopy files, but each pdf file belongs to only a single
report: that's a standard one-to-many relationship between reports and
files. you need to remove the two Pdf fields from your table, and create
another table, as

tblReportFiles
ReportID (foreign key from table Reports)
FileType (PdfReport, PdfPhotos, any other applicable report type)
FileName (or Filepath, perhaps; whatever name is descriptive of what you're
storing)

so if a report has two files associated with it, there will be one record in
the Reports table, and two associated records in tblReportFiles, linked by
the ReportID (that's whatever field you're using as the primary key of your
Reports table). note: in tblReportFiles, you can use fields ReportID and
FileType (or FileName) as a combination primary key, to prevent the same
file from being entered twice in the table.

hth

 

[Craig Alexander Morrison]

I think you should consider redefining your table, expecially as it appears
you have no primary key.

Another approach would be to have a field for the filename of the PDF file
and a marker indicating Report or Photo.

This would ensure that each record would have a filename and this could be
assigned as the primary key.

To relate the Report to the Photo you would need another field that would be
the foreign key reference to another record in the same table.

The table may look like this:

Table: PDF Files
Field: PDF Filename (Primary Key)
Field: PDF Filetype (Report or Photo) probably best as a Yes/No field
Field: PDF Filename Related (Foreign Key) Make this No Duplicates also

You should also define a Table Validation rule to ensure that PDF Filename
and PDF Filename Related cannot be the same. [PDF Filename]<>[PDF Filename
Related]

Create a 1 to 1 relationship between the PDF Filename and the PDF Filename
Related fields in the same table. You will need to add two copies of the
same table to the relationships window.

You will need to use the marker field to ensure that you recording or
viewing a particular type of PDF file, i.e. Report or Photo.

The current form you have will need to have 2 records one for the Report and
one for the Photo filename, the Report/Photo marker needs to be set to Yes
for one and No for the other.

 

[Craig Alexander Morrison]

Amendment:

One of the problems of email db design is that the problem changes when more
info is supplied.

If you have a table detailing the Report (with a Primary Key of Report Name)
irrespective of the PDF Files then the following table will work:

Table: PDF Files
Field: PDF Filename (Primary Key)
Field: PDF Filetype (Report or Photo) probably best as a Yes/No field
Field: Report Name (Foreign Key to Report)

No need for table validation although you may wish to create a unique index
of the PDF Filename and PDF Filetype if you can only have one of each type
of PDF file for each Report.

Try to avoid pointless ID numbers if you have a perfectly good candidate for
Primary Key.

 

[Craig Alexander Morrison]

Amendment: If you have a table with more fields than mentioned in your first
post consider:

A new table that may look like this:

Table: Report PDF File
Field: PDF Filename (Primary Key) also define a Unique index on this field
on its own to ensure that you cannot have the same filename linked to a
different report.
Field: Report Name (Primary Key) Note: The Primary Key is a combination of
this and the Filename as the records on this table are dependent on the
Report table.
Field: PDF Filetype (Report or Photo) probably best as a Yes/No field

This table would be related to the Report table with a Primary Key of Report
Name.

Alternatively it could look like this:

Table: PDF Files
Field: PDF Filename (Primary Key)
Field: PDF Filetype (Report or Photo) probably best as a Yes/No field
Field: Report Name (Foreign Key) Make this a Required Field

This table would be related to the Report table with a Primary Key of Report
Name.

If the Report can only have one Report PDF and one Photo PDF consider a
unique index on the combination of PDF Filetype and Report Name. The
uniqueness of the PDF Filename is taken care of by being the sole field in
the Primary Key.

Avoid the use of IDs and Codes if you have a perfectly good candidate for
Primary Key.

DB Design by email is dangerous take all the suggestions with a pinch of
salt and see which works best for your actual problem, which may not be
exactly what I or others have assumed.

--
Slainte

Craig Alexander Morrison

I think you should consider redefining your table, expecially as it appears
you have no primary key.

Another approach would be to have a field for the filename of the PDF file
and a marker indicating Report or Photo.

This would ensure that each record would have a filename and this could be
assigned as the primary key.

To relate the Report to the Photo you would need another field that would
be the foreign key reference to another record in the same table.

The table may look like this:

Table: PDF Files
Field: PDF Filename (Primary Key)
Field: PDF Filetype (Report or Photo) probably best as a Yes/No field
Field: PDF Filename Related (Foreign Key) Make this No Duplicates also

You should also define a Table Validation rule to ensure that PDF Filename
and PDF Filename Related cannot be the same. [PDF Filename]<>[PDF Filename
Related]

Create a 1 to 1 relationship between the PDF Filename and the PDF Filename
Related fields in the same table. You will need to add two copies of the
same table to the relationships window.

You will need to use the marker field to ensure that you recording or
viewing a particular type of PDF file, i.e. Report or Photo.

The current form you have will need to have 2 records one for the Report
and one for the Photo filename, the Report/Photo marker needs to be set to
Yes for one and No for the other.

--
Slainte

Craig Alexander Morrison

Jeff, I hope this explains it better:

I am developing a database to keep track of test reports.

All test reports are available as a hard copy.
Some of these test reports are available as PDF.
If a report is available as a PDF there could be two files Report and
Photo
attachment.

I can't set the fields as required, as there is not always a PDF report.
And
if there is a PDF report there is not always a photo attachment.

In my table I have two fields PdfReport and PdfPhotos.
The user enters the path and file name using an open file dialogue box.

I want to ensure that any file is only entered into the database only
once,
wether it is into PdfReport or PdfPhotos.

Thanks for your help,
Seth

 

[Craig Alexander Morrison]

Did not realise I had sent this one, the 5:12 version is more considered.
Ooops.

 

[Guest]

Thanks Tina! I'll create a second table.
Seth

you're storing data in field names - PdfReport and PdfPhotos. that's a
common mistake that violates data normalization rules. a single report may
have multiple hardcopy files, but each pdf file belongs to only a single
report: that's a standard one-to-many relationship between reports and
files. you need to remove the two Pdf fields from your table, and create
another table, as

tblReportFiles
ReportID (foreign key from table Reports)
FileType (PdfReport, PdfPhotos, any other applicable report type)
FileName (or Filepath, perhaps; whatever name is descriptive of what you're
storing)

so if a report has two files associated with it, there will be one record in
the Reports table, and two associated records in tblReportFiles, linked by
the ReportID (that's whatever field you're using as the primary key of your
Reports table). note: in tblReportFiles, you can use fields ReportID and
FileType (or FileName) as a combination primary key, to prevent the same
file from being entered twice in the table.

hth

 

[tina]

you're welcome.

Thanks Tina! I'll create a second table.
Seth

 

[Craig Alexander Morrison]

<...to prevent the same file from being entered twice in the table.>

A unique index on the Filename is required to ensure that the Filename is
unique in the table as opposed to unique within Report, if that is in fact
the requirment.

 

[tina]

yes, that's correct.

<...to prevent the same file from being entered twice in the table.>

A unique index on the Filename is required to ensure that the Filename is
unique in the table as opposed to unique within Report, if that is in fact
the requirment.

 

[Craig Alexander Morrison]

I know, that's why I wrote it. (g)