If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database design - reminders for overdue publications
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 |
#2
|
|||
|
|||
Database design - reminders for overdue publications
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Scitea" wrote in message ... 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 |
#3
|
|||
|
|||
Database design - reminders for overdue publications
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Scitea" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|