Help needed with Query...

[showmak]

Hi there, this is my first post in this forum and hope to get the required information.

I am a mechanical designer and we have a database in the company to populate all the drawings generated by the drawing seciton, this database keeps updating regularly every week. Simply the database has 4 columns, Doc No, Doc Title, Doc Revision and Revision Date.

Now, I have two tables, one is the for the last week and one is up to date, I would like to set qurey to look for the changed revisions of the drawing list. The only things that are changing in every new list are the Doc Rev and the Rev date, the Doc No and the Doc Title will be kept unchanged.

Example, here are two tables, you can see from the table Week 23 that some document revisions and dates were changed and 1 new document issued "In Bold". Imagine you have thousand of records, how difficult will be to tack the changes. It will be very useful to set a query to find the updated documents.

Documents as of Week 22


Doc No---Doc Title---Rev---Rev Date
*********************************
BRA-123460---Detail Drawing---1---1/9/2010
BRA-123461---Detail Drawing---1---1/9/2010
BRA-123462---Detail Drawing---1---1/9/2010
BRA-123463---Detail Drawing---2---2/9/2010
BRA-123464---Detail Drawing---3---2/9/2010
BRA-123465---Detail Drawing---1---3/9/2010

Documents as of Week 23
Doc No---Doc Title---Rev---Rev Date
*********************************
BRA-123460---Detail Drawing---0---5/9/2010
BRA-123461---Detail Drawing---0---5/9/2010
BRA-123462---Detail Drawing---1---1/9/2010
BRA-123463---Detail Drawing---3---4/9/2010
BRA-123464---Detail Drawing---3---2/9/2010
BRA-123465---Detail Drawing---1---3/9/2010
BRA-123466---Detail Drawing---0---5/9/2010 (New Document)

The query shall show the records with updated revision and the new document as well, and to exclude records with equal data in both the tables.

Could you please guide me step by step, or give me a formula to set the required qurey for the above case.

[dynamictiger]

If you want to find just drawings altered/added whatever in the last week just make a query using >Date()-7 as your date criteria.

If you want to find all the latest drawings, I would be more inclined to use revision number and simply tell the query to show the Max of revision number.

[Desstro]

Do you have to have 2 tables?

The reason I ask is if you had one table with Doc# and Rev set as primary keys you should be able to create a query where you could set the criteria on RevDate to ">Date()-7"
and the or: to =date().

or something like that. I'm still pretty much a novice at database's myself but it's worth a try. I have a similar query in my database that shows only work that is scheduled for the current system date and this is how I achieved this.

Anyway I think this might steer you in the right direction. If not I'm sure someone on here will have a solution for you.

[weekend00]

It is quite easy a query to find the changed records:
tablename: week22 and week23

select week23.* from week23 inner join week22 on week23.[doc no] = week22.[doc no] where week23.[rev date]<> week22.[rev date]

[oldman]

If I understand your question correctly, you want to find two separate things with a single query. You want to know what is new this week and what has changed this week.
Try this.
First copy the two tables into "this week" and "last week" tables. That will allow you to standardize any queries based on the data.
Search only the this week table and ignore the previous one by using the DATE() function. All you really care about is the change in the last 7 days so a new drawing is one with a revision of zero and a date that meets the criteria of "> DATE()-7". You can refine things a bit finer by using the NOW() function if your revision information includes time but that would be a bit unusual. A revision meets the same date criterion but has a revision number <> 0.
If you do the query in two steps, you can have a first query that tells you all changes in the last 7 days and a second query, based on the results of the first, that lists either Rev 0 (new) or Rev >0 (revised) as the result. If you want a simple report listing first revisions and then new drawings, make a couple of sub-reports that each have their own query. One retrieving the Rev 0 material and the other retrieving the Rev >0 material.
The only thing missing with this approach would be a drawing created on Monday and revised on Friday. The Monday record would not exist in the present week as a Rev 0 so it would only be counted as a Friday revision, not an original. You can find those few examples by using a "find unmatched" query where you left join the "this week" drawing list with the "last week" drawing list and taking only those records where the drawing number is null for the "last week" and the revision number <> 0. Unless very frequent revisions to new drawings are common in your environment, that last query is really not needed.