Reverse Crosstab

I'm looking for an SQL or VBA solution to reverse a crosstab. I have a staffing plan of employees with their hours spread out over 200 columns.  I can do a query in SQL using UNION ALL. Normally I would append all to to my destination table (tblStaffPlanDN) in a second query. However, this SQL query is too complex because I need more than 200 SELECT statements. Is it possible to do a VBA solution with a loop instead of writing out 200 select statements? What would it look like? Looking at other questions in the forum, I may need to use DAO but I have no idea how to do that.

D001_Date is the first day of the staffing plan (in this case, Day 1 = 12/1/2015).  
D001_D is the hours to be worked on the Day shift on Day 1.
D001_N is the hours on the night shift for Day 1
D002_D and N are for the day and night shift of the second day (D001_Date +1).
D003_D and N are for the third day (D001_Date+2), etc.

SELECT tblStaffSpreadDN.EmployeeID, tblStaffSpreadDN.D001_Date As WorkDate, tblStaffSpreadDN.D001_D as WorkHours, "D" AS WorkShift
FROM tblStaffSpreadDN
UNION ALL
SELECT tblStaffSpreadDN.EmployeeID, tblStaffSpreadDN.D001_Date As WorkDate, tblStaffSpreadDN.D001_N as WorkHours, "N" AS WorkShift
FROM tblStaffSpreadDN
UNION ALL
SELECT tblStaffSpreadDN.EmployeeID, tblStaffSpreadDN.D001_Date+1 As WorkDate, tblStaffSpreadDN.D002_D as WorkHours, "D" AS WorkShift
FROM tblStaffSpreadDN
UNION ALL
SELECT tblStaffSpreadDN.EmployeeID, tblStaffSpreadDN.D001_Date+1 As WorkDate, tblStaffSpreadDN.D002_N as WorkHours, "N" AS WorkShift
FROM tblStaffSpreadDN;

Open in new window

SampleStaffPlan.xlsx
zinoviia660Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can open the crosstab as recordset and loop thru each field..

what are the fields in table tblStaffPlanDN?
Dale FyeOwner, Developing Solutions LLCCommented:
I think to normalize that you are going to need to create a temp table with fields like:

Date_Offset (or maybe "Work Date")
Day_Hours
Night_Hours

Or possibly:
Date_Offset
Shift:  'D' or 'N'
Hours:

With this latter structure, you could easily recreate your crosstab data format using a combination of the [Date_Offset]  and the [Shift] columns as your column header.

Then create a loop which loops through that recordset and fills that table by extracting the appropriate data from the column headers.  With the latter data structure, you would need two loops, the outer for the date offset and the inner for the shift.
zinoviia660Author Commented:
The fields are
EmployeeID (integer field)
WorkDate (date field)
WorkHours (double field)
 WorkShift  (text field)
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Dale FyeOwner, Developing Solutions LLCCommented:
The loops would look something like:
Dim intDay as integer, intShift as integer
Dim strSQL as string
Dim db as DAO.Database
set db = Currentdb 
For intDay = 1 to 100
   for intShift = 1 to 2
        strSQL = "INSERT INTO yourTempTable (EmployeeID, WorkDate, WorkShift, WorkHours) " _
               & "SELECT EmployeeID, " _
                       & "DateAdd("d", intDay - 1, [D001_Date]), " _
                       & IIF(intShift = 1, "D", "N") & ", " _
                       & "[D" & Format(intDay, "000") & IIF(intShift = 1, "_D", "_N") & "] " _
               & "FROM [yourTable]"
        db.Execute strSQL
    next
next
db.close

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
@zinoviia660,

can you post the SQL of the crosstab.
zinoviia660Author Commented:
Dale,
I think your solution is heading the right direction but I'm not sure I explained the problem correctly.
I attached a second sample of data to see if a different visual reference makes things clearer. I need to create a table that lists each craft/employee, each date, the number of hours scheduled that day and whether the shift is day or night. I need to basically reverse the Crosstab.

The difference is between this attachment and the first one I posted are Craft types instead of employee names and the dates are specifically written out.  The only way I can tell what shift someone worked on is from the column header of "D" or "N"  ("N" AS WorkShift). Instead of using a specific date in the column headers, I have used D001_D or D001_N to represent the day and the shift.
2016-SM1-Staffing-Plan.xlsx
zinoviia660Author Commented:
Rey,
I don't have the SQL for the Crosstab. The data comes from an Excel spreadsheet of the employee/craft schedule for the next several months. I import that into a table in Access. The data isn't usable to me in that format so I have to resort it into a different table.
Dale FyeOwner, Developing Solutions LLCCommented:
Now you have me totally confused.  How are you getting this into Access in the first place?

The code I provided would read the data you provided in your first example into a single table with EmployeeID, WorkDate, Shift, and Hours.  But this latest example looks nothing like the earlier data.  If you were to insert a debug.print in there to replace the db.Execute line, you would see that it is building the SQL string to create an actual date value and to extract one of the "data" columns at a time into the temp table.

And BTW, what is the difference between ManPower and ManHours?
And where does the EmployeeID come in from this latest table?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
perhaps you can create a blank database and import DEFINITION ONLY for the 2 tables you are talking about so we can see all the fields and how they are defined?

in the blank database:
EXTERNAL DATA RIBBON
Access
Select the 2 tables
click Options >
choose Definition Only

thank you
Rey Obrero (Capricorn1)Commented:
@crystal,
welcome to EE!
Dale FyeOwner, Developing Solutions LLCCommented:
@zinoviia660,

Did that answer meet your needs?  You do not have to accept an answer just because one of us gets a little forceful.

Or did you really need to read the data from the 2nd spreadsheet you sent us?  If so, that would involve automating Excel, but would not be significantly more difficult.

Dale
PatHartmanCommented:
The first spreadsheet sample can be normalized through a series of append queries.  You would only need to union them if you didn't want to save the normalized data in a table.  BTW, you can nest unions.  I've never worked with 200 queries before but I've worked with 40.  Instead of using a single union query, I broke it down to 4 union queries that unioned 10 sets each and then a final union that unioned the 4 sets.  For some reason, the query engine found this acceptable.  For this format, you can sometimes use Excel itself to pivot the recordset by converting columns to rows.  Than you can just import the file directly.

The second spreadsheet can't be easily handled with queries since it is a report rather than a table.  You would need to use something like the code solution proposed by Dale but it will be more complex because you don't have the information you need in each row to make a unique identifier.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thank you, Rey :) missed you at the Summit this year ...

nice code, Dale ~
Dale FyeOwner, Developing Solutions LLCCommented:
thanks, Crystal!

Over the years, I've become quite proficient at creating complex cross-tab queries which incorporate multiple columns in the column header, and then using Excel automation to split those column headers much the way the OP has in his/original spreadsheet.  Did this before we have Pivot queries in Access, and again, now that we don't have that capability, again.

Reversing that process just takes a little of reverse engineering.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.