Unmatched query based on two criteria (1 Viewer)

[lscheer]

I have an unmatched query that I want to use to find all the records that don't match (obviously) the ID fields, but I also want to set additional criteria on the ID field so that it doesn't pull archived data out of the first table.

So basically, T1 has the full set of data, old and new. T2 has a subset of the data that I work with and append data to it from T1 whenever T1 is updated, but I have removed old data from T2 that I want to stay out of it. However, when I run the unmatched query, it obviously pulls both old (archived) and new data up in the results. I want to limit the unmatched results from T1 to start with the last record in T2 (ID numbers are sequential).

Any ideas?

 

[HiTechCoach]

Create a query that limits to just the records you want. Use this as one of the record sources in the unmatched query.

 

[lscheer]

I was hoping to be able to achieve this at least somewhat automatically; so that the db users won't have to be trying to figure out which records to append and not to append on the fly...

 

[HiTechCoach]

I was hoping to be able to achieve this at least somewhat automatically; so that the db users won't have to be trying to figure out which records to append and not to append on the fly...

You should be able to do it "somewhat automatically", if not completely.

I have my users only work with forms and reports. They probably have never even see the database window or a query.

Note: An unmatched query can be between to queries, not just two tables.

 

[jardiamj]

Hello lscheer. How HiTechCoach suggested you can create a query that limits your data to the ones you want. You wrote that you want the Unmatched results to start with the last record in T2. So this query should look like this:

SELECT *
FROM T1
WHERE ((T1.ID)>(SELECT MAX(ID) FROM T2));

And then you can run your unmatched query against this query instead of doing so against T1.
You can always do this using VBA. If you want so, you can use DMAX() function to find the last ID in T2 and use it as the criteria for the query you will run against T2. I hope it makes sense... lol.
I hope this will get you going. Cheers!

 

[lscheer]

jardiamj - Thanks, this works perfectly! It is run via a form as part of an append query and does exactly what I want it to. Thanks again!

 

[jardiamj]

Happy to help! I'm not gonna have so much access to the internet during one month or so.
So, I'm not gonna be around because of that, but as soon as I get to the internet again. I will continue in the forum.

Jardi.