Database design - reminders for overdue publications

[Scitea]

Hi, this is a bit complicated but I'm hoping someone out there has an idea of
whether it can be done or not.

I am designing a database to track all publications that are received at the
scientific labs where I work. On this database, the following information is
the most vital aspects;

1. Date of publication
2. Date received
3. Was it late (y/n)

The problems that I have are that there are monthly, weekly and daily
publications. I would love to set up a database that automatically adds a
record for the relevant publication without someone having to input the data
on a daily basis (e.g.daily publications) - so that I can set up reminders to
say whether it is late - essentially a self populating database.

But I don't think that it is possible (I can't think how to do it anyway)

Any clues?

Sci x

 

[Allen Browne]

Let's assume you have a table of publications (one record for each kind),
with fields like this:
- PublicationID AutoNumber primary key
- PublicationName Text name of this journal
- FirstReceived Date/Time the first date you will receive this.
- PeriodFreq Number
- PeriodType Text
The last field contains an expression that can be used with DateAdd(), i.e.
yyyy, m, or d. Combined with PeriodFreq, you can now record when the
publication arrives, e.g.:
28 d = every 4 weeks
1 m = monthly
6 m = twice a year
1 yyyy = annually
Since you also know the date when the first copy was due, you can now
calculate when each edition should arrive.

To do that, you need a counting table, i.e. a table with a record for each
number between 0 and (say) 4000 (to cope with the next 11 years of daily
publications.) If you call this table tblCount, and give it one Number field
named CountID, you can use the code in this link to populate it:
http://allenbrowne.com/ser-39.html

Now create a query containing both your Publication table and tblCount, with
no line joining them in the upper pane of query design. This gives you every
possible combination. Type an expression like this into the field row to
calculate each time the publication is due:
DateAdd([PeriodType], [PeriodFreq] * [CountID], [FirstReceived])

Options:
a) Add criteria to limit this to sensible dates.
b) Turn it into an Append query (Append on Query menu) to add the dates to a
table where you can then record the actual dates each issue arrived.

 

[Scitea]

That's absolutely brilliant! I've done exactly what you have suggested and I
have created records for the next few years at least!

I'm hoping that by the time they run out the database / access will be
obsolete

Thanks,

Sci x

Let's assume you have a table of publications (one record for each kind),
with fields like this:
- PublicationID AutoNumber primary key
- PublicationName Text name of this journal
- FirstReceived Date/Time the first date you will receive this.
- PeriodFreq Number
- PeriodType Text
The last field contains an expression that can be used with DateAdd(), i.e.
yyyy, m, or d. Combined with PeriodFreq, you can now record when the
publication arrives, e.g.:
28 d = every 4 weeks
1 m = monthly
6 m = twice a year
1 yyyy = annually
Since you also know the date when the first copy was due, you can now
calculate when each edition should arrive.

To do that, you need a counting table, i.e. a table with a record for each
number between 0 and (say) 4000 (to cope with the next 11 years of daily
publications.) If you call this table tblCount, and give it one Number field
named CountID, you can use the code in this link to populate it:
http://allenbrowne.com/ser-39.html

Now create a query containing both your Publication table and tblCount, with
no line joining them in the upper pane of query design. This gives you every
possible combination. Type an expression like this into the field row to
calculate each time the publication is due:
DateAdd([PeriodType], [PeriodFreq] * [CountID], [FirstReceived])

Options:
a) Add criteria to limit this to sensible dates.
b) Turn it into an Append query (Append on Query menu) to add the dates to a
table where you can then record the actual dates each issue arrived.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, this is a bit complicated but I'm hoping someone out there has an idea
of
whether it can be done or not.

I am designing a database to track all publications that are received at
the
scientific labs where I work. On this database, the following information
is
the most vital aspects;

1. Date of publication
2. Date received
3. Was it late (y/n)

The problems that I have are that there are monthly, weekly and daily
publications. I would love to set up a database that automatically adds a
record for the relevant publication without someone having to input the
data
on a daily basis (e.g.daily publications) - so that I can set up reminders
to
say whether it is late - essentially a self populating database.

But I don't think that it is possible (I can't think how to do it anyway)

Any clues?

Sci x