Tables Versus Queries (1 Viewer)

mkaeser · Mar 30, 2015

Hello all,

I am redesigning an access database, but I have no formal training so I am not sure what it the best practice to use here. Currently, there is a new data entry form with a record source of a selection query. When new data is entered, it is split between two tables (the two that are being selected in the query). From there, this data takes on a snow ball effect, where the data is selected from tables and appended to other tables. The forms are based on queries based on queries based on tables. It all seems convoluted and takes a while to find your way back to the source to make any changes. This doesn't seem right, but like I said, I don't have formal training. It seems there are more tables then needed, since a lot of the tables have the same information.

So my question is, is there a reason why you would want to make a new table to house data as opposed to using a selection query to pull the data you want? Wouldn't it cause database bloat to have tables over queries?

KenHigg · Mar 30, 2015

You are correct. As a rule of thumb you should not save query results in tables...

spikepl · Mar 30, 2015

It is not a question of tables vs queries but of using the right tool for the right job. Your instincts are right - you do not just copy data all over ...

You need to get some basics sorted out before proceding: http://www.access-programmers.co.uk/forums/showpost.php?p=1419416&postcount=7

Do follow the linked-to tutorial .. and see some vids

ButtonMoon · Mar 30, 2015

spikepl said:
You need to get some basics sorted out before proceding: http://www.access-programmers.co.uk/forums/showpost.php?p=1419416&postcount=7

Do follow the linked-to tutorial .. and see some vids

You might instead like to read these comments on those very same videos, and then perhaps take a look at some alternative learning resources.
http://www.access-programmers.co.uk/forums/showpost.php?p=1419465&postcount=11
http://www.access-programmers.co.uk/forums/showpost.php?p=1419474&postcount=12

mkaeser · Mar 30, 2015

BEAUTIFUL thank you this is very helpful!

mkaeser · Mar 30, 2015

ButtonMoon said:
You might instead like to read these comments on those very same videos, and then perhaps take a look at some alternative learning resources.
http://www.access-programmers.co.uk/forums/showpost.php?p=1419465&postcount=11
http://www.access-programmers.co.uk/forums/showpost.php?p=1419474&postcount=12

Ok now I'm back to the same question hahaha, thank you for the other opinion ButtonMoon.

jdraw · Mar 31, 2015

Perhaps you should read this post as well

KenHigg · Mar 31, 2015

Agreed good starting points. But:

a. You can normalize too much sometimes. A lot depends on the deployment.
b. Somebody (read between the lines) needs to walk the business process(s) and understand and collect the business requirements, making it a point to not even mention a computer or database.

mkaeser · Mar 31, 2015

All good information I appreciate it. I would LOVE to walk through the business plan before even touching the computer. Problem is, the BUSINESS doesn't even know what it is. I am running off the way things WERE done, guessing how things SHOULD be done as they are changing the way their business process flows. I have their old access DB, well actually, there is a total of 5 DBs. The person who designed these (my guess) didn't have much experience, so employee have to re-enter the same information into all these systems as there is no link. So, I am trying to wade my way through the first of the 5 (the most complete, in my opinion) and "salvage" what I can, but the way the DB is currently set up would call for several tables with the same information.

KenHigg · Mar 31, 2015

Ugh... The worst part is they probably want their data from the old, poorly designed database to work with the new stuff...

mkaeser · Mar 31, 2015

KenHigg said:
Ugh... The worst part is they probably want their data from the old, poorly designed database to work with the new stuff...

That, and this frequent conversation:
"Do you use this form for anything?"
"No."
"Has anyone used it recently?"
"I don't think we have ever used it."
"Do you know why it was made?"
"No, I haven't even seen it before."
"Does anyone know why it was made?"
"Nope."
"Ok, well I'm going to get rid of it then."
"No, don't do that!"
"Why? You don't use it!"
"But we might need it!"
"But.....you......fine...." :banghead:

CJ_London · Mar 31, 2015

So true, mine go like this now

That, and this frequent conversation:
"Do you use this form for anything?"
"No."
"Has anyone used it recently?"
"I don't think we have ever used it."
"Do you know why it was made?"
"No, I haven't even seen it before."
"Does anyone know why it was made?"
"Nope."

then - say nothing, keep a backup and remove or hide

jdraw · Apr 1, 2015

I have described the "stump the model" approach, and I'm sure there are a number of variations of it. You do have to have something for people to look at and participate. I hear you though, I've seen business people so sure of their "absolutely golden contact lists" that they wouldn't hear of change. After some analysis, we found some of the contacts had been retired or dead for years; some businesses out of business for 8+ years.
One technique is to make a glaring error(on purpose) and have someone correct you, just to get a dialog started. And don't overlook the option of asking , "... and how does that work exactly?... and who uses that...? Or what does that group do..?"

Good luck.

Tables Versus Queries (1 Viewer)

mkaeser

Registered User.

KenHigg

Registered User

spikepl

Eledittingent Beliped

ButtonMoon

Registered User.

mkaeser

Registered User.

mkaeser

Registered User.

jdraw

Super Moderator

KenHigg

Registered User

mkaeser

Registered User.

KenHigg

Registered User

mkaeser

Registered User.

CJ_London

Super Moderator

jdraw

Super Moderator

Similar threads

Users who are viewing this thread