A dandy of a difficult denormalization problem

[usmchummer]

OK,

So here's a good one - I am trying to create the attached through a query or some VBA code.

Is the only way to do this via VBA in excel? Or is there some sort of fancy union/query that will work? It will need to create new columns for each and every new action (if it goes past 3 in this case). Or I can create a table with something like 50 "action" columns.

It kills me to do (its anti-normalization to say the least) but this is how I am being asked to provide the data.

Thanks!!!!
Russ

 

[spikepl]

Yeah. Brilliant stuff.

What do they plan to do when reaching 50 actions? Print on A0 in landscape?

 

[usmchummer]

I am assuming use some form of electron microscope when they see how small the font is when printed.

But in any case, this is what they want - so they can see the activity bridge per employee.

 

[Galaxiom]

Normalization is how you store the data. There is no rule say that you can't report it on a grid like that.

I can't imagine a query that can do it unless there is some freaky variant of a cross tab that can be done. One of our members (Stopher) has done stuff in CrossTabs that surprised me so if anyone can do it he probably can.

Otherwise I would probably do it in a loop through a recordset on the Actions table. It is a brute force approach.

First get a count of Actions for each employee and find the maximum. Use this to generate a fabricated ADO recordset. Then loop through the table recordset sorted in order of the EmployeeID and ActionDate writing to the output recordset by refering to its field index numbers in a loop.

The recordset can then be converted to Excel which would probably suit those who want this kind of layout.

 

[spikepl]

That gives me an idea. Before any coding, why not make prototype output with 50 actions in Excel, print it, and then innocently ask for verification whether that is how it's supposed to look like?

 

[usmchummer]

I will head down the road of VBA records then. I will wait a little bit to see if Stopher has any magic he can work on this, otherwise it is what it is - and I will begin the looping........

Thanks again!

 

[spikepl]

Just rambling on: on reflection, what is the purpose of this thing? To score points? A histogram would show a tally much better. Is the timeline of interest? For that plotting the accomplishments using various symbols along a time axis for each combatant would be much more clear. As is, they just get a load of text to look at.

You have my deepest sympathy

 

[usmchummer]

Lawyers.

That should say it all....

 

[spikepl]

LOL. Let me not reprint the suitable line from Shakespeare's Henry VI

 

[stopher]

Hi All

Galaxiom asked me to help…

Take a look at the attached solution (run qrySolution). It uses 4 queries to create the effect you are after. It runs in A2010 but although I’ve saved it as 2003 I haven’t tested (not sure if you can run pivots on union queries in earlier versions).

Anyway, the principle is this:

• Use Dcount to create a sequence (the 1,2,etc you see in the Action headers) - qryActionNos
• Split the Dates from the Types so that we can give them the correct headers – qryActionDates, qryActionTypes
• Union the last two queries to create a singles data set that we can crosstab – qryUnion
• Apply the crosstab – qrySolution

If you are planning to create a report, I would not use this approach at all. Instead I would create a report with a subreport for the action columns. The subreport can be set to multiple columns so that the detail show across the page rather than down thus creating the layout you want.

Hth
chris

 

[usmchummer]

Thanks much! I will test it out and let you know how it works out. I truly appreciate everyone's help

 

[usmchummer]

Your idea is great - only one problem, what if my employee id's are text? It breaks the qryActionNos query. Do I need to reassign them numeric ids for this exercise?

Thanks!!!

 

[usmchummer]

Dont worry about it - I will assign a temporary numeric identifier and VLOOKUP it to the actions table. Let you know how this works!!

 

[usmchummer]

If you are planning to create a report, I would not use this approach at all. Instead I would create a report with a subreport for the action columns. The subreport can be set to multiple columns so that the detail show across the page rather than down thus creating the layout you want.

Hth
chris

Chris,

So the query worked (took hours to run) but left blanks in some instances (IE EMP 1 ACTION 1 _______ ACTION 6 ____ ACTION 10)

How would you do this in a report/subreport? I only thought they could go vertically?

Thanks again for your help!