Re: Combination of fields must be unique

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
"Craig Alexander Morrison" <reply@xxxxxxxxxxxxxx> wrote in message
news:u7k85YchFHA.2840@xxxxxxxxxxxxxxxxxxxxxxx
>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
> "Seth" <Seth@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:DA3384E4-DE03-469B-9DDB-021398C8655D@xxxxxxxxxxxxxxxx
>> 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
>>
>>
>> "Jeff Boyce" wrote:
>>
>>> 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>
>>>
>>>
>
>


.

Relevant Pages

  • Re: Combination of fields must be unique
    ... assigned as the primary key. ... To relate the Report to the Photo you would need another field that would be ... Table: PDF Files ... PDF Filename Related Make this No Duplicates also ...
    (microsoft.public.access.tablesdbdesign)
  • Re: why>?
    ... Just because PDF is more popular than SNP; ... Emailing a dynamic report is easier than emailing PDF files.. ... Excel is ALWAYS less efficient than Access. ... e) multi-user functionality - being able to work on the same report / ...
    (microsoft.public.excel)
  • Re: Serviced Component : Architectural revival
    ... Q. Did you tried to wrote test of generating pdf by means of any other tools, ... Our client have Crystal Report license and they export it in Excel ... Placement of Dataacess layer to other machine. ... Can serviced components help us out under such circumstances? ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Combination of fields must be unique
    ... > If you have a table detailing the Report (with a Primary Key of Report ... > Table: PDF Files ... > Craig Alexander Morrison ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Create macro which automaticallly creates PDF of a report
    ... It will automatically convert a report into a pdf file. ... when the macro is executing. ...
    (microsoft.public.access.reports)