[MSAccessProfessionals] Different data in same column

Friends,

 

We have a new timekeeping system and I’m used to importing hours from the old system into a database where it can be sliced, diced, grouped, and etc.  Unfortunately, now when I’m running a report to extract the hours, there are so many types of paycodes used that the report puts all the paycodes in same column and the hours associated with each paycode in another “shared” column instead of making a column for each paycode and putting the hours in the correct column.  See examples:

This is the report I get.

Employee ID	Paycode	Total Hours
30252579	PTO Scheduled Current Year	3.50
30252579	Regular	36.57
30244320	Overtime	0.20
30244320	Regular	40.00
30250342	Regular	40.00
30250342	Overtime	0.18
30079782	Overtime	0.38
30079782	Regular	40.00
30268802	Regular	40.00
30268802	Overtime	0.33

 

This is how I want the data with 1 line per employee ID:

Employee ID	Regular	OverTime	PTO
30252579	36.56667		3.5
30244320	40	0.2
30250342	40	0.183333333
30079782	40	0.383333333
30268802	40	0.333333333

 

Are there any easy ways to do this?  My thoughts are to query the top table with If statements to then use a totals query on the first query to put all the records into one row.    

 

 

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPO

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269