A dandy of a difficult denormalization problem

U

usmchummer

New member
Local time
Today, 07:29
Joined
Jan 4, 2012
Messages
8
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
 

Attachments

Yeah. Brilliant stuff. :p

What do they plan to do when reaching 50 actions? Print on A0 in landscape?
 
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.
 
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.
 
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?
biggrin.gif
 
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!
 
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 :)
 
LOL. Let me not reprint the suitable line from Shakespeare's Henry VI :-)
 
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
 

Attachments

Thanks much! I will test it out and let you know how it works out. I truly appreciate everyone's help
 
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!!!
 
Dont worry about it - I will assign a temporary numeric identifier and VLOOKUP it to the actions table. Let you know how this works!!
 
stopher said:
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
Click to expand...


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!
 
You must log in or register to reply here.

Similar threads

C
Update Query using SWITCH function
Replies
13
Views
528
GPGeorge
GPGeorge
109bow
Solved filter results in a query
Replies
3
Views
325
109bow
109bow
B
Pad a column alias' with a leading space.
Replies
9
Views
528
Pat Hartman
P
109bow
Count number of rows depending on criteria
Replies
4
Views
350
109bow
109bow
S
Solved Dynamic CrossTab Query (Conditional Sum)
Replies
12
Views
621
stinger
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom