Merge Query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello, I have a table with fields EmplNbr, RDOSun,
RDOMon, RDOTue... (RDO = Regular Day off). The RDO
fields are Boolean, -1 is true.
I need to make a table from this data with the
following fields: EmplNbr and DayOff. So now if an
employee has two days off there will be two rows, the
employee number and the day of week number (1 = Sunday...)

Does anyone have a clue. It's beyond me.

Thanks for the help.
 
Dear Steve:

I strongly recommend you have a different table structure to perform
this function.

How about a table with EmpNbr, DayOff. Primary Key is both columns.

In this table, enter one row for each DayOff for each employee.

There will be one row in this table for each day off, as in your
existing setup.

Now, is that what you intended? Is the question how to move the data
from the existing table to the new one?

SELECT EmpNbr, 1 AS DayOff FROM OldTable WHERE RDOSun = -1
UNION ALL
SELECT EmpNbr, 2 AS DayOff FROM OldTable WHERE RDOMon = -1
UNION ALL
SELECT EmpNbr, 3 AS DayOff FROM OldTable WHERE RDOTue = -1
UNION ALL
SELECT EmpNbr, 4 AS DayOff FROM OldTable WHERE RDOWed = -1
UNION ALL
SELECT EmpNbr, 5 AS DayOff FROM OldTable WHERE RDOThu = -1
UNION ALL
SELECT EmpNbr, 6 AS DayOff FROM OldTable WHERE RDOFri = -1
UNION ALL
SELECT EmpNbr, 7 AS DayOff FROM OldTable WHERE RDOSat = -1

This should look just like the data you want to insert in the new
table. Check to make sure it does. Then save this query and base a
new Append query on that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom, Thanks for taking the time to help me. I am good at
structuring a DKNF database structure and originally had
a table with emplnbr and dayoff fields. However, I am
not yet strong in programming and I wanted the user to
have a form with their crew listed and check boxes on the
right, one for each day of the week. I could not figure
out how to transfer this type of structure into a table
with the two fields so I chose a less than desirable
table structure. If I go back to the two field structure
do you know how I can use the form with the employee
names and checkboxes? That is the real problem.

I am now reduced to using your code to make a two
attribute table which I need to do other reports... I
hate the inefficiency.

I'm home with the flu today but hopefully I can try out
the code tomorrow.

Thanks again.
 
Dear Steve:

This is to answer your one question: "If I go back to the two field
structure do you know how I can use the form with the employee names
and checkboxes?"

First of all, you need to be able to represent the data in "Day of the
Week Columnar Fashion" even though it isn't stored that way. You
could use a crosstab query for this, but I wouldn't. Rather, I would
use a query with subqueries for the days of the week. I'll call this
the "Transformation Query." This will transform the data into the 8
column appearance: EmpNbr and 7 days of the week.

SELECT EmpNbr,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 1), -1, 0) AS Sun,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 2), -1, 0) AS Mon,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 3), -1, 0) AS Tue,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 4), -1, 0) AS Wed,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 5), -1, 0) AS Thu,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 6), -1, 0) AS Fri,
IIf((EXISTS SELECT * FROM NewTable NT WHERE NT.EmpNbr = ET.EmpNbr
AND DayOff = 7), -1, 0) AS Sat
FROM EmpTable ET


This would certainly not be updatable, but only suitable for a report
or read-only form. You are probably intending the form to be updating
the data.

To do this, you need to have a temporary table in the 8 column format
into which you insert all the rows from the transformation query
above. You can then edit the table in the non-normalized form but
save the results (though a UNION query as shown before) back to a
normalized form.

This is not the sweetest operation I've seen, but it will work well in
most cases.

I have not touched yet on conflict resolution, which would be an issue
if there could be multiple users accessing this functionality
simultaneously.

We've built such things several times, but you're approaching a fairly
high level of "Expert Programming" here to do it the way we have. I
haven't heard of anyone else doing "Interactive Crosstab Forms" but
I'd love to hear other's ideas on it.

I will say that the results are quite gratifying when you finish one
of these (assuming you don't lose your hair or your job along the
way).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top