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
|
|||
|
|||
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 |
#2
|
|||
|
|||
Count tables and adding records
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" wrote in message ... 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 |
#3
|
|||
|
|||
Count tables and adding records
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Count tables and adding records
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. -- 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 ... 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" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|