Query-Unmatched Query

[cbende2]

Hello all,

Long time since I've posted here. I'm having trouble wrapping my brain around an "unmatched" query I'm trying to create.

I have the following tables:
TableA
TableB

Fields in TableA:
PlanID
PlanYearEnd

Fields in TableB:
PlanNameID
PYE

More info: PlanID and PlanNameID are both autonumbers, which I'm using to relate the two tables. PlanYearEnd and PYE are both date fields.

My problem: I'm trying to compare and check which records are in TableA but not in TableB. This is simple using the built in unmatched query wizard. However, there is a huge underlying process of appending to TableB from TableA. Whenever the append query runs, I have an unbound form that takes a date(ex. 1/31/Year(Date), 3/31/Year(Date), etc..) to append to the PYE. At the beginning of a new year I need to run the append query, and then I need to run the unmatched query to check if the records were added for current year. The reason I do this is because, the records may exist in prior years, however they may not always be added for the new(current) year, therefor a simple Unmatched query will not work. Thus I need to check if the record doesn't exist for the current year.

Any help?

It's hard to explain, just ask if I need to explain more. I think I will have to explain more unfortunately lol..

Thanks,


cbende2

[ItsMe]

You won't be able to compare the two tables using the PK. You will need to use one or more other columns to compare/JOIN. Determine which column(s), other than the PK, will determine if the record exists.

[cbende2]

Hello ItsMe,

PlanID in TableA is the primary key, however in TableB a different field is primary key(AnnualTrackID). I didn't list it in my given info, maybe I should have.

[ItsMe]

PlanID in TableA is the primary key, however in TableB a different field is primary key(AnnualTrackID). I didn't list it in my given info, maybe I should have.

OK, I was not aware of the actual names. However, I imagined they were named differently. Because both are Autonumbers, you won't be able to use them as a comparison. that is the point I was trying to make. Don't compare the two PK's from the two tables.

So, which columns can you use? If you had some success using the wizard, post that SQL here and explain its shortcomings. I think the whole thing is as easy as identifying the column(s) that will be compared.

[cbende2]

Ok.

This database is old and I didn't know anything at the time, and I know there are problems in the naming conventions, save yourself some time and don't worry about explaining it

TableA = [Plan Table]
TableB = AnnualTrackingTable

SELECT [Plan Table].PlanNameID, [Plan Table].[Plan Year End], [Plan Table].TradingPartnerID, [Plan Table].StatFreqID, [Plan Table].[First Val Dates], [Plan Table].PlanStatusID, [Plan Table].[Term date], [Plan Table].PlanName, AnnualTrackingTable.PlanYearEndFROM [Plan Table] LEFT JOIN AnnualTrackingTable ON [Plan Table].[PlanNameID] = AnnualTrackingTable.[PlanNID]
WHERE ((([Plan Table].PlanStatusID)=1 Or ([Plan Table].PlanStatusID)=2) AND ((AnnualTrackingTable.PlanNID) Is Null))
ORDER BY [Plan Table].PlanName;

So the problem is that AnnualTrackingTable may have the record for 2014, however, not for 2015. How would I check based on "year-1"?

[cbende2]

I need some sort of "OR" statement that is like, It may exist in both tables, however, it needs to be added for a new year. the 2014 record may exist, however now I need to add the record for 2015.

[ItsMe]

Post #6 is not helping me understand at all, really. What I understand is that you need to compare two tables. That seems simple enough. It also appears that there is another factor that needs to be considered. I am not seeing or understanding what this other factor is. Before I can consider the other factor, however. I will need to understand how you are comparing the two tables in the first place.

How many columns are in each of the two tables, two? If this is the case, you can only compare on the date column. And that will be the end of your adventure. No more business rules will be able to be applied.

[ItsMe]

I just now realized that there was a post #5. So, you can disregard my post #7, for the most part. I was totally confused and post #5 is the answer to my previous question.

What would happen if you use the Wizard to create your query and, instead of comparing the PK's, you compared the following ...
TradingPartnerID or StatFreqID

After the wizard runs, you can adjust the SQL to include both the fields as a comparison. Do these fields exist in AnnualTrackingTable?

[cbende2]

Yes they exist in AnnualTrackingTable.

I'm not sure how comparing another field allows me to check for a date?

The problem is that, the record (PlanID) will eventually exist in both tables, [Plan Table] and AnnualTrackingTable, however a new record with the same PlanID may not exist with the new Date(Year) in AnnualTrackingTable

[ItsMe]

I was only guessing about the other two fields. As for comparing the two Autonumbers, as indicated in your SQL Statement, that does not make any sense to me. If anything, you would compare a PK to an FK. Often times, in unmatched queries, you will want to use additional columns (like a date field, etc.).