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    
post Nov 28 2017, 09:02 AM

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

First... a polite scream...


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
Go to the top of the page
post Nov 28 2017, 09:28 AM

UA Admin
Posts: 34,106
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

UtterAccess VIP
Posts: 22,002
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
post Nov 28 2017, 09:33 AM

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


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.
Go to the top of the page
post Nov 28 2017, 09:36 AM

Posts: 1,662
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...
Go to the top of the page
post Nov 28 2017, 09:43 AM

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


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


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...
Go to the top of the page
John Vinson
post Nov 29 2017, 05:48 PM

UtterAccess VIP
Posts: 4,275
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
Go to the top of the page
post Nov 30 2017, 06:27 AM

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


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

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

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    13th December 2018 - 07:36 PM