Count tables and adding records

Ok,

I have created a count-table to create records for journals that are
received where I work (Approx 42 different journals each month). The aim of
the database it to keep a record of when journals are due and what date they
actually arrive - mainly to help when it comes to renewing the subscription
so a report can be printed of the year, with a column for "overdue by (days)"

I have sorted the count table out, and using an Append query I have created
1500 'due date' records for each publication - this will allow reminders to
keep popping up for a few years to come.

The problem is this;

I have added another journal to the list and run the append query again, but
rather than creating the additional records that I wanted, it has duplicated
the current records, effectively doubling the data.

Is there a way to add another journal and append the results for just that
journal, not the whole fiippin lot?

Also, if we were to stop subscribing to a journal, is it possible to quickly
delete the records created for that journal in the table?

I hope so because this is all getting rather too complicated for me to handle!

Sci x

To prevent duplicating the records, use a subquery in the WHERE clause of
your Append query statement.

INSERT INTO PubDue (...
SELECT ...
WHERE NOT EXISTS
(SELECT PublicationID FROM PubDue AS Dupe
WHERE (Dupe.PublicationID = 99) AND (Dupe.DueDate = #1/1/2008));

Hopefully you have enough SQL experience to match up the subquery to the
main record, replacing the literal place holders (99 and date) in the
example.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Regarding deletion, this might be a good candidate for cascading a delete.
In the Relationships window, you probably created a relation between the
Publication table and the PubDue table. If you double-click this line and
check the boxes for Referential Integrity and for Cascading Delete, it will
automatically delete all PubDue records for that publication when you delete
it from the Publication table.

If you don't want to use cascading deletes, execute:
DELETE FROM PubDue WHERE PublicationID = 99;

--
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" <Scitea.RemoveThis@discussions.microsoft.com> wrote in message
news:8117198C-2244-4F9D-B074-C9D66C7DA2E7@microsoft.com...
> Ok,
>
> I have created a count-table to create records for journals that are
> received where I work (Approx 42 different journals each month). The aim
> of
> the database it to keep a record of when journals are due and what date
> they
> actually arrive - mainly to help when it comes to renewing the
> subscription
> so a report can be printed of the year, with a column for "overdue by
> (days)"
>
> I have sorted the count table out, and using an Append query I have
> created
> 1500 'due date' records for each publication - this will allow reminders
> to
> keep popping up for a few years to come.
>
> The problem is this;
>
> I have added another journal to the list and run the append query again,
> but
> rather than creating the additional records that I wanted, it has
> duplicated
> the current records, effectively doubling the data.
>
> Is there a way to add another journal and append the results for just that
> journal, not the whole fiippin lot?
>
> Also, if we were to stop subscribing to a journal, is it possible to
> quickly
> delete the records created for that journal in the table?
>
> I hope so because this is all getting rather too complicated for me to
> handle!
>
> Sci x