Query returns nothing

[KTuohy]

Hi, kinda new to Access and I've hit a wall I'm hoping someone can help me with.

Two tables: DOM Repairs and FA_DOM_Repairs. I want FA_DOM_Repairs to update it's fields with only those from DOM Repairs which aren't new, ID is primary in both. I first have created a query for LastOfID from FA_DOM_Repairs, because DOM_Repairs is huge (million plus lines) I don't want to do an actual match between the two tables.

In the next query which EX:
SELECT DOM_REPAIRS.ID
FROM DOM_REPAIRS LEFT JOIN [qry_Last FA DOM Repair] ON DOM_REPAIRS.ID = [qry_Last FA DOM Repair].LastOfID
WHERE (((DOM_REPAIRS.ID)>[LastOfID]));

I keep getting nothing. It'll return one record if I say >= the one record of LastOfID will show up but nothing greater than that ID.

What am I doing wrong and how do I fix it?

Thanks

 

[The_Doc_Man]

I want FA_DOM_Repairs to update it's fields with only those from DOM Repairs which aren't new

So then the question is, how will Access know it isn't new? (That isn't meant to be flippant.) See discussion below after I posit another question.

because DOM_Repairs is huge (million plus lines) I don't want to do an actual match between the two tables.

Understandable, but the JOIN is going to want to do exactly that.

Here's a couple of points to ponder. The old programmer says "Access won't tell you anything you didn't tell it first." So if you want Access to know which records are not new, how will Access know that? Define the answer to the question and you won't have to ask us because YOU will know.

If you don't want to do a true match on a million-record table, there had better be an index somewhere so that you can join based on indexes (indices?) rather than a relation-scan.

The JOIN language is possibly getting in the way. I am inferring your goal a little here, but if you are looking to find the records in one table that DO NOT appear in the other, look at the query wizard's "unmatched" queries for examples of how to do that.

 

[KTuohy]

Thanks, Doc_Man for responding.

Both tables have indexed ID for a field. DOM_Repair's field for ID is autonumber, FA_DOM_Repair's field for ID is not (it's going to mirror DOM_Repair autonumber). Because DOM_Repair will reach such proportions by the end of the month and we want only selected fields from it that pertain to my division, we want a slice, not even half of the pie.

I started by finding the last ID in FA_DOM_Repair by grouping it under ID:
SELECT Last(FA_DOM_Repair.ID) AS LastOfID
FROM FA_DOM_Repair; the result was ID number 6165386
This gets me the last numeric ID for FA_DOM_Repair. THEN I did the unmatched query with DOM_Repair, but the results gave me even previous records of 6165386 which were of course already in FA_DOM_Repair. So under Criteria I said I wanted only DOM_Repair IDs that were greater than the LastOfID from FA_DOM_Repair. THAT'S when I get nothing in return.

It's frustrating because it looks logical to me! Does it to you?

Shouldn't we be able to do that? Whoosh! I just expelled all my breath.

Thanks again, I really appreciate it.

 

[The_Doc_Man]

Look at using a construct like this:

SELECT {mumble} FROM TABLEA WHERE TABLEA.ID > DMAX( "ID", "TABLEB" ) ;

If there is really an index on both tables, the DMAX function should be very fast. BUT it avoids the JOIN language, which has very specific meaning to Access when there is a defined relationship between two tables.