Combining 2 query columns into 1 column with data in separate rows

[Blackfaer]

So I'm pretty advanced with macros and designer fields but very limited on actual VB coding... so would love a solution that doesn't mean I have to go into the VB under the hood...

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.



Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type

101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Any suggestions? Thanks for your help!

Access 2007, Windows 7.

[pbaldy]

You could do that with a UNION query:

SELECT ID, RegularHours, "Reg" As PayType
FROM TableName
WHERE RegularHours > 0
UNION ALL
SELECT ID, OvertimeHours, "OT" As PayType
FROM TableName
WHERE OvertimeHours > 0

Adjusting for your actual names of course.

[Blackfaer]

Just tried that - as I said, I'm amateur with VB coding... got an error: Characters found after end of SQL statement.
Tried changing a couple things but still same error: here's the code at the moment...

SELECT [Payroll Export Query 2].ID, [Payroll Export Query 2].Regular
FROM [Payroll Export Query 2];
UNION ALL
SELECT [Payroll Export Query 2].ID, [Payroll Export Query 2].OT
FROM [Payroll Export Query 2];
WHERE [Payroll Export Query 2].OT >0

[pbaldy]

Get rid of the semi-colons.

[Blackfaer]

Duh, that was it. The query builder tool does those semi colons automatically which is where I started... Thanks so much that did what I needed exactly!

[pbaldy]

Happy to help, and welcome to the site!