Is this setup in a workable fashion?

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

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?

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

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?

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

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.  

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.

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.

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

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

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!