Multiple dates design question

[MMC]

I am designing a database to track grants, both received and to be applied
for. One important thing to track is when grant reports are due. I would
like to be able to print a report listing all of the reports due in the
upcoming month or quarter. Some grants have one report due, others have
several reports. At first I set up separate date fields for report1,
report2, report3, report4, but then I cannot neatly print a report listing
all upcoming reports, like this:

Staff person
Reports due July
Funder date due type of report (progress, final, etc.)

Any suggestions on how to do this? I am not a database designer.

Thanks very much for your thoughts.

MMC

 

[Duane Hookom]

You didn't provide anything about your table design. Do you have "task
names" as field names? Or, is your table structure normalized so that each
task associated with a grant creates a new record in a related table?

 

[John Vinson]

I am designing a database to track grants, both received and to be applied
for. One important thing to track is when grant reports are due. I would
like to be able to print a report listing all of the reports due in the
upcoming month or quarter. Some grants have one report due, others have
several reports. At first I set up separate date fields for report1,
report2, report3, report4, but then I cannot neatly print a report listing
all upcoming reports, like this:

Staff person
Reports due July
Funder date due type of report (progress, final, etc.)

Any suggestions on how to do this? I am not a database designer.

Thanks very much for your thoughts.

MMC

You've made a very common mistake in setting up your table structure.
It's jocularly called "committing spreadsheet upon a database" - using
multiple fields within a record, when you should be using *two tables*
in a one to many relationship.

Your task would be much easier if you had a DueDates table with a
ReportID link to the Grants table, and one record for each report due
date.

John W. Vinson[MVP]

 

[MMC]

Duane,

So far, I have two related tables: one with basic foundation information
(name, address, and so on), and one with information on a specific
application (an application id, foundation name, date submitted, amount
requested, amount received, grant project title, program, staff member
responsible, and so on). Where I'm stuck is the things with multiple
dates--foundation deadlines for applying and report due dates. I don't know
if there is a way to include those in the same table or whether that
information should link to another table, and what that other table should
look like.

Thanks for the prompt reply!

MMC

 

[MMC]

Okay, makes sense. Does each report date have its own unique id? That seems
like a data entry hassle. For example, if I get a grant from an agency
called RMA that requires quarterly reports, I have a report id that's
something like: RMA2006Grant1Report1. Then my table would have theses
fields:

report id
due date
report type
completed?

What does the reportid field look like in my grants table if each grant has
multiple reports?

Thank you!

MMC

 

[Duane Hookom]

I think you should be using FoundationID to be joining the foundation table
with the application table. The application table should have an
ApplicationID field as the primary key. Then create an ApplicationDates
table

tblDateTypes
==================
DateTypeID primary key
DateType (deadline type title)

tblApplicationDates
======================
ApplDateID
ApplicationID links to tblApplications.ApplicationID
DateTypeID links to tblDateTypes.DateTypeID
DueDate
ApplComments

 

[John Vinson]

Okay, makes sense. Does each report date have its own unique id? That seems
like a data entry hassle. For example, if I get a grant from an agency
called RMA that requires quarterly reports, I have a report id that's
something like: RMA2006Grant1Report1. Then my table would have theses
fields:

report id
due date
report type
completed?

What does the reportid field look like in my grants table if each grant has
multiple reports?

If you use an Autonumber ReportID, the user doesn't need to even SEE
it, much less enter it. There is certainly *no* good reason to store
all of the information about the report in one redundant, composite
field!

I'd say the Reports table should have fields as you suggest; if there
is only one instance of each report type for each grant, then you
could ctrl-click the ReportID and ReportType fields, and click the Key
icon to make them a joint, two-field Primary Key.

John W. Vinson[MVP]