UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Up And Down Vs Side To Side, Access 2010    
 
   
nuclear_nick
post Nov 28 2017, 09:02 AM
Post#1



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


First... a polite scream...

pullhair.gif

Been working with a table for years containing 4 fields... Year, Month, Activity ID, Number.

The requested editor for the table was that it go across, so activity goes down, month goes across. Which of course generally means a crosstab for the months, and you can't update a crosstab. So I placed at the bottom input boxes (combos for month, activity, input box for number).

The new request for the editor is to not do that. "I prefer user ease-of-use over normalization," was essentially what I was told. So a table with 14 fields... Year, Activity ID, Month1Number, Month2Number, Month3Number...

I've heard/read many times that de-normalization is harder to deal with in the end... query for reports, etc. But I have no proof other than "Experts I've read say..." and the ever-so-often "In my experience..."

And the push back I get often is "Prove it." Teaching my bosses. Lovely.

Anyone have anything set up I can show with? A "bad example database" as it were? A layman's white paper?

And again... pullhair.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Nov 28 2017, 09:28 AM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Well, you can denormalize for a form, but it's a lot of extra work.

See if this demo can be helpful here.

--------------------
Go to the top of the page
 
Doug Steele
post Nov 28 2017, 09:29 AM
Post#3


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


As soon as you include data (such as which month it is) in the field name, the SQL becomes exponentially more difficult.

Write me a query that will compare each month to the value from 3 months ago.

Write me a query that gives averages by fiscal year (July - June) rather than by calendar year.

Write me a query that shows which month had the maximum value each month.

That being said, you could consider loading the normalized data into a temporary denormalized table when editing needs to be done, then update the normalized data once editing is complete.

--------------------
Go to the top of the page
 
orange999
post Nov 28 2017, 09:33 AM
Post#4



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Nick,

I don't know if this is relevant, nor whether you have seen it before, but Pat Hartman posted an example of making a normalized database look/behave much like a spreadsheet.
Here's the link to the post and attached sample.
As I said it may not be relevant, but you did say "anything".

Good luck.

--------------------
Good luck with your project!
Go to the top of the page
 
nuclear_nick
post Nov 28 2017, 09:36 AM
Post#5



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


Oh, I'm not.

My idea is to have a 'temp table', that when the year and activity is selected at the top of the form, VBA and/or SQL would fetch the data into the temp table, and then once the form was closed (or some 'update' button clicked), VBA and/or SQL would then update from the temp table into the 'normal' table.

I just... grrr... at the thought of rather than changing the culture, I'm "programming" to what the user thinks they want, thinks is easier...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
nuclear_nick
post Nov 28 2017, 09:43 AM
Post#6



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


@DS

Thank you for the example query questions... I can make those attempts and show off the 'more difficult' SQL...

@everyone

For a little more information on the original table... it's basically holding a monthly 'target'. The actuals are compared to the target for a percentage. Not saying that I'm looking for something that will fit just this example... we have several other tables...

Thank you all for the replies so far! As always...
uarulez2.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
John Vinson
post Nov 29 2017, 05:48 PM
Post#7


UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US


Ogden Nash had a poem which spoke (tangentially) to this issue:

In Baltimore there lived a boy,
He wasn't anybody's joy.
Although his name was Jabez Dawes,
His character was full of flaws.
In school he never led his classes,
He hid old ladies' reading glasses,
His mouth was open when he chewed,
And elbows to the table glued.
He stole the milk of hungry kittens,
And walked through doors marked No Admittance.
He said he acted thus because
There wasn't any Santa Claus.
Another trick that tickled Jabez
Was crying "Boo!" at little babies.
He brushed his teeth, they said in town,
Sideways instead of up and down.

So... unless you want coal in your stocking, or the equivalent in your reports, Be Good!!! ohyeah.gif

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
nuclear_nick
post Nov 30 2017, 06:27 AM
Post#8



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


@JV

Wonder if they'd notice if I put that up on the bulletin board...

(Yes, we have a cork bulletin board in the department.)

coffee1.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 05:54 PM