Hi all.
Here are some example tables for visualization of what I want to happen:
Table1
JobNumber Part Completed 1 part1 1/1/2008 2 part2 1/1/2009 3 part3 1/1/2008
Table2
PONumber JobNumber PoDate 1 1 2/1/2008 2 2 2/1/2009 3 3 2/1/2008
Edit for clarity: Basically, we build parts for ships and the like, and we need a more organized way to keep track of Job#'s and Purchase Order#'s. We have to keep records on file for 7 years before we can dispose of them. So, what I'm trying to do here is find a way for Access to look at the dates of the Job#'s, see if they're old enough to delete, and then delete them and everything to do with that particular Job# wherever it may be referenced (will be cross-tables and multiple fields).
Now, these tables are linked through the JobNumber field so that I can pull those in to to tie them to certain Purchase Order Numbers (PONumber).
What I'm wanting to do (but cant' figure out how to make happen), is to have access look at dates and have it delete anything older than (for this example) 2009. So all that should be left is Job#2 and PO#2.
I've tried a delete query, and the problem I'm finding with that is that it doesn't seem to be able to delete across multiple tables at once. Only one at a time. When I try to add more than one table I get a message stating "could not delete from specified tables." I also get this message even if there is one table present in the delete query, but the tables are joined through the JobNumber field. Unlinking or destroying that join resolves the issue, but again, at that point it's only deleting records from Table1.
So how can I do this without having to go in and make multiple queries that effectively do the same thing? I started reading about macros and vb code and I'm thinking that may be the way to go. Again, I'm wanting this to delete everything involved with specific job#'s after certain dates. It's quite a lot, but I feel like access can do it, just not sure how.
Thanks in advance!