Microsoft Excel - Using barcode to scan data into excel, then arranging data as layers

There are two types of barcode I scan into my excel file, one is employee ID Number, another is training record cards.

Employee ID starts with barcode 9751001 , 9751002, ...

Training card record barcode starts with 1001, 1002 , ....

Basically, I want two records.

1. Attendance record : so that when employee 9751001, scans his id card with barcode 9751001, the information is populated to another excel sheet with time stamp. I have mostly done it and seems to be almost working with few bugs. Will appreciate any bright ideas on this one as well.

2. Training record : the one I am really struggling - when employee completes a training card 1001, employee id 9751001 is scanned first and then, training card 1001 is scanned. This way, it goes into training record that employee 9751001 has completed that training. This should not disturb my first attendance record. I am trying to use same scanner and same excel sheet to collect data for both records and then populate into other excel sheets. Will appreciate any ideas. Can maintain separate sheets as well to collect data from scanner if that makes it easier. 

Will really appreciate, thanks.




   

Hi Sandy,
A question.  Do the four digits on the training card always correspond with the last four digits of the employee id?
Regards
Harry

Hi Harry,

I should have chosen some other digits as an example for training card. There is no co-relation between employed Id and training card barcodes. They are both independent of each other.

I am going to generate barcodes for both employee id and training card myself, and I want it to create it in such a way that excel knows whether its a training card or employee id which is being scanned. If its employee id only, it will go into attendance register but if its training card then there will be option to scan employee id into it..

Thank you.

Sandy

Hello Sandy,
Perhaps you can just distinguish on the basis of the range of digits - greater than 10000 is employee, less than is training card or alternatively the number of digits, x digits is employee, y digits is training card.
Possibly you could use a count-down timer triggered by reading an employee code and, if the timer times out before the second card is read, it is a clock on/off, otherwise a training record.
Regards
Harry

Could you scan the training card first, then the ID card to assign the training to that employee.  rather than the other way and mess with your clock in clock out.