Select query

[nirapreet]

Hi

I am trying to generate a report based on a table.
My table has fields as below
FnCode ErrCode ErrDesc and effective date

The logic behind it is, The data is uploaded in the table on any date. This data tell the errors for particular fund on the date it was uploaded. Now if on a particular date there were 10 errors, next week there could be 40.
For eg
FnCode ErrCode ErrDesc and effective date
aaa err2 adderr 29/12/2005
aaa err1 adderr 01/01/2005
aaa err2 adderr 01/01/2005
bbb err1 invderr 30/12/2004
bbb err9 invderr 30/12/2004
bbb err4 psterr 21/01/2005
ccc err1 adderr 30/12/2004
ccc err1 adderr 30/12/2004
ccc err1 adderr 01/01/2005
ccc err1 adderr 01/01/2005

Now there are diffrent latest effective date for the funds depending on the day data was uploaded.

The report I want to generate is that it should display aal the latest dayes error for all the fund So I should get the output as
The next week the errors could be
FnCode ErrCode ErrDesc and effective date
aaa err1 adderr 01/01/2005
aaa err2 adderr 01/01/2005
bbb err4 psterr 21/01/2005
ccc err1 adderr 01/01/2005
ccc err1 adderr 01/01/2005

This by selecting all the latest errors for each fund.

Anuy solutions please..!!

Thanks in advance

 

[john471]

Two Queries

Step 1)
Create a query to retireve the maxDate for each fnCode:-

SQL for "qryFundMaxEffectiveDate" :-
SELECT YourTableName.FnCode, Max(YourTableName.[effective date]) AS [MaxOfeffective date]
FROM YourTableName
GROUP BY YourTableName.FnCode;

Step 2)
Join this query to the undelying table to display all rows where for each FnCode where the date=maxdate.

SQL for desired result :-
SELECT YourTableName.*
FROM YourTableName INNER JOIN qryFundMaxEffectiveDate ON (YourTableName.[effective date] = qryFundMaxEffectiveDate.[MaxOfeffective date]) AND (YourTableName.FnCode = qryFundMaxEffectiveDate.FnCode);


Note: Your example has an entry
aaa err2 adderr 29/12/2005
where I think, based on your stated desired result set should have been:-
aaa err2 adderr 29/12/2004

HTH

Regards

John

 

[nirapreet]

Thanks John,

Great..That worked..!!
I sthere also a way where I can pick up all the records with the date as 2nd last date ie if the data was uploaded on date 1 and then on date 2, I should be able to pick all the records from date 1.

Thanks for your help.


nirapreet

 

[john471]

Perhaps this will work:-
Use the MaxDate query from Step1) before.
Step 2) Use the Find Unmatched Query Wizard to build a query retrieving all records form the base table that don't match the maxDate. I think the Unmatched query wizard allows "matching" on only one field, and you need two fields, so choose one in the wizard, then in the design grid create the other join exactly th ssame as the one the wizard created, and add in the matching field with Is Null criteria.
Step 3) create another query similar to the one in Step 1, except based on the query you built in step 2. This should give result of the 2ndMax (for want of a better term).
Step 4) Use the query from Step 3 in a query similar to the one that gave you your maxDate result-set (previous post). - i.e. Join the query created in Step 3 of this post to your base table, on the two matching fields.

HTH

Regards

John.
Re

 

[nirapreet]

Re: query

Thanks for your help John,

Actually I was able to get the solution usin the Top1 pred and Not IN.

But I am stuck on some other point now. What I was trying to do is that as my table can have duplicate row for different date ie as below,

FnCode ErrCode ErrDesc and effective date
aaa err2 adderr 29/12/2005
aaa err2 adderr 01/01/2005

ccc err1 adderr 30/12/2004
ccc err1 adderr 01/01/2005

I want to select the previous date rows(errors) and compare the sets, if there are some rows which were on previous dates and not in current dates, these are the fixed errors. I want to find all the fixed errors.

Thanks for your time John.

Nira

 

[john471]

Thanks for your help John,

Actually I was able to get the solution usin the Top1 pred and Not IN.

You are welcome. Glad you found a solution. I can see that the above should prob work, I just didn't think of it.

But I am stuck on some other point now. What I was trying to do is that as my table can have duplicate row for different date ie as below,

FnCode ErrCode ErrDesc and effective date
aaa err2 adderr 29/12/2005
aaa err2 adderr 01/01/2005

ccc err1 adderr 30/12/2004
ccc err1 adderr 01/01/2005

I want to select the previous date rows(errors) and compare the sets, if there are some rows which were on previous dates and not in current dates, these are the fixed errors. I want to find all the fixed errors.

Thanks for your time John.

Nira

Hmmm that sounds a little tricky, and beyond the scope of the help I can offer you. I'm not sure of forum etiquette on this point, but you may want to move that (new) question to a new thread, as you'd have more chance that more people would look at it. Naturally you'll want to do a few searches first, to ensure the answer isn't already out there.

Regards

John.

 

[nirapreet]

I will try that..!!

Thanks once again for your time..

Nira