Need help in getting the logic to derive table.

[10e5x]

1) IMHO, Unless we have the dates & times in MM/DD/YYYY format, it will be difficult to proceed. It can be done in access as well as the source Excel before importing. Would prefer it to be done before importing, as I believe it will be easier.
2) The EventDate & EventTime fields should be in Date/Time Format.
3) If and only if the above 2 conditions are satisfied, then you can proceed with checking the final results of qryCart, whether they are correct or not & what else needs to be done.
4) Taking a deep look at the source data & understanding all functional implications is a must. Can't emphasize enough on this point.
5) I have some issues at my end, due to which I am not able to take a look at the db. However, I have managed to take a look at the Excel you had posted earlier. Let's hope, some one manages to take a look at the db.

Thanks

Hi recyan,

I will try talk to the db adminstrator as i do not have rights to the db source(oracle). What i have been working on is an access view/table created from the source. I will be able to extract directly from the db source starting next week, but before that i am told to derive working hours and no.of consecutive worked days. Therefore have been consulting u.

For working hours what i know is to take the exitDT minus the entryDT, however i do not know how the logic to match them accordingly.

For consecutive worked days, i know that i should compare the entryDate of each event of the same employee, if it is back-to-back i will add on to the counter. However problem will arise if the emp clock in on 1st may 03:00 and clock out and clock in again on same day at maybe 23:00 hours. He has been actually working consecutively.

For now i cant change the source before importing as u requested. Just hope other guys can help me in deriving the two logics needed. At the meanwhile i will move on to do cont my next task(SSIS and SSRS) first.

Finally i would like to really GREATLY thank you for all your help. You are so helpful.

THANKS,
10e5x

P.S. Do you have any good SSIS/RS forums to intro? After my exp here, i kinda like these type of forums.

 

[recyan]

I will try talk to the db adminstrator as i do not have rights to the db source(oracle). What i have been working on is an access view/table created from the source.

You don't have to talk to anyone for this.
At your end, convert the EventDate to MM/DD/YYYY format & use the converted Date field in our queries.
Just google, how to convert DD/MM/YYYY to MM/DD/YYYY in excel or access.
You can also change the Type of EventDate & EventTime fields in your access table (the access table at your end) to Date/Time Fields Type.

For working hours what i know is to take the exitDT minus the entryDT, however i do not know how the logic to match them accordingly.

For consecutive worked days, i know that i should compare the entryDate of each event of the same employee, if it is back-to-back i will add on to the counter. However problem will arise if the emp clock in on 1st may 03:00 and clock out and clock in again on same day at maybe 23:00 hours. He has been actually working consecutively.

As you have rightly pointed out, there are many ifs & buts in the data given to you.
Another eg. If a person ( at work from 9.30 pm on 1st May 2012 to 8.00 am on 2nd May) clocks in on 1st May 2012, at 9.30 pm & then goes out at 11.45 pm & then comes back again at 12.15 am on 2nd May 2012 & then exits at 8.00 am on 2nd May 2012....
Usually, there is additional data available for an employee in the sense, in what shift he is working on a particular date.

P.S. Do you have any good SSIS/RS forums to intro? After my exp here, i kinda like these type of forums.

Sorry, Don't have any info on this.

Thanks