Thread: Query table to compare last mo to current mo

Ok, I'm struggling with this one, still not getting all the results I need...

Looking back at AOG comment, AOG you mean an autonumber on my table with all my months data? If so, is it too late to add one?

What happens is .... each month I get a new load of data (there will be new EQ#'s and EQ#'s that disappear from month to month). There are 2 things I would like to see, if it was there but with a different cost center # than in the current/latest month (looking at specific cost center)....then secondly, if it didn't exist in previous months' data but now in the specific cost center.

Hope that makes sense....from month to month some EQ#'s may move from one cost center to another (I only want to see a cost center for current month but if the previous cost center was different). this will tell me it wasn't in the new cost center until now, leaving out all the ones that were already in the current cost center. Just new EQ#'s (either from another cost center or weren't in the previous months' data at all).

Maybe this can't be done in one query?

EQ# is unique with the month. So it can only exist one time in the month of 11/1/2009.

Nope the EQ# will only have 1 cost center assigned each month.

so one month (11/1/2009) EQ# 1133 - CC# 1001110122- (HOU)
then next month it may be the same in 12/1/2009, if it is the same I don't care about it, but if it does change like...

12/1/2009 EQ#1133 - CC#1001210147-(DAL)

So now this EQ# now shows in Dallas Cost center so its new to Dallas even though in November it was in Houston. This one I would want to see for list of new for Dallas.

Then there may be some EQ# that didn't exist in Novembers data (11/1/2009) but now exist in 12/1/2009 under Dallas, ex:

12/1/2009 EQ#1145 - CC#1001210148-(DAL), this one I want to show up in list new to Dallas.

What I don't want to see are records that existed in 11/1/2009 Dallas Cost center and 12/1/2009 Dallas Cost center because this tells me it was already in Dallas and was not new to Dallas in Dec.

Right now with the query I'm working with it pulls if it didn't exist in Nov but now exists but I'm not getting the items that may have been in another cost center in Nov and now new to its new cost center.

Here is what it looks like:

Code:

SELECT A.EQ, A.[Func Loc], A.[Cost Ctr], C.LocName, C.LocCC
FROM (tblMain AS A LEFT JOIN (SELECT EQ FROM tblMain WHERE MonthYr=#11/1/2009#) AS B ON A.EQ=B.EQ) 
INNER JOIN tblLocCC AS C ON A.[Cost Ctr]=C.LocCC
WHERE (((A.MonthYr)=#12/1/2009#) AND ((B.EQ) Is Null));

Everytime I try to add in something for cost center <> cost center I get errors.

Hey W0lffy,

Thanks for the reply, I was thinking about what you last reply suggested but I use the table to calculate each months' data for other fields in the table, so I don't think I want to lose any records or else I'd have to make a new table all together.

I was tinkering around with this again right now and I put the following together to attempt to get the records that I was missing from the other query, can you take alook and see if it looks good to you? This seems to get me the records that were in another cost center in previous month and now in current month. So I need to see if I can combine the other query you helped me with with this one into one query...

Code:

SELECT A.EQ, A.[Cost Ctr], A.MonthYr, B.EQ, B.[Cost Ctr], B.MonthYr
FROM tblMain AS A INNER JOIN tblMain AS B ON A.EQ = B.EQ
WHERE (((A.[Cost Ctr]) Not Like [enter Not Like CC:] & "*") AND 
((A.MonthYr)=[Enter Previous MonthDate:]) AND ((B.[Cost Ctr]) Like [enter like CC:] & "*") 
AND ((B.MonthYr)=[Enter Current MonthDate:]));