Delete Query? Macro? VB code?

[FFN]

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!

[orange]

Please describe your "business" in simple, plain English so readers understand what this database is dealing with.
Relationships between tables are based on your business facts/rules.

[FFN]

Edited and added more info for clarity.

[Minty]

There is no point in you saving a PO for a deleted job I assume? You can't reference it back once the Job is deleted.

The usual way to do this is to create a select query that finds one set of records then use that as the IN clause as a sub query.
Something like

DELETE * FROM YourTable WHERE PK_ID IN (SELECT PK_ID FROM YourTable Inner Join Table2 on PK_ID =FK_ID WHERE SomeCriteriaApply)

As an aside unless you are suffering from either storage issues or slow queries due to the number of records, the prefered method is to mark a records as expired , and exclude them from normal day to day operations. This retains the actual data for historic reporting / integrity purposes without impacting your normal reporting.

[orange]

Can you post a jpg of your Relationships window with tables extended to show all fields?

Still trying to understand what tables you have and what relationships exist.

From your post table 2 it seems 1 Job has 1 PO.

[FFN]

So the only relationship for this example is between the field in Table1 called JobNumber, and the field in Table2, called JobNumber.

This relationship was created because I used to Lookup Wizard to pull the JobNumbers in from Table1.

Each JobNumber will only have 1 PONumber associated with it.

And correct, Minty. After a JobNumber has been deleted, I want to delete everything associated with it, in this case a PONumber.

[Minty]

So you actually want a two stage process

1. Delete or expire all the Jobs that meet your criteria.
2. Delete or expire all the PO records that have no matching JobNumber

You can create the two select queries to provide the PK's you need just substitute them into the delete query.
You do this in the query designer simply put under the PK in the criteria

IN( then paste the select query that works in here)

[orange]

??

Each JobNumber will only have 1 PONumber associated with it.

So why do you have a 1 to Many?
It seems you could put the PO info right in the Job (Table1), then you only have to Delete relevant records from that table.
And as was asked earlier by Minty --do you really want to physically delete records, or just mark them as Logically deleted?

[FFN]

They'll be physically deleted after seven years or so from the database to clear them out.

And I'm not sure what you mean by one to many. There are 3 Job#'s and 3 PO#'s. So each job# only has one po#.

And Minty, that makes sense, the two step thing.

So it's definitely looking like I'm going to need to dive into learning marcos/vb for this.

[Minty]

You can save them as two action (Delete) queries, then simply run them from a command button in the correct order.

I still think you should consider just marking them as expired. Only saying... 20+ years experience an all that...

[FFN]

Hah! I appreciate it. I'm thinking about it all. Thank you!

[orange]

And I'm not sure what you mean by one to many.

In your post, the relationship symbol indicates 1 to Many


which suggests that your table set up is not correct for your rule

So each job# only has one po#.

So a single table with fields

JobNumber
PONumber
PODate
Part
JobCompleted

would seem to satisfy your business rules.

To confirm, 1 PO per JOB, and 1 Part per PO? Is that correct?