Is this setup in a workable fashion?

daisypetals313
daisypetals313 used Ask the Experts™
on
Hi - before I start asking how to do things, first I would like to ask if this database is setup in such a way that would even allow it to be worked with.

I have tables that are linked from a master database so I cannot change how they are setup.  A sample is attached.  Please note how the Main table appears normal (with one ID number per row) and the other Phone Number table has the ID numbers on multiple rows with various phone numbers.

I am trying to make a query where everything is NORMAL (meaning one ID number per row and all the information on that one row).  Everything I try is creating a mess.  It creates multiple rows row each ID number OR for those that dont have a phone number it doesn't show them at all.  This is a mess!  Are databases even supposed to be made like the Master it is feeding from?  Please see sample attached.
Sample2.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
This is because you have "Normalized out" the phone numbers.
That in itself is a good thing.

But why do you need this "One Row View"?
(IMHO, I would not refer to this as "Normal")

The relationship between the Main table and the PhoneNumber table is One to many, not one to one.

That is what makes this difficult.

Can it be done?... sure, but it will require a fair amount of work on the Experts part, and it may require you to be familiar with VBA and possibly SQL.

What is your skill level with VBA and/or SQL?

JeffCoachman

Author

Commented:
I would like the "One Row View" so that I can work with this data....for example have a form that shows all the data on one sheet rather than having it spread over multiple repeatedly, I would like to print a report that shows a person with their name/address/phone number on one line instead of spread over multiple, I would like to make queries that do simple things like count records and insead of showing 30 people in one city because everything repeats it would show the correct number of 10.    How on earth do people work with data like this?  My VBA and SQL is crap so unless I can find a way to have a nice traditional little database setup like the below:
1  John  Doe  123 Main Street  City State 555-555-5555 666-666-6666 777-777-7777

Instead of:
1  John  Doe  123 Main Street  City State 555-555-5555
1  John  Doe  123 Main Street  City State 666-666-6666
1  John  Doe  123 Main Street  City State 666-666-6666

Am I out of luck?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The typical way of doing this is with a Main Form/SubForm, or with a Main/Sub Report.

It will looks something like this:
1  John  Doe  123 Main Street  City State
555-555-5555
666-666-6666
777-777-7777
This is "easy", will take about 1 minute, and again, is the standard way of displaying this type of related data.
This can be formatted, but the Phone numbers will be Vertical, not horizontal

If you really need it "in one row" then it will be "challenging"
It must be in a Report, not a Query
It will require you to modify the code I will present, to work in your database.

How would you like to proceede?

JeffCoachman


Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
boag200 - thanks so much for the above, however, that doesn't really solve the entire issue (i think).  the examples above will solve the form view perfectly, however, im looking for a universal solution rather than item by item fix.  unfortunately, i simply cannot work with the data and create the queries/count/reports when the data is in multiple tables like this.  our most important function is creating queries.  this simply will not be possible unless it is in one table.  how much of a challenge are we looking at here to do this?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Again, the Main/Sub Technique is the standard way this data is typically displayed.

Queries can easily be created with relational data.

<this simply will not be possible unless it is in one table.>
That's a pretty strong proclamation.
Are you quite sure that it is "Not Possible"?
Or, ...are you saying that you may not know "how" it is possible?

I and all of my fellow Experts here never had an issue with querying related data.
Finally, in 5 years I have been on this site I have never had this question come up.

Can you post a "specifc example" of the type of query you are trying to create that is "impossible" to do with related data?

Thanks

JeffCoachman

Author

Commented:
Yes you raised a good point..it's that I do not know how to work with the data when it is setup this way.  I would like to have the database with everything in rows because it will be a more universal way for me to work.  An example of where I am having trouble...in the sample attached above, when I make a query to show only people named John it returns multiple records rather than just one.  When I do a count, the count is not accurate.  My reports do not appear in the way we are accustomed to viewing which is going to through off the users.  I guess I could think of a million different examples where having the table with one row per record instead of multiple repeating rows would be easier to work with.  Maybe I am not explaining the right, but I see a huge different between having 30,000 records with everything on one line as opposed to 150,000 records of repeating information.  Yes, true, I cannot work this way.  Is there a way to create a query so it will just show a database where the ID for each record is only on one line with all the info?  Then I could use these query almost like it was the Master and do the stuff I need to do with it.
Hi Daisy,

I remember being in your position not to long ago.  You want to work with the data in more of spreadsheet format.  The issue is for what you want is not natively possible with Access because the phone numbers are all carried in on field and the related field is repeated (person's name).  You are unfortunately stuck.  The tables are setup in a typical relational database format.  This format works well if lets say you wanted to know how many phone numbers there are to each unique name.  For counting and grouping you issue maybe stemming from how you "join" the tables.  I would recommend doing a google search on it plenty of free information out there.    There should be a way to get the information you need natively just not in the fashion you are used to and not with a master table in that format.
unless I can find a way to have a nice traditional little database setup like the below:
What you are asking for is not at all a traditional way to set up a database.  It is, however, a traditional way to set up an Exccel spreadhseet (and, contrary to what you may think, Excel is not a database application ;-).
How are you planning on using this query?  Is it for a report or are you planning on trying to set up a data entry form?
 
You could set up the main form with the person information and a (continuous) subform with the records associated with the person being shown/selected.  

Author

Commented:
jstadler, yes you explained my issue the way i was trying to...and yes 8080_diver, i am aware Excel is not a database application, and I am by no means a computer expert, but i dont see how this relationship stuff is not super limiting.    for something simple, like counting records in a certain city, the correct answer could be 800 but it would show as many as 2400 count.  or how would you even do things to find duplicates when every record is duplicated now.  Sorry not to be rude, but I will never be sold on this relational stuff working for me...so experts out there...is it possible to have that "spreadsheet" view or am i truly stuck?  And thanks for the suggestions everyone for how to do certain tasks (such as setting up the form), but I was hoping for a universal fix (i.e. get that "spreadsheet" view) as opposed to individual bandaids for every task.  And i am totally lost as to how a query would work...my query has each person multiple times...so when I try to query out my mailing list based on various factors i have poor john doe getting 3 emails for no reason other than he has a home, cell and work number.  boo.
MIS Liason
Most Valuable Expert 2012
Commented:
Ok, here is the deal.

Now this post is rather long and meant to be humorous as well as informative.
It is littered with "Smileys", so as not to be taken as being offensive.
Mkay?
;-)



Your structure itself prevents this from being easy.
Even what I was going to propose would have been what you have called a "Band-Aid"

As you stated:
<I have tables that are linked from a master database so I cannot change how they are setup.>

So when you say:
"This is not the way I need the data"

Then it is clear that you have been working with Flat file Excel type tables.

But as the other experts have pointed out:
This is Access, play time is over, and you are in the big leagues now.
;-)
You have to change your thinking and learn new techniques for querying relational data.
It may not be easy or fun, but it is part of the learning process.

Insisting that you be able to manipulate related data in Access like a flat file, is like an Adult insisting that the training wheels be left on their bicycle, because it is "easier" and "The way I am used to"
;-)

I know of no adults still riding bikes with training wheels.
I know of no Access developer who needs or wants "Flat files", (the way you are wanting them), magically created from related data.

Again, you are the first person in over 3,500 questions I have answered who has ever taken issue with this.
Millions of people go about there lives querying related data using the standard techniques and none of them need any "training wheels"
;-)
As a matter of fact, every one of your scenarios can be remedied, if only you would just take the time to look into these different techniques and queries.
Again, it may not be simple, but it is part of the learning process.
For example:
If you do the query "Right", John Doe will get only one email, not three.
(no training wheels required)
See the attached sample:
Again, not simple, but I am a Big boy who took off his Access "Training Wheels" 7 years ago.
;-)


(Can I ask if database development is something that you have no interest in, because it was simply "Thrust upon you"?)


If this is your point of view, then you will be fighting with this issue for your entire career in Access and relational databases.

For example, in this question you appear to be "Stuck" as you say.

So.....
How will you accomplish what you are after if it is more or less impossible?
The data structure cannot be changed, and you cant get the view you are after.
So where does that leave you?
You appear to be at an impasse
What will you do?
I'm curious?
;-)


Please take a moment to think over the advice we experts have given you.
Take the entire weekend if you like.

Then post back with you final thoughts.
;-)

Respectfully,

JeffCoachman

Access-BasicSampleEmailIndividua.mdb
To answer your basic question:
is it possible to have that "spreadsheet" view or am i truly stuck?
The only solution that you appear to willing to accept is to export the data and put it into an Excel spreadhsheet, then trash your database.
 for something simple, like counting records in a certain city, the correct answer could be 800 but it would show as many as 2400 count
 The answer is that you have to learn how to write SQL better.  Even the Query Wizard in Access makes it failry easy to accomplish this if you get over your determination to not have it work.  Change is a constant in IT and you have 2 basic choices: Change or become obsolete.  Flat files are somewhere between endangered as a species and extinct . . . those who insist on working only with flat files are in the same basic category.
or how would you even do things to find duplicates when every record is duplicated now
 Well, the first thing is that every record is not duplicated in the tables you indicated.  The second thing is that, like many aspects of life, if you don't plan ahead you can find yourself at the dark end of a very long dead-end tunnel where your only choice is to go a long way back and start over.  In the world of relational databases, failure to plan ahead (and to properly normalize your tables) means that you may have to totally redesign the database.
In your case, though, the database tables that you presented, while quite sparse, are properly normalized and can be used in rational, relational ways.  That does not mean that you can get an Excel spreadsheet view of the data in an easy manner, though.
so when I try to query out my mailing list based on various factors i have poor john doe getting 3 emails for no reason other than he has a home, cell and work number.  boo.
 Well, if you are querying for a mailing list, why do you need a phone number at all?  If you are querying for a phone list, then you probably need to have another column added to your PhoneNumber table so that you can indicate the "Preferred Contact Number" )ever see that check box on a web form when you are supplying phone numbers and/or addresses and/or email addresses? ;-).  Like I said, plan ahead and you cans solve the problem.  If you don't plan ahead, then you may have to retrofit solutions, such as getting that "Preferred Contact" Yes/No column added to the table.
If you have had this task thrust upon you, you have three basic choices:
  1. You can march yourself right into the office of whoever assigned this to you and tell them that you won't do it (which may be a questionable tactic in toay's economy ;-);
  2. You can fight the relational concept as hard and as long as you can and, in the process, not only raise your blood pressure, increase your resentment at being assigned this task in the first place, go home angry and frustrated every evening, wak up dreading going to work, and, in general, do a really lousy job at work;
  3. Accept that you now have to change the way you approach problems and the way you think about the data you are working with and decide that you can and will learn to work with relational data, that you can and will plan ahead and think through the task before embarking upon addressing the task, that you can and will acquire the skills necessary to do the job as now defined.
If you need to get some training on working with Access, then ask for it and, if your supervisor won't supply it, then check out community colleged and their Continuing Ed courses (most of them will have a couple of Access classses for a reasonable price).

Author

Commented:
All - thanks for the above and I do mean that...I agree with you on all points and if I were an IT person I would gladly embark on the above.  The truth is I am in a totally different field in a small company and I take on extra tasks (that while I am not expert at) am the only one even remotely close to being able to do in this particular office.  Unfortunately I do not have time to learn SQL to this level with a deadline looming.    I agree with everyone that technically everything you are saying is correct and makes sense and it can work if a lot is learned etc. etc. etc. etc. , however, for me when this much extra work needs to be done for the simplest of tasks then the cost/benefit of the specific project I am trying to do does not align with it.  Again, thanks for the background info above and if someone wants to take a stab at answering my original question that would be great.  If not I'll wait a few days and award points to somewhere for the closest response above, which is only fair so as not leave what seems to have been an odd question hanging.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
daisypetals313,

Great.
I just want to say "Thank You" for not taking our comments offensively.
(and for keeping your sense of humor:  <boo...> )
;-)
They are not meant to be offensive, only enlightening, and you seem to realize that.
;-)

That's good.

There are many times that we as experts get asker who want it: "their way or no way!"

At least you understand what needs to be done.

So for keeping your cool while still arguing your point, I again say, Thanks.

I wish you all the best in the future.


As far as the points are concerned, I feel that they should be split between all participating experts.
This post would be a great resource to anyone with the same issues or concerns.
The fact that this thread never got "Ugly", can serve as an example to others that the exchanges between Expert and Asker need not be "adversarial"

;-)

Jeff
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
8080_Diver,

Congratulations on deciding to become an Expert here.

I can see from the quality of your post that you are knowledgable, and can communicate your points well.

;-)

Jeff

Author

Commented:
Thank you experts for taking so much time on my question...Sometimes the answer is that there isn't an answer (or at least not the expected answer), and that has saved me much time and helped push me in the right direction.  Oh and I never knew I could split points until this question so thanks for that too :)  Cheers!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial