Multiple Values for one field

[MimiSD]

I am trying to create a database that tracks what reports need to be run on
what days. Some reports need to be run on just one day of the week others
more than once. Is there a way that when the user is entering a new report
to be run they can choose the days that the report needs to be run or will I
need to just have them type in the days.

 

[Allen Browne]

Create a related table to hold the many values that are needed for each
report. This kind of thing:

Table of reports: one record per report.
ReportID unique name for this report. Primary key

Table of report days:
ReportID Relates to one of the records on your reports table.
ReportDay Value between 1 and 7.

In reality, that approach is probably inadquate. If a particular report
should be run every Monday, but Monday was a public holiday, or the user was
sick so it was not run, do you need to run it on Tuesday? I suggest you need
to log when each report is actually run, so you can determines whether it is
due or not.

 

[Nikos Yannacopoulos]

The "proper" reply here would be, this is a 1-to-may relationship (one
report to many days), so use a separate table.
Yet, given (a) you only have 7 days in a week (and that's unlikely to
change any time soon!), and (b) the number of reports is unlikely to
ever exceed three digits (more likely two), it would be acceptable IMHO
to just add seven Yes/No fields in your reports table, without making
any measurable trade-offs in performance or disk space usage. The gain
is a simpler design (one table less) and easier designing of a form for
users to select which days a report should be run on, with just a few
mouse clicks (also very intuitive for the users).

HTH,
Nikos

 

[MimiSD]

Allen I understand where you are coming from when you say to actually log the
day that it is run but this database is just going to be used as a reference.
the actual reports are not actually run off of this database. Right now all
the information is held in an excel worksheet but i am trying to make things
a little easier to use. In the excel sheet there is a column called "Days of
Week" and in there is typed what days the report is run. When I import this
information it has the same thing but I would like to update that so that
when the user is entering a new report they can use a combo box or check
boxes to select exactly what days the report is to be run. Does that make
sense?