Link to home
Start Free TrialLog in
Avatar of barnescs
barnescsFlag for United States of America

asked on

use of minimum function

I have a table that contains the results of a query that marries labor records to training records.  Some of the labor records have 2 training dates.   I am trying to write the code that will compare the date worked on the labor record to the training record, calculate the number of days between them and then use the training date that is closest to the work date.

empno     prodno          wrkctr           workdate        daysdiff          trainingdate
111111    13541000      501-5th          11/15/2005      463                 8/9/2004
111111    13541000      501-5th          11/15/2005        57                 9/19/2005
111111    13541000      501-5th          11/28/2005      476                 8/9/2004
111111    13541000      501-5th          11/28/2005        70                 9/19/2005

I have tried using select Min(bby.daysdiff) as Minofdaysdiff but for these records it chooses the 8/9/2004 training date instead of 9/19/2005.

Also in this same table I have these records and I am getting the correct training date:
empno     prodno          wrkctr           workdate        daysdiff          trainingdate
111111    13541000      501-5th          9/20/2005          1                  9/19/2005
111111    13541000      501-5th          9/20/2005        407                8/9/2004

Could it have something to do with the records place in the table and if so, I do I correct it?

Thanks.

cindy
Avatar of GRayL
GRayL
Flag of Canada image

Try:

SELECT * FROM myTable WHERE daysDiff IN (SELECT Min(a.DaysDiff) FROM myTable AS a WHERE a.empno=empno AND a.prodno=prodno AND a.wrkctr=wrkctr)
Avatar of Patrick Matthews
Hi barnescs,

SELECT empno, prodno, wrkctr, workdate, trainingdate, daysdiff
FROM YourTable
GROUP BY empno, prodno, wrkctr, workdate, trainingdate, daysdiff
HAVING daysdiff = Min(daysdiff)

Regards,

Patrick
Avatar of barnescs

ASKER


I have something wrong.  This won't run

SELECT bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY, from bby where daysdiff in (select min(a.daysdiff) from bby as a where a.empno=bby.empno and a.prodno=bbyprodno, and a.wrkctr=bby.wrkctr)

ORDER BY bby.PRODNO, bby.WRKCTR, bby.PAYDAY;
you've a comma after bby.PAYDAY

Now I get a synatx error from the 2nd select on.

from bby where daysdiff in (select min(a.daysdiff) from bby as a where a.empno=bby.empno and a.prodno=bbyprodno, and a.wrkctr=bby.wrkctr)
You didn't qualify all the fields with their table names or aliases - bby.daysdiff

from bby where bby.daysdiff in (select min(a.daysdiff) from bby as a where a.empno=bby.empno and a.prodno=bbyprodno, and a.wrkctr=bby.wrkctr)
Ok, I got ya!

cindy
Cindy,

Did you try my construction?

Regards,

Patrick
This is the entire SQL statement


SELECT bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY
from bby where bby.daysdiff in

(select min(a.daysdiff) from bby as a where a.empno=bby.empno and a.prodno=bby.prodno, and a.wrkctr=bby.wrkctr))


GROUP BY bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY
ORDER BY bby.PRODNO, bby.WRKCTR, bby.PAYDAY;

I still get a syntax error with the select min.......statement.
you've now got one too many closing parentheses.
Matthewspatrick

I tried your solution:

SELECT bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY, bby.daysdiff
from bby
GROUP BY bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY, bby.daysdiff
having daysdiff = min(daysdiff);

and I still get multiple training dates for the same labor record.
GRayL

Still get a syntax error with this:

SELECT bby.EMPNO, bby.PRODNO, bby.ORDNO, bby.SEQNO, bby.WRKCTR, bby.PAYDAY
from bby where bby.daysdiff in
(select min(a.daysdiff) from bby as a where a.empno=bby.empno and a.prodno=bby.prodno, and a.wrkctr=bby.wrkctr)
You have a table named bby.  To put it in a format I am used to creating:

SELECT a.EmpNo, a.ProdNo, a.OrdNo, a.SeqNo, a.WrkCtr, a.PayDay FROM bby as a WHERE a.DaysDiff IN
(SELECT Min(b.DaysDiff) FROM bby AS b WHERE b.EmpNo=a.EmpNo AND b.ProdNo=a.ProdNo AND b.WrkCtr=a.WrkCtr);

This has to run if the fields and tablenames are spelled correctly.  You can add the ORDER BY after you get this part working.
ok, I got that to run without any error messages but the daysdiff does not show in the resulting table so it is not available in the other query you have been helping with that calculates the status.

cindy
Then its a simple matter to add it to the list of fields in the SELECT statement - after a.PayPay insert - ,a.DaysDiff.
Sorry, that should be:  -  after a.PayDay insert - ,a.DaysDiff.

I have add a file called minimum function.  This file contains the tables and queries where this is calculated plus the status tables and queries.  I got the last code on the min that you sent me to work but it still does not pull the correct cert date for prodno 13541000 on payday 11/15/2005.  It pulls 9 Aug 04 and it should pull 19-sep-05.  The days diff calculates correctly but the program choises the record with the greater number of days diff instead of the smaller.

Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22144412
https://filedb.experts-exchange.com/incoming/ee-stuff/2304-feb-4-2007.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/2289-cindy-op-2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2307-minimum-function.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2387-status2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2247-WorkTimes.txt
https://filedb.experts-exchange.com/incoming/ee-stuff/2248-cindy-operator.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2427-status3.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2252-Rays.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2286-Ray2Cindy2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2366-status.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2303-cindy-op-2ray.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2307-minimum-function.zip 

I have been working on this stuff since 5:30 AM CST and I am tired.  I am calling it a day but will be back bright and early at 4:30 AM CST tomorrow.  If you have any luck today with this send me a post and I will take a look at it.

cindy
cindy:  Do you realize a lot of your fields (in the other thread) have trailing spaces.  That can play havoc with SQL.
I am open to any help and suggestions that you have.  I know just enough to be dangeruous.  Please forward all the suggestions that you have. :-)

cindy
You should do an update on your main data source table(s) - especially the text fields

UPDATE myTable SET
fldtxt1=trim(fldtxt1),
fldtxt2=trim(fldtxt2),
etc.

You also seem insistent on populating a table named bbbb with all the derived fields from the query to determine Status and reverting to your old naming convention(s) - Why?  Something like the powers that be insist on the names as they are?  If so, fine, but that is something that should have been said way back.

Anyway, I have to shut this thing down too.  I haven't been at it as long as you, but 'She who must be obeyed' is starting to make noises and the Superbowl starts soon!
I don't have to keep the old naming convention(s).  I have some other queries and reports that run off this data and I was trying not to have to change them.

cindy
OK, I have downloaded the mdb and looked at a few things.  The first thing I noticed is that your daysdiff field is text, not numeric which is why you get the wrong date.  How did that happen?  That is one of the problems with storing derived values - I, at the other end of this thing have no idea how you got there.  From a text point of view '476' is less than '70'.  
AHHHH.  What a duhhhh move but at least it was easy for a smart person to find.  :-)  I am not sure how it happened unless the oracle table that I pull it from has it as a text field.  I have been tied up most of the day on other things so I have only had a few minutes to look at this.  I will go into my table and change this field to numeric and re-run the query.

THANKS!!!!

cindy
I have been all over this stuff and I got to say I cannot appreciate what you are trying to to with the data after you import it from Oracle or where ever.  You seem successful at getting dates into the proper MS format.  However, the rest seems to go to hell in a hand basket.  You could start by telling us how many records come from the Oracle extract - then what you then do to the data to make it in a MS Access readable format, (I'm looking at tons of trailing spaces), the final structure of your MS Acess table, and where you want to go from there.  We keep seeing snippets of how you want to do this or how you want to do that, but I still haven't a clue what you are trying to accomplish.  If my involvement has stopped other EE members from lending their support, then I will certainly appreciate your request to CS to close the question and suggest you open a new one without my participation.  It's just that I do not know what else to say!

Ray
ok,  Let me see if I can fit into this space all of the things that happen with this data.  

I have a form with supervisor's pictures that allow them to click on their picture and get a list of their employees.  This list is derived from running a query off of a HR oracle table that has employees and their supervisors listed.  
Next a query runs that pull the labor records for the the last 18 months, from an Oracle table, and marries the employee number on the table to the employee number in an HR oracle table that contains the employee names.
The next query takes the data in the table created above and calcualtes the previous date, dates difference, number of times the prodno has been run, and a new previous date.  This is stored in a new table.
The next query takes the above table and joins the data to two other tables.  The first join pulls the SAP user name from an oracle table and adds it to the new table created by this query.  The second join pulls the work center name from an oracle table and adds it to the new table.
The next query takes the above table and deletes the work centers that the training requirements do not apply to.
The next query takes the table from above and joins the data in it to the table that lists the employees training records and calculates the days difference field.
The next query takes the table from above and calculates which training record should be used for each labor record.  This is where the min calculation is done.
The next query takes the table from above and calculates the status field.
The next set of queries takes SAP confirmation data that has been imported into tables and looks for all of the records for the employee that was choosen in the first query.
The next query pulls the SAP confirmation date for each of the labor/training records that are in the table for the employee.
The next two queries create the tables for the reports.  the first query pulls the expired and delinqent records into a report.  the other query pulls the current records and lists them in a report.

So in a nut shell the supervisor must be able to choose an employee and then get a report that shows for the last 18 months what parts the employee ran and if when they ran them their training was current, expired, or delinquent.

Let me know if this helps.

cindy
cindy:  Thanks, that's a good breakdown of where the data comes from.  Now if I may;-)  

>The next query takes the data in the table created above and calcualtes the previous date, dates difference, number of times the prodno has been run, and a new previous date.  This is stored in a new table.<  

You make the calculations here and then 'carry' the data thru the next five steps without doing anything with the calculations.  Only when you calculate the status field do you need the calculated fields from step 3.  In fact you should do the min calc along with the other calcs in this step >The next query takes the table from above and calculates the status field.<

If it were me, it would not be storing those derived fields in a table - unless you have to revisit the data from time to time and the dataset per employee may change for whatever reason.  It is still not clear what 'the number of times the prodno has been run' does.  I've seen numbers like 56.  Does that mean that the process to determine status for that supervisor/employee combination has been run 56 times?
I had created "the number of times the prodno has been run" field when I was trying to calculate the status field.  Since it am not going to need it and don't use it in any of the current calcualtions, I can delete it.

I am working on this trying to stream line my process and cut out any unnessesary steps that I see.  Give me a little while and I will try your suggestion above and let you know the outcome.

cindy
Standing by.
ok, this is what I have found:

The query that I have that calculates the "daysdiff" has this coding:

NZ(DateDiff("d",[Emp Certs Pn]![Date Certified],bb!payday),0)

After this query runs, the resulting table does have a daysdiff field but it is text and not numeric.  What do I need to change in this coding to make the field numeric?  This is why the correct "dates certified" is not being pulled in a subsequent query.

Thanks.

cindy
If the query is an update query, the field that is being updated is set to datatype text.  Go into the design mode of the table and change it to numeric, Integer or Long.  
Curious, is the table created new every time you rerun the exercise or is it just emptied and then filled with the new data?  If the former, you have to go to the process that creates the table anew, and change the daysdiff field to Numeric.  

This is the entire SQL statement.  Table bb is calcualted from another query.  Table Emp Certs Pn only has data added to it each time an employee has training data changes/updates.

SELECT bb.EMPNO, bb.EMP_NAME, [EMP CERTS PN].[Procedure/MESOP Number], bb.PRODNO, bb.ORDNO, bb.SEQNO, bb.WRKCTR, bb.[WORK CENTER DESCRIPTION], [EMP CERTS PN].Revision, bb.PAYDAY, bb.PrevDate, bb.NEWPREVDATE, bb.DatesDifference, [EMP CERTS PN].[Date Certified], bb.TIMESRUN, bb.[USER NAME], NZ(DateDiff("d",[Emp Certs Pn]![Date Certified],bb!payday),0) AS DaysDiff INTO bby
FROM bb LEFT JOIN [EMP CERTS PN] ON (bb.WRKCTR = [EMP CERTS PN].[Work Center]) AND (bb.PRODNO = [EMP CERTS PN].[Part Number]) AND (bb.EMPNO = [EMP CERTS PN].[Clock Number])
ORDER BY bb.PRODNO, bb.WRKCTR, bb.PAYDAY;
The table that holds the daysdiff number is deleted and re-created each time the query is run.
I don't understand how DaysDiff can wind up being a text field.  This is a make table query and the DateDiff function always produces a Variant subtype Long datatype.  The only thing I can thing of is to force the datatype.  From the above query change:

NZ(DateDiff("d",[Emp Certs Pn]![Date Certified],bb!payday),0)

to:

CInt(NZ(DateDiff("d",[Emp Certs Pn]![Date Certified],bb!payday),0))

and see what that does.
ok, let me make this change and see what happens.
BTW when you say the table is deleted, you mean totally gone, not just emptied.  Come to think of it, as I recall, if you empty a table and then run a maketable query with the same name, you get a warning about how the table will be deleted befoe running the query.  Still a mystery!
that worked.  I made the change and ran the query and the result was a numeric field for daysdiff.

THANKS!!!

Now I am going to run the query that generates the status field and see what happens.  Let you know something soon.

cindy
GRayL

We are really close.  I have uploaded my latest table with the status field and when the training goes to an expired status the next record shows up as current even thought the training date is the same (if less than 90 days between records).  I think I remember us talking about this but don't know if you came up with a solution.  So...here is the file.

Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22144412
https://filedb.experts-exchange.com/incoming/ee-stuff/2304-feb-4-2007.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/2289-cindy-op-2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2307-minimum-function.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2387-status2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2247-WorkTimes.txt
https://filedb.experts-exchange.com/incoming/ee-stuff/2248-cindy-operator.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2427-status3.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2252-Rays.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2286-Ray2Cindy2.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2366-status.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2303-cindy-op-2ray.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2366-status.zip 

OK, I have come to the conclusion you cannot do this update in a single query.  You have to modify the table bbbb you included in you last zip file with another query.  I renamed the table bbbbRay added a new field status1, and included the new query qudStatus1 in the zip file status1.zip.  By my calcs you missed four records which should have been annotated "Expired".  Have a look and get back to me.

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2373-Status1.zip 
ok, I copied the coding into a new query in my database.  I am getting an error message on the status1 field.  This is what I have:
UPDATE bbbb AS a SET a.Status1 = "Expired"
WHERE (((a.Status)="Current") AND (([a].[PayDay]-[a].[WkSeqDiff]) In (SELECT b.PayDay FROM bbbb AS b WHERE b.Status="Expired" AND b.PayDay = a.PayDay - a.WkSeqDiff AND b.EmpNo=a.EmpNo and b.ProdNo=a.ProdNo and b.WrkCtr=a.WrkCtr)));

Is this query supposed to add the status1 field?

cindy
When I run the query a parameter value box pops up for a.status1.

cindy
There was one other prodno that was incorrect and I have added expired to the 'status1' field for those records also.
They are just like the other ones and if we can solve the problem for the other records it will correct it for this one also.

cindy
I added status1 to give you an idea of where the bbbb.status field was different from my calc.  I was expecting to see only three records from my query, yet seven appeared.  I checked the fourth and it did not appear to be correct - ie. it should be 'expired' - so I reported as such.  Did you evaluate the other three?
Yes, what do we need to do now???

cindy
If you are satisfied the second query updated the table correctly, then change the query to modify the field status in your table bbbb.

This is the query that updated the field status1 in the table bbbbRay

UPDATE bbbbRay AS a SET a.Status1 = "Expired"
WHERE a.Status="Current" AND a.PayDay-a.WkSeqDiff IN (SELECT b.PayDay FROM bbbbRay AS b WHERE b.Status="Expired" AND b.PayDay = a.PayDay - a.WkSeqDiff AND b.EmpNo=a.EmpNo and b.ProdNo=a.ProdNo and b.WrkCtr=a.WrkCtr);


Change it to:

UPDATE bbbb AS a SET a.Status = "Expired"
WHERE a.Status="Current" AND a.PayDay-a.WkSeqDiff IN (SELECT b.PayDay FROM bbbb AS b WHERE b.Status="Expired" AND b.PayDay = a.PayDay - a.WkSeqDiff AND b.EmpNo=a.EmpNo and b.ProdNo=a.ProdNo and b.WrkCtr=a.WrkCtr);

If you want to 'check' the table bbbb before the update, change only the first line  and add the WHERE clause:

SELECT a.* FROM bbbb AS a
WHERE a.Status="Current" AND a.PayDay-a.WkSeqDiff IN (SELECT b.PayDay FROM bbbb AS b WHERE b.Status="Expired" AND b.PayDay = a.PayDay - a.WkSeqDiff AND b.EmpNo=a.EmpNo and b.ProdNo=a.ProdNo and b.WrkCtr=a.WrkCtr);

This will show you the records the query would have updated to "Expired"
so, this query will run after the one that calculates the status and will update any statuses that are incorrect?

cindy
Yes, as far as I can tell.  I just could not find a way to do it with a single query.  What ever you did to create bbbb which contained those seven errors (three you found and four I found), you have to finish up by running the last update query I gave you above to fit the last piece into the puzzle.
I guess that I am confussed. :-)  When I run the code that is above, all i get is 7 one field records and the field contains the word current.

The information that I sent you was for items that had a "current" status that should have been expired.  The query must be looking prodno because I have one prodno that has 40 records that have a current status and the status should be expired..

Also, how do we incorporate this into a query that will update the status field in the bbbb table?

cindy
You are running the SELECT query which just shows you the seven records which are in error.  I provided that for a bit of comfort to allow you to verify the 'changes' that will take place before you run the UPDATE query which precedes it. Have another read of that thread.
Sorry, that should be: "Have another read of that post."  
BTW, we must be getting close to setting a record for the longest thread in EE;-)  Actually, there are lots a lot longer, but I do sense we are getting near the end.
ok, I re-ran the code again and everything looks good except for the one part number that has 40 records on it and they have a current status instead of expired.  It is so exciting that we are almost there!!!

cindy
If that is in the bbbb table you posted in the status.mdb file, can you tell us the part number - I guess you mean ProdNo?
Yes, it is toward the end and the prodno is 13541500-500.  The first 8 have the correct status.  The remaining 40 so have a current status and should be expired.

cindy
This is getting scary.  I now found 73 records, which include the 40 you spoke of above.  Are you positive there were no more?  Have a look at the results of this query:

SELECT a.*
FROM bbbb AS a WHERE a.Status="Current" AND a.[Date Certified]  IN (SELECT First([b.Date Certified])  FROM bbbb AS b WHERE b.[Date Certified] = a.[Date Certified] AND b.Status="Expired" AND b.EmpNo=a.EmpNo and b.ProdNo=a.ProdNo and b.WrkCtr=a.WrkCtr);

This should give you 73 records.  I leave it to you to determine whether the other (73-47) 26 records are also incorrect.
ok, I ran the query above and got 66 records.  All of them were correct except for the 40 that are on prodno 13541500-500.  These should be expired.

Does that answer your question?

cindy
I have to leave earlier than usual today but will be back around 4:30 AM CST tomorrow.  Send me any ideas you have and I will look at them as soon as I get there tomorrow.

Thanks.

cindy
Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2387-status2.zip 

A new file status2.zip containing status2.mdb.  I have gone back to the beginning.  I created a table named bbbbR1 which contains only those values you import from the Oracle tables and update from other Oracle tables - as you provided in bbbb - but without the derived fields.  When you run the query qmtbbbbR1Status this query creates the table bbbbR1Status and generates all the calculated fields and makes an initial stab at filling in the status for each record. Then run the query qryCounts and it will give you a count of each of the three status types. I got Curr, Del, and Exp values of 946,44, and 20 which, not suprpisingly adds up to 1010, the number of records.

Now run qudbbbbR1Status.  This modified 123 records in table bbbR1Status and after running qryCounts again, I got Curr, Del, and Exp values of 823,44, and 143.  Does that look about right for that dataset?  I hope so!  With your early start tomorrow and my leisurely breakfast at my fav deli across the street, by the time I check-in, I expect you will be totally dismayed or walking tall.  Whatever, we still have 10 days to go before the audit.  I guess I am saying that if this approach looks like the way, you should create a test dataset which contains all the tricks which might foil my queries, and knowing in advance the values of Curr, Del, and Exp - you can test anything I throw at you.  Look like a plan?
This looks like a plan!!!  I'll get started on it now and should have something by the time your "leisurdly" breakfast is finished. :-)

Thanks!
Hi:  I should have mentioned I created an index for bbbbR1 on EmpNo, ProdNo, WrkCtr, [Date Certified], and PayDay to speed up the processing of the first query.
GREAT!  I am working on this now.  I had a manager that I had to help with an Excel vlookup formula this morning and I didn't switch gears to this until just a little while ago.  I should be able to get back with you in the next 30 minutes.  I am reading through your post now and then will go look at the files.

THANKS!!!
I think it is important to understand where I am coming from.  Once one of your directors, clicks on his pic, then selects an employee, the whole process of extracting the data, adding fields to the point where you are finished with Oracle should be automatic.  Then my process takes over, continuing with the addition of the the index and calculated fields, producing the preliminary status result, doing a count, finishing the status result (and doing a final count so you can compare - if necessary).

The counts are only there to give you an idea of what is going on during this development stage.  Maybe only the last count on the final status result might be retained for the director's edification.  I like names and abbreviations to 'rhyme' hence the field name changes to CPDiff - meaning Certified - PayDate difference, and PDDiff - meaning PayDay differences.  It makes it easier when going back and troubleshooting.  
GRayL,

I have taken a look at the bbbR1Status table after you ran the update query.  The problem now is that records that should be current are expired.  For example on prodno 13541445 all of the records should be current except for the ones with paydays of 03-mar-06, 15-jun-06, 15-aug-06, 7-dec-06.  These should all be expired.

cindy
OK I think I fixed that one.  Paste this as qudStatusFinal.  Note the inequality of PayDay in the Subquery.

UPDATE bbbR1Status AS e SET e.Status = "Expired"
WHERE e.Status="Current" AND e.[Date Certified]  IN
(SELECT First([f.Date Certified])  FROM bbbb AS f WHERE f.[Date Certified]  = e.[Date Certified] AND f.PayDay<=e.PayDay AND f.Status="Expired" AND f.EmpNo=e.EmpNo and f.ProdNo=e.ProdNo and f.WrkCtr=e.WrkCtr);


CountCurrent      CountDelinquent       CountExpired
850                   44                                 116
Do the EE files have this in them?
Ok, I copied the last too queries to my database and ran them.  I then reviewed the status listed in column a_status.  All of these were correct except for the last 39 records on prodno 13541500-500.  They all have current and should have expired.  You are sooooo close. :-)
Think I got it

qmtbbbbR1StatusPrelim

SELECT a.*, DateDiff("d",a.[Date Certified],a.PayDay) AS CPDiff, (SELECT Max(b.PayDay) FROM bbbbR1 AS b WHERE b.PayDay<a.PayDay AND b.EmpNo=a.EmpNo AND b.ProdNo=a.ProdNo AND b.WrkCtr=a.WrkCtr) AS PrevPayDay, (SELECT First(c.PayDay) FROM bbbbR1 AS c WHERE  c.PayDay<a.PayDay AND c.EmpNo=a.EmpNo AND c.ProdNo=a.ProdNo AND c.WrkCtr=a.WrkCtr) AS FirstPayDay, DateDiff("d",PrevPayDay,a.PayDay) AS PDDiff, IIF(IsNull(a.[Date Certified]) Or CPDiff<0,"Delinquent",IIF(CPDiff<91 OR (CPDiff>90 AND PDDiff<91),"Current","Expired")) AS Status INTO bbbR1Status
FROM bbbbR1 AS a
ORDER BY 1, 3, 4, 5, 6;

In the above query, I changed the order in which it computed 'Current' and 'Expired'.  This changed the counts from the previous.

qudStatusFinal

UPDATE bbbR1Status AS e SET e.Status = "Expired"
WHERE e.Status="Current" AND e.[Date Certified]  =
(SELECT First([f.Date Certified])  FROM bbbb AS f WHERE f.[Date Certified]  = e.[Date Certified] AND
f.PayDay<=e.PayDay AND f.Status="Expired" AND f.EmpNo=e.EmpNo AND f.ProdNo=e.ProdNo AND f.WrkCtr=e.WrkCtr);

In this query I changed the IN to = .  I don't think it changed anything, but it is sounder logically.  I went through the whole table (rather quickly) and I did not see any blatant errors.  Time for your fine toothed comb.

THANKS.  I am headed out for the day but will be back about 4:15 AM CST tomorrow and will look at it first thing.  Once I go through it I will post a response.  Should have something by the time you are finished with your "leisurely" breakfast. :-)

cindy
I found some errors on the bbbr1status file that is on EE.

Prodno. - 13541601
wrkctr - 501-2h
payday 8 nov 05
           9 nov 05
status = expired, s/b current

Prodno. - 13541000-501
wrkctr - 501-5wh
payday 26 apr 06
           27 apr 06
           26 jun 06
           25 jul 06
status = expired, s/b current

Prodno. - 13541500-500
wrkctr - 501-1h
payday 29 nov 05
           22 dec 05
status = expired, s/b current

Prodno. - 13541650-501
wrkctr - 501-4h
payday 26 sep 05
            5 oct  05 through 1 dec 06
status = expired, s/b current


cindy
Lets be clear.  You pasted those last two queries into your most recent table - empno - 122082,1010 records.  I checked my results and could not find your first example, all the rest were as you said they should be.
I was looking at queries and tables in the last file you posted on EE.  Should I instead copy these to my DB and run them?
I am uploading a new mdb status3 as we speak.  In it you will find three tables bbbb, bbbbR1, and bbbbR1Status. When you run qmtbbbbR1StatusPrelim it will create the table bbbbR1Status.  Run qryCounts and you should get 940, 44, and 26.  Now run qudStatusFinal.  When finished re-run qryCounts and you should get 850,44, and 116.  When I checked table bbbbR1Status against the errata you posted above, none were there.  To me, this looks like a solution.
YEAH!!!!  I think this is going to work.  I am going to copy these queries over to my database and see if I can replicate the results.  YEAH!!!!!!!!
Everything on this employee looks GREAT!!!!!!!!  I want to try one more employee before I celebrate.  It will probably be Sunday afternoon or EARLY Monday before I have a change to run another employee.  I will let you know what happens.

Thanks for all of your help so far!!!!!!!!
Thanks, but I learned a long time ago to stop holding my breath!
I understand!!  Everytime I have thought I was in good shape on this, I would find another problem.  If it makes you feel any better I have a huge headache today and I know that this part of it.  I do really appreciate all of your help and hard work.  You have really helped out!!!  I'll get back with you soon.

cindy
Hi cindy:  Monday morning - nice day here in Montreal.  Hope you headache is gone and your fine-toothed comb is not finding any more snags.
HI!!!  I am having fun with my printer this morning. :-)  I just got a report off the printer.  I pulled the data for another employee and am going through it now to see how it looks.  So far, so good.....keep your fingers crossed and I'll get back with you very soon! :-)

cindy
ok, gone through this report and....................YEAH!!!!! all the statuses are CORRECT!!!!!!!!!!!!  WOW!!!  how long have you been working on this??????  I am going to try just one more employee.  I am like you, I don't want to hold my breath cause I may turn purple!! :-)

cindy
ok, I have picked another employee that moves to different areas and works on more prodnos.  What is happening is when the program runs to choose a cert date for each labor record, everything works fine (no new calculations are stored).  When the next query runs to get some information from another table such as the employee name, and the wrk ctr description, on this employee, I get multiple certification dates again.  This did not happen on the first two people I tried?????  Should I upload a table to the ee site?

cindy
The statuses on these are correct, it is just that the join between the payday and the certification date is not holding and I am getting multiple records instead of just one.

cindy
Somewhere in the process preceding the point where I begin to make and store the calcs used to determine the status, your process of evolving the data appears to require one or more of those fields to make a proper certification date choice. As the employee name and work center description are not used to determine the status, is it feasible to leave them out of the process until the statuses have all been compiled - or is it part of the Oracle process which precedes the Access stuff?  

In your post at http:Q_22148894.html#18474845 you described the process.  It was not clear at which point you left Oracle and the process continued soley under Access.  For example, are you updating an Oracle data set from Oracle, or are you updating an Acess dataset from Oracle.  Could you amplify each step in that process and try to describe whether it is a create table or update table and the source and destination of the data.
Yes, I could add these fields after the status is determined.  It doesn't matter where in the process this is done.

I have a form with supervisor's pictures that allow them to click on their picture and get a list of their employees.  This list is derived from running a query off of a HR oracle table that has employees and their supervisors listed.  
Next a query runs that pull the labor records for the the last 18 months, from an Oracle table, and marries the employee number on the table to the employee number in an HR oracle table that contains the employee names.

The next query takes the data in the table created above and calcualtes the previous date, dates difference, number of times the prodno has been run, and a new previous date.  This is stored in a new table.  All done in access.

The next query takes the above access table and joins the data to two other tables.  The first join pulls the SAP user name from an oracle table and adds it to the new table created by this query.  The second join pulls the work center name from an oracle table and adds it to the new table.

The next query takes the above access table and deletes the work centers that the training requirements do not apply to. all access tables

The next query takes the access table from above and joins the data in it to the access table that lists the employees training records and calculates the days difference field.

The next query takes the access table from above and calculates which training record should be used for each labor record.  This is where the min calculation is done.

The next query takes the access table from above and calculates the status field.

The next set of queries takes SAP confirmation data that has been imported into access tables and looks for all of the records for the employee that was choosen in the first query.

The next query pulls the SAP confirmation date for each of the labor/training records that are in the access table for the employee.

The next two queries create the tables for the reports.  the first query pulls the expired and delinqent records into a report.  the other query pulls the current records and lists them in a report.

See if this clarifies any of your questions.

cindy
Thanks.  What we should be doing at this time is getting the employee records for the past 18 months from the Oracle table.  Do not make any calculations about previous dates or date differences.  We want a record with EmpNo, ProdNo, WrkCtr, [Date Certifiied], and PayDay.  That is what I want you to hand over to the two queries I gave you.  Don't carry derrived data around that you calculate at the beginning of a process and do not use until the end.  It is still not clear why you are getting multiple records of identical (?) data.  Describe the fields you initially extract from the Oracle table (into an Access table?) and what else you have to do to get those five fields I mentioned above.  
Hi cindy:  You seem to have your head down today.  All OK?
No, I have been trying to deal with a personal issue the last couple of days.  Sorry I didn't let you know I was out.  Let me look at you last question and see if I can get you a good answer.  It will be good for my mind of have something else to think about.  Get back with you shortly.

cindy
GRayL,

Are you going to be around Friday morning?  I haven't been able to spend much time on this today but want to try and wrap things up on it tomrrow.

cindy
cindy, Remember that leisurely breakfast at my fav deli across the street? I should be on line about 11:00 CST.    That OK for you?  
Yes, I remember those. :-)  11:00 CST will be fine.  That will give me time to ge in her and get my act together before you get on line.  Talk to you tomorrow. :-)
I'm back!
GRayL,

I am still not feeling well so I didn't go in today.  I WILL go in tomorrow EARLY and will send you something before you are finished with your leisurely breakfast, if you will be online.

cindy
I'll be ready to help.
GRayL,

THANKS!!! I'll talk to you tomorrow.

cindy
GRayL,

Ok, I am looking at your last comments and working with my queries to see what changes I need to make.

cindy
Ok, I have a table that contains the following:
empno
empname
prodno
ordno
seqno
wrkctr
description
payday
username

I have another table that has the certification data.  The fields are:
empno
procedureno
revision
prodno
wrkctr
date certified

These two tables are joined using empno, prodno, and wrkctr.
When I run the query I get mupltiple records for certification dates.  For example, if prodno was has a certification date of 2/15/06 and 10/21/06, then I get two records, one for each certification date.  I need some kind of calculation to say compare each labor record to all of the certification dates and only list the certification data that is closest to the payday.

cindy
Given the first table is table1 and the second is table2:

SELECT a.*, b.[Date Certified] FROM table1 INNER JOIN table2 ON
a.EmpNo = b.EmpNo AND a.ProdNo=b.ProdNo AND a.WrkCtr=b.WrkCtr
WHERE Abs(a.PayDate-b.[Date Certified]) = (Select Min(Abs(a.PayDate-c.[Date Certified])) FROM table2 AS c WHERE c.EmpNo = a.EmpNo AND c.ProdNo=a.ProdNo AND c.WrkCtr=a.WrkCtr);

When you are happy that works, turn it into a make table query by adding - INTO bbbb - before the WHERE clause.
You do know that should be another question;-)
Sorry!!! I was trying to get all of the data in one table as you suggested and didn't know how to do that without having some calculated fields.  Do you want me to add another question for that????

cindy
A little short today are we?  No I don't want another question.  Does that query work?
I'm sorry.  I didn't mean for it to sound short.  I got my hands slapped once before because I asked a question and so I didn't want to mess up again. :-) :-)  I have had a couple of interuptions but am getting ready to try this.  Will let you know in a few minutes how it worked.

Again, sorry if I sounded short!!!!

cindy
Ok, I am getting syntax error for the join and it highlights a.*

cindy
Sorry, I'm getting sloppy.  Does this make sense to you.  I'm shooting in the dark here but it seems to me we want to pick the [Date Certified] that is closest to but not after the PayDay - right?

SELECT a.*, b.[Date Certified] FROM table1AS a INNER JOIN table2 AS b ON
a.EmpNo = b.EmpNo AND a.ProdNo=b.ProdNo AND a.WrkCtr=b.WrkCtr
WHERE a.PayDate-b.[Date Certified] = (Select Min(a.PayDate-c.[Date Certified]) FROM table2 AS c WHERE a.PayDate-b.[Date Certified] >=0 AND c.EmpNo = a.EmpNo AND c.ProdNo=a.ProdNo AND c.WrkCtr=a.WrkCtr);

GRayL,

I tried this and cannot get it to work so I loaded my 2 tables and query on ee stuff.  PLEASE look to see where I have messed up.
Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22148894
https://filedb.experts-exchange.com/incoming/ee-stuff/2373-Status1.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/2549-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2551-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2524-cert-date.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2524-cert-date.zip 
 I am so sorry if I offended you earlier.  I did not mean to be short with you.  I appologize!!!

GRayL:
The certification date could be after the pay date if they worked on a part before they were certified and there is only on cert date.

cindy
Gotcha!  I'm nearly there.
GRayL:

Do you have a HUGE headache??? :-) :-)
Using your tables exactly as I received them I ran this query and it reduced the number of records from1934 to 1639.  Look about right?

SELECT bb.*, [eno certs ob].[Date Certified]
FROM bb INNER JOIN [eno certs ob] ON bb.PRODNO = [eno certs ob].[Part Number] AND bb.WRKCTR = [eno certs ob].[Work Center] AND bb.EMPNO = [eno certs ob].[Clock Number]
WHERE  bb.PayDay-[eno certs ob].[Date Certified] = (SELECT Min(bb.PayDay-c.[Date Certified]) FROM [eno certs ob] AS c WHERE bb.PRODNO = c.[Part Number] AND bb.WRKCTR = c.[Work Center] AND bb.EMPNO = c.[Clock Number]);
Never get headaches.  I guess I'm blessed.  Hope you are feeling better these days.  

If you follow the logic of that query, it is looking for the [Date Certified] that is closest (either before or after) to the PayDay by looking for the minimum of the date difference.  I could have 'done it right' and used the DateDiff() function but in this case as we are not viewing the difference but just using it to separate the wheat from the chaff, so to speak, I just subracted the two dates which leaves me with a double precision number which I want to minimize.  Because there are fewer records after the query is run, I can only surmise that there were records where there was no certification.  More importantly, when I ran the query with just the JOIN, the record could increased to over 3k - meaning there was more than one [Date Certified] for every empid, prodno, wrkctr combination, and the subquery weeded those ones out - hopefully!

GRayL
I'm ok, it's just been a long day.  I got here at 4:30 this moring and starting to feel it. :-)

When I ran the first query to get the labor records i got 1934 records.  When I tried to join this data with the certification data i also got over 3000 records.  Can the code be written so that we also get the records that do not have a certification data?  Those are part of the records that get a "delinquent" status when that query is run.

cindy
GRayL,

I think so but I want to take a really good look at all of the records and see which ones are being deleted.  I am going to leave pretty soon but will be back in the morning bright and early, before your leisurely breakfast.  I will look at this first thing when I get in and my mind is fresh.  Then I will run this table against the 2 queries that generate the status and see what happens.  Keep your fingers crossed and have a great rest of the day!!!

cindy
Assuming the query I gave you above is stored as qryDatesCert, run this

Select bb.*, a.[Date Certified] FROM bb LEFT JOIN qryDatesCert AS a ON bb.EmpID=a.EmpID AND bb.ProdNo = a.ProdNo AND bb.WrkCtr = a.WrkCtr and bb.[Date Certified] = a.[Date Certified];

This should return 1934 records, 1639 of which will have the [Date Certified] field filled in.
GRayL:

I used this code:
SELECT bb.*, [eno certs ob].[Date Certified]
FROM bb INNER JOIN [eno certs ob] ON bb.PRODNO = [eno certs ob].[Part Number] AND bb.WRKCTR = [eno certs ob].[Work Center] AND bb.EMPNO = [eno certs ob].[Clock Number]
WHERE  bb.PayDay-[eno certs ob].[Date Certified] = (SELECT Min(bb.PayDay-c.[Date Certified]) FROM [eno certs ob] AS c WHERE bb.PRODNO = c.[Part Number] AND bb.WRKCTR = c.[Work Center] AND bb.EMPNO = c.[Clock Number]);

and saved it as qryDatesCert.

  I then created another query and entered this code:
Select bb.*, a.[Date Certified] FROM bb LEFT JOIN qryDatesCert AS a ON bb.EmpID=a.EmpID AND bb.ProdNo = a.ProdNo AND bb.WrkCtr = a.WrkCtr and bb.[Date Certified] = a.[Date Certified];

When I run it I get the following error message:
The specified field 'bb.[Date Certified]" could refer to more than one table listed in the FROM clause of your SQL statement.

The qryDatesCerts is a select query.  Should it create a table or be an update query?

cindy

Try this:

Select a.*, b.[Date Certified] FROM bb AS a  LEFT JOIN qryDatesCert AS b ON a.EmpID=b.EmpID AND a.ProdNo = b.ProdNo AND a.WrkCtr = b.WrkCtr and a.[Date Certified] = b.[Date Certified];
In JOINS, Jet can lose track of which instance of a table you are wanting to use.  In the query I used the table names.  In the JOIN, I used aliases for both the table and the query.  That should work.  
GRayL:

Ok, I used this code:
Select a.*, b.[Date Certified] FROM bb AS a  LEFT JOIN qryDatesCert AS b ON a.EmpID=b.EmpID AND a.ProdNo = b.ProdNo AND a.WrkCtr = b.WrkCtr and a.[Date Certified] = b.[Date Certified];

and when it started running I got an enter parameter value box asking for a value for a.date cert.  The query finished running but the date cert column was blank.

cindy
We have a luncheon with the sailing club in an hour so I must be off.  I'll check in about 15:00 CST to see where you are at.  
GRayL:

That sounds like lots of FUN!!!!  I hope it is warm though!  I'll let you know what happens! :-)

cindy
GRayL:

Ok,  used this code:
SELECT bb.*, [eno certs ob].[Date Certified]
FROM bb INNER JOIN [eno certs ob] ON bb.PRODNO = [eno certs ob].[Part Number] AND bb.WRKCTR = [eno certs ob].[Work Center] AND bb.EMPNO = [eno certs ob].[Clock Number]
WHERE  bb.PayDay-[eno certs ob].[Date Certified] = (SELECT Min(bb.PayDay-c.[Date Certified]) FROM [eno certs ob] AS c WHERE bb.PRODNO = c.[Part Number] AND bb.WRKCTR = c.[Work Center] AND bb.EMPNO = c.[Clock Number]);

and saved it as qryDatesCert and got 1636 records.

I then ran
Ok, I used this code:
Select a.*, b.[Date Certified] FROM bb AS a  LEFT JOIN qryDatesCert AS b ON a.EmpID=b.EmpID AND a.ProdNo = b.ProdNo AND a.WrkCtr = b.WrkCtr and a.[Date Certified] = b.[Date Certified];

and get this  enter parameter value box asking for a value for a.date cert but get a table with 295 records with no cert date.  Combined that gives me 1931 records.  

By the time you get online Monday, I will have these combined into one table and have run the 2 status queries to see what it looks like.

I hope you have a wonderful time at your luncheon.  I am going to see if I can find something fun to do this afternoon.  I need a little break to prepare me for this LONG week ahead.

Thanks for all of your help!!!! :-)

cindy :-)

-22 C (-8 F) overnight tonight.  It'll be a while yet before boats go back in the water.  The luncheon at a restaurant was OK.  Message you tomorrow.
GRayL,

I took the results of these queries and put them into table bbbb:
#1
SELECT bb.*, [emp certs pn].[Date Certified]
FROM bb INNER JOIN [emp certs pn] ON (bb.PRODNO=[emp certs pn].[Part Number]) AND (bb.WRKCTR=[emp certs pn].[Work Center]) AND (bb.EMPNO=[emp certs pn].[Clock Number])
WHERE bb.PayDay-[emp certs pn].[Date Certified] = (SELECT Min(bb.PayDay-c.[Date Certified]) FROM [emp certs pn] AS c WHERE bb.PRODNO = c.[Part Number] AND bb.WRKCTR = c.[Work Center] AND bb.EMPNO = c.[Clock Number]);

#2
Select a.*, b.[Date Certified] FROM bb AS a  LEFT JOIN qryDatesCert AS b ON a.EmpID=b.EmpID AND a.ProdNo = b.ProdNo AND a.WrkCtr = b.WrkCtr and a.[Date Certified] = b.[Date Certified];

I then tried to run this query to start the status calculation:
SELECT a.*, DateDiff("d",a.[Date Certified],a.PayDay) AS CPDiff, (SELECT Max(b.PayDay) FROM bbbb AS b WHERE b.PayDay<a.PayDay AND b.EmpNo=a.EmpNo AND b.ProdNo=a.ProdNo AND b.WrkCtr=a.WrkCtr) AS PrevPayDay, (SELECT First(c.PayDay) FROM bbbb AS c WHERE  c.PayDay<a.PayDay AND c.EmpNo=a.EmpNo AND c.ProdNo=a.ProdNo AND c.WrkCtr=a.WrkCtr) AS FirstPayDay, DateDiff("d",PrevPayDay,a.PayDay) AS PDDiff, IIF(IsNull(a.[Date Certified]) Or CPDiff<0,"Delinquent",IIF(CPDiff<91 OR (CPDiff>90 AND PDDiff<91),"Current","Expired")) AS Status INTO bbbR1Status
FROM bbbb AS a
ORDER BY 1, 3, 4, 5, 6;

The table was blank but I did not get any errors.  I have looked at this query and all of the fields are in the bbbb table and the fields are the correct data type.  I know I must be missing something that should be really OBVIOUS.

cindy


Hi: I'm back and investigating.
GRayL:

I thought you had forgotten about me. :-)

cindy
Let me talk my way through this - The Process:

1.  You (or a director) select an employee of the director and extract all the direct charge (?) records for the past 18 months for that employee.  Your QA or someone requires that you be able to demonstrate which of those records were Delinquent, Current, or Expired according to the rules you have established.  This recordset goes into a table bb   This current set for EmpNo 121820 contains 1934 records.

2.  Then you extract all the training records for some period of time for a lot of people (all the employees?) - probably for the ProdNo's and WrkCtr's that show up in the employee's recordset above.  Is this [eno certs ob]? - and what does that mean.  Interestingly, 2347 records show up, only 25 of which apply to this employee.  Of those 25, a few have multiple [Date Certified] for the EmpNo, ProdNo, WrkCtr combination.  Is the job now to apple the Status to all 1934 records from step 1?  In which case I have to ask why are we dealing with 2347 records from this step instead of 25?

3.  If that is the case, we then apply the correct [Date Certified] to the 2347 records from step 1 using the 25 records from step 2 - correct?

I'll wait to hear back from you before proceding to the next step.

I should have added:  "...using  the [Date Certified] closest to the PayDay."
GRayL:

The process:
1.  What you stated in number 1 above is correct.
2.  In this step I am selecting all of the training records for the employee choosen in step number one out of a table that contains the training records for all employees.  The reason for 2,347 records is that a employee can have multiple training records for a partno, wrkctr.  If 90 days lapses between times that the employee runs the part, they have to re-certify.  So, if an employee goes out sick, is temporarily moved to another area, etc. 90 days will lapse and the certification be invalid.  Also, because until recently, the tracking system for the certifications has been mostly a manual process, the manager may re-certify because they think the certification has expired.  Training records are never removed from the table.  This step also applies a certification date to each labor record.  If there is more than one certification date, then the query lists each partno, wrkctr as many times as there are certification records for that partno, wrkctr.
3.  If, as in this case, I start out with 1934 records, then after I match the certification records, i should still have 1934 records.

Does this help any??? :-)

cindy
Re step 2 - then why does the table [eno certs ob] contain 129 different [Clock Numbers], only one of which is 121820, the EmpNo from table bb?  As I said before, there are only 25 records in the training table with the [Clock Number] 121820.
GRayL:

let me take a look at the table.  Are you looking at the one on EEStuff?

cindy
GRayL;

Re Step 2 - table [eno certs ob] is the table that contains all the training records for all the employees.  What the query needs to do is to take these 25 records for this employee and compare them to the 1934 records in table bb and associate a certification date to each of the 1934 labor records.

cindy
Finally, we see the light;-)  I'll be back in a while.  I will be importing my status queries into the [cert date] mdb and running everything from there.  When I get it all working I'll repost the new [cert date] as [CertDateMod].
Cindy:
I'll be waiting for your posting.  :-)

cindy
One other thing.  We start off here with table bb.  When we applied the status we were working with table bbbb.  Is this the evolution?

bb - raw data
bbb - added field [Date Certified]
bbbb - added field Status and make the correct fill-ions.

GRayL:
Yes, it isn't very original but that is the evolution.  :-)

cindy

Standby for further.
Ok, I'll be here when you get back.

cindy
View all files for Question ID: 22148894
https://filedb.experts-exchange.com/incoming/ee-stuff/2373-Status1.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/2549-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2551-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2524-cert-date.zip


Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2549-CertDateMod.zip
      
      

Note I changed the name of [eno certs ob] to EnoCertOb

1.  Run qmtEnoCertsObMin - produces the 25 record set

2.  Run qmtbbbAddDateCert - adds the [Date Certified] field

3.  Run qciIndbbb - creates the Index on table bbbb so the following qry runs quicker.

4.  Run qmtbbbbPrelimStatus - makes table, adds and does a preliminary evaluation of the Status field.

5.  Run qryCounts

6.  Run qudStatusFinal - does a final adjustment of the Status field

7.  Run qryCounts

We there yet?
Should be:


3.  Run qciIndbbb - creates the Index on table bbb so the following qry runs quicker.
Ok, we have had trouble with our Oracle tables today and the DBA is just now getting everything running again.  I have a couple of weekly reports to get out and then I will look at all of these.  Does EEStuff contain a table that is a result of all of the queries that I could look at first or do I need to run all of the queries to create the tables?

cindy
EE stuff contain CertDateMod.zip which contains CertDateMod.mdb.  That file contains 2 tables and a bunch of queries.  Table bb is the recordset you last gave me.  EnoCertsOb is the table will all the labor records.  AS far as I know the rest of the stuff is created when you run the queries in the sequence I outlined above.  Good Luck!
That will work great!!!  I should be where I can look at the queries and run them in about an hour.

THANKS and keep your fingers crossed. :-)

cindy
Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:

View all files for Question ID: 22148894
https://filedb.experts-exchange.com/incoming/ee-stuff/2373-Status1.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/2549-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2551-CertDateMod.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/2524-cert-date.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/2551-CertDateMod.zip 
 
I only see one thing that I have a question on.  In table bbbb, partno 13540400-911, wrkctr 0906, the certification date listed on all records is 03-apr-06.  If you look in the table created by query qmtEnoCertsObMin, this prodno, wrk center is listed twice with certification dates of 03-apr-06 amd 23-mar-06.  The payday start on 22-Mar-06.  Why don't any of the records in bbbb have the 22-mar-06 certification date?  This is the only thing that I can find.

I've been at this for over 13.0 hours so I am heading out and will be back about 4:30 tomorrow morning.

Thanks.

cindy

 
Do you think we have done all we can with this?

cindy
No, I have a fix.  Replace qmtbbbAddDateCert with:

SELECT b.*, c.[Date Certified] INTO bbb
FROM bb AS b LEFT JOIN EnoCertsObMin AS c ON (b.EMPNO=c.[Clock Number]) AND (b.ProdNo=c.[Part Number]) AND (b.WrkCtr=c.[Work Center])
WHERE IsNull( b.PayDay - c.[Date Certified]) OR Abs(b.PayDay - c.[Date Certified]) = (SELECT Min(Abs(b.PayDay - d.[Date Certified])) FROM EnoCertsObMin AS d  WHERE  b.EMPNO = d.[Clock Number] AND b.ProdNo=d.[Part Number] and b.WrkCtr=d.[Work Center])
ORDER BY b.EmpNo, b.ProdNo, b.WrkCtr, c.[Date Certified];

Notice how the Abs() function inside the Subquery is inside the Min() function.  That hit me at 4:00 am this morning.

Delete all the tables generated by my process except for bb and EnoCertsOb and run through the seven steps I gave you previously.  You should notice a difference.
:-)  Isn't it weird how the solution to stuff like this will hit you at the oddest times.  I have a meeting at 9:00 that will probably last about 30 minutes.  Once i am finished with it, I will replace this query, delete all the tables generated by the process except for bb and EnoCertsOb and then run through all the queries.

Please hang in there! :-)

THANKS!!!

cindy
ok, my meeting took a little longer than I expected.  I am starting on this now and will get back to you shortly.

cindy
THAT WORKED!!!!!!!  YEAH!!!!!

cindy
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I just want to let you know how much I appreciate your time, patience, and expertise.  You have made this a wonderful learning experience and have been very helpful!!  :-)  Your way of dealing with people has made me feel like I was working with an long time friend instead of a stranger.  THANK you so much!!! :-)

The first time that I went to the Dallas plant, which was under the LTV umbrella, I went to their "high bay" building where they built airplanes.  I had never been in a building so BIG!.  I started walking down the aisle way to get to some offices and saw people riding bicycles and sort of laughed but after I had walked several minutes and saw that I had a long way to go and looked back and saw that I had already walked a long way, I understood why the bicycles were in use. :-)

cindy
cindy:  Thanks, glad to help.

Ray
An Interesting blog:

http://www.wander-woman.com/
it is!!!  THANKS!!!