Subquery Or VBA Loop through recordset

[Excel_Kid1081]

Hello-

I am trying to create a query from a single table that has these two fields: PricingDate and SecurityID. Each week I get new data that is appended to the table. What I want to find is the New SecurityIDs that were not in last week's. Thus, I am looking to restrict my recordset to just a few new securityIDs.

I have two questions:
1) Am I better off trying to create a OpenRecordset and loop through each securityID or doing just a subquery?
2) Could somebody help get me started on some syntax?

thanks!!

EK

 

[pbaldy]

If the current week's data is in a different table to begin with, you could use the unmatched query wizard to find ID's in that table that aren't in the main table.

 

[Excel_Kid1081]

Thanks for the reply! but unfortunately, it is not in a different table to begin with as I just get the single table with all the data in it vs. having two separate tables.

 

[pbaldy]

I guess you could create 2 queries, one with this week's data and one with previous data, then run the unmatched query wizard against those.

 

[MSAccessRookie]

Hello-

I am trying to create a query from a single table that has these two fields: PricingDate and SecurityID. Each week I get new data that is appended to the table. What I want to find is the New SecurityIDs that were not in last week's. Thus, I am looking to restrict my recordset to just a few new securityIDs.

I have two questions:
1) Am I better off trying to create a OpenRecordset and loop through each securityID or doing just a subquery?
2) Could somebody help get me started on some syntax?

thanks!!

EK

If the IDs have never been in the table before this week, would searching for an ID with a Minumum Pricing Date of this week find what you want?

Select SecurityID, Min(PricingDate)
From YourTable
Group By SecurityID 
Having Min(PricingDate)>= {Variable or value representing "This Week");

 

[pbaldy]

Good idea; that looks like it will work.

 

[Excel_Kid1081]

Thanks everybody! I got it to work.