Count tables and adding records

[Scitea]

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

 

[Allen Browne]

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;

 

[Scitea]

Ok, I get it all apart from the PublicationID=99

What I have set up is a form to create the new record, then that leads to
another form to run the append query. The PublicationID is an autonumber, so
how do I enter it into the subquery so that it will change everytime the
records are updated without physically having to change the number?

This is all getting rather complicated! Thanks so much for your help!

Sci x

 

[Allen Browne]

When you enter a new publication into your form, Access assigns a new
PublicationID (autonumber.) I assume that you use the AfterInsert event
procedure of this form to insert the new records into the PubDue table.

Alternatively, if you are using a query drawing existing records from the
Publication table, and ensuring that each one has the desired records in the
PubDue table, then you already have the PublicationID in the main query. The
subquery can refer to that:
WHERE Dupe.PublicationID = Publication.PublicationID
or whatever the tables/fields are called.