Re: [MSAccessProfessionals] Different data in same column

Doyce,

Is there any follow-up to your question? Did you get the assistance you needed?

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom via groups.io <duanehookom=hotmail.com@groups.io>
Sent: Monday, November 17, 2025 4:02 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Different data in same column

 

The "new" format is the most useful since it's normalized. You can create a crosstab query with the pay code as the column heading. The employee ID and I expect a date filed will be the Row Headings and Sum of hours as the value. 

Duane

On Nov 17, 2025, at 2:19 PM, Doyce Winberry via groups.io <doyce.winberry=xpo.com@groups.io> wrote:



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