Help Required with Article (1 Viewer)

Uncle Gizmo · Sep 28, 2017

I would be grateful for a bit of help folks. I am writing a Blog on the difficulties of extracting data from incorrectly constructed tables, or in other words data that doesn't conform to first normal second normal and third normal. If you happen upon, or have seen a recent post here in which the user has provided a sample database which nicely demonstrates this problem, then if you could point me to it I would be most grateful….

Sent from my SM-G925F using Tapatalk

isladogs · Sep 28, 2017

Happy to help but I'm just wondering whether it would be better to do so via private messages rather than identifying a thread called xxx by a user called zzz.
Just a thought

The_Doc_Man · Sep 28, 2017

Concur with Colin. Wouldn't want to embarrass someone by publicly stating that Joe Scheisskopf make a rookie mistake. If I see anything, will PM you.

Lightwave · Sep 29, 2017

I regularly come across plenty of enterprise grade applications with poor or sub optimal normalisation. Often understandably as they are legacy applications which have been continually developed over 20 years by multiple developers each with their own ideas.

One of the reasons why in my opinion and in a perfect world, big applications should be periodically redesigned from the ground up to take on board new things.

A significant number of government organisations are sitting with applications in databases that could be spatially enabled but because they were designed prior to spatial data types being available in the backends most applications have geography tacked on at the end. Now that most of the databases have these data types available they just sit being unused. Someone is going to come along and knock it out of the park.

New entrants are coming into the market with geography built in from the ground up and really threatening the resident systems often the main improvement is improved normalisation.

Uncle Gizmo · Oct 3, 2017

Thank you for your comments suggestions and links --- cheers Tony

AnthonyGerrard · Oct 3, 2017

Uncle Gizmo said:
Thank you for your comments suggestions and links --- cheers Tony

I wonder - (im expanding your remit!) if you could throw in a bit of dataware housing complexity into it?

Uncle Gizmo · Oct 3, 2017

AnthonyGerrard said:
I wonder - (im expanding your remit!) if you could throw in a bit of data warehousing complexity into it?

I presume you mean swap out the bulky text and things into lookup tables to reduce the amount of data?

isladogs · Oct 3, 2017

So its not just me who didn't really understand AG's request....
Still you had more idea than I did, Tony

MarkK · Oct 3, 2017

Lightwave said:
...legacy applications which have been continually developed over 20 years by multiple developers each with their own ideas.

Here's tweet you might like...

https://twitter.com/codewisdom/status/831588572310409216?lang=en

"We build our computer (systems) the way we build our cities: over time, without a plan, on top of ruins." - Ellen Ullman

Pat Hartman · Oct 4, 2017

I ran into one on a different forum where the poster was asking how to name columns with month ending dates such as "10/31/17". He had multi-million row tables which he wanted to summarize and he thought he would just create a table with 240 columns to handle 20 year's worth of data. The scary part was that two experts were advising him on a naming standard and neither even mentioned that this was probably the worst idea for a table design posted this year. The posters plan was to add a new column every month and populate it from the base data. There was no db attached so I didn't send a link but my head is still spinning. I'm not sure that I have convinced him to go with a normalized schema but I tried.

Uncle Gizmo · Oct 4, 2017

Pat Hartman said:
....... I'm not sure that I have convinced him to go with a normalized schema but I tried.

I see that here all the time, someone comes in with a problem, due mainly to the construction of the database. I've noticed, often, three or four people will weigh in and reinforce the message.

There are two problems in convincing someone.. The first is it's difficult to see the benefits. The second is very often they've got a lot of time and effort invested in the position they find themselves in. I distinctly remember having done something similar myself when I was first learning!

It's quite easy to forget how long it took you to learn something. However recently I have had my eyes opened as I am trying to learn JavaScript. In essence JavaScript looks quite simple, it's basically the same as VBA you've got the same sort of structures.

However I've noticed there is something different about it, and I can't find any instructions on how to approach it. I think it's a bit like the concept of a collection in VBA. In Javascript (GAS) if you wanted to do something to a column of data in a Google spreadsheet you would access the whole set of Records and operate on them in one go, I can't for the life of me work out how the code works that does that!

I mention this because it's my struggling with JavaScript that makes me realise that MS Access isn't particularly easy to learn and I think it pays to bear that in mind when you answer people's questions.

Pat Hartman · Oct 5, 2017

It doesn't surprise me when users come up with designs like that one. What do they know about design? But to have two experts put bullets in his gun was hard to get past.

Each development environment has a gestalt to it and once you "get it", I'm sure you'll be productive. I learned over 20 years ago that Access was smarter than I was and "resistance was futile". Once I figured out the "Access way", the amount of code I needed to write dropped dramatically and I came to appreciate the pure genius of the original Access team. Some of the worst applications I've had to modify were developed by experienced programmers who were trying to bend Access to how they thought it should work rather than going with the flow.

Lightwave · Oct 5, 2017

Pat - I'm totally with you on the superb design of Access.

The report writer is simply outstanding - I m doing quite a bit in Crystal Reports at the moment. Its limited and the UI is just poor compared with Access. Yes you can do everything but I can't see myself ever being as quick as in access.

I've never really needed to touch SSRS as a result.

The combination of having pure SQL Editor / A rock Solid UI designer / A really quite powerful database AND a Full featured Code Editor wrapped up in a single program that deals with nearly all configuration and then having the opportunity to finely adjust configuration to include enterprise database backends as well is just outstanding. I would love to meet some of the original designers.

I think many went on to work on SQL Server

Uncle Gizmo · Oct 5, 2017

I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

isladogs · Oct 5, 2017

Uncle Gizmo said:
I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

Good idea. Go for it.
Saw you'd self censored the last sentence but its there in my notification email!!!

Uncle Gizmo · Oct 5, 2017

ridders said:
Good idea. Go for it.
Saw you'd self censored the last sentence but its there in my notification email!!!

I had second thoughts... It might be a spam magnet... Who's going to decide what should be allowed.... On the other hand this site is basically self-regulating so it should sort itself out.. Anyway I'm getting too deep into it!

Lightwave · Oct 5, 2017

Uncle Gizmo said:
I was looking at your blog http://rounduptheusualsuspects.org/ the other day Mark, excellent stuff you've got on there. I've already posted one of your articles to my Facebook group... I note that Pbaldy Paul http://www.baldyweb.com/ has also got a good website. I wondered if we if I ought to start the separate thread where people can post a link to their blog? I should mention I do have one of my own I'd like to promote!

Hi yes UG have visited your sites regularly - I am happy with my tag having a link to my blog I think overtime you will find that people will find sites if they are good mine is mostly visited by me but if you want to start a thread I don't think anyone would object.

gemma-the-husky · Oct 5, 2017

Uncle Gizmo said:
I see that here all the time, someone comes in with a problem, due mainly to the construction of the database. I've noticed, often, three or four people will weigh in and reinforce the message.

There are two problems in convincing someone.. The first is it's difficult to see the benefits. The second is very often they've got a lot of time and effort invested in the position they find themselves in. I distinctly remember having done something similar myself when I was first learning!

It's quite easy to forget how long it took you to learn something. However recently I have had my eyes opened as I am trying to learn JavaScript. In essence JavaScript looks quite simple, it's basically the same as VBA you've got the same sort of structures.

However I've noticed there is something different about it, and I can't find any instructions on how to approach it. I think it's a bit like the concept of a collection in VBA. In Javascript (GAS) if you wanted to do something to a column of data in a Google spreadsheet you would access the whole set of Records and operate on them in one go, I can't for the life of me work out how the code works that does that!

I mention this because it's my struggling with JavaScript that makes me realise that MS Access isn't particularly easy to learn and I think it pays to bear that in mind when you answer people's questions.

Uncle Giz.

The points you are making here remind me of an analogy I often use for Access. There isn't a learning curve. It's more of a learning staircase. You get to a cliff wall (the next step), and all you need is the "lightbulb" moment - someone shows you how/why to get to next level. Once you know that particular trick, you never forget it.

It reminds me of trying to follow an MS issued CD library when I was just learning. They had total album times, and I couldn't work out how they were summing the track times. It turned out they weren't - they just had an extra field for the album total.

Help Required with Article (1 Viewer)

Uncle Gizmo

Nifty Access Guy

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

Lightwave

Ad astra

Uncle Gizmo

Nifty Access Guy

AnthonyGerrard

Registered User.

Uncle Gizmo

Nifty Access Guy

isladogs

MVP / VIP

MarkK

bit cruncher

Pat Hartman

Super Moderator

Uncle Gizmo

Nifty Access Guy

Pat Hartman

Super Moderator

Lightwave

Ad astra

Uncle Gizmo

Nifty Access Guy

isladogs

MVP / VIP

Uncle Gizmo

Nifty Access Guy

Lightwave

Ad astra

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread