Question Option for Multiple Entries in One Row?

[DFlynn24]

Hi there,

I am pretty new to Access. I was wondering if there is a way to allow for multiple entries into a single row/column. For example, Some entries (people) in my database will have multiple addresses and /or phone numbers. Rather than having to have columns with "Address 1", "Address 2", etc. or having to use a new row for each subsequent address, I would like to display this data in a single cell like this:

Field Name: Phone Number(s)
555-555-0001
555-555-0002
555-555-0003

Is this possible? Thank you.

 

[MajP]

In a database you would not have multiple fields for different phone numbers. Normally you have a related table that holds multiple phone numbers for a each person related back to that person.

There is however a special field called a Multi Value Field. This will do what you say, but many people shy away from them because they can be confusing and cause problems. You can read up on them and determine if you would like to use it.

Building a related table is more standard, but that will take you some learning (but necessary learning).

 

[DFlynn24]

So if I went with a related table how would that work? Would the data from the related table (child table?) display in the parent table? I know that they would link together through a relationship but I am unsure of how one would view that data. For example:

I have a parent table with fields that include: Name, DOB, Main Phone Number, Current Address, Additional Phone Number(s), and Past Addresses.

John Smith's main phone number is 555-555-0001. I want to see all other phone numbers associated with John Smith. If his other three phone numbers are in a related table, how can I display them in that "Additional Phone Number(s)" column I mentioned above?

Please let me know if that example does not make sense.

 

[mike60smart]

Hi

Can you upload a zipped copy of your Db??

 

[DFlynn24]

Sorry, I am not able to, the data is sensitive

 

[theDBguy]

So if I went with a related table how would that work? Would the data from the related table (child table?) display in the parent table? I know that they would link together through a relationship but I am unsure of how one would view that data. For example:

I have a parent table with fields that include: Name, DOB, Main Phone Number, Current Address, Additional Phone Number(s), and Past Addresses.

John Smith's main phone number is 555-555-0001. I want to see all other phone numbers associated with John Smith. If his other three phone numbers are in a related table, how can I display them in that "Additional Phone Number(s)" column I mentioned above?

Please let me know if that example does not make sense.

We recommend all user interactions (viewing and updating) be done using forms. So, I would try to avoid thinking how to "see" the related information in tables (although you can). If you use forms for everything, you have total control what the user can see or do. To display related records on a form, you can use a subform control.

 

[DFlynn24]

So is it possible to nest multiple tables into a single form? For example, if I had an "Identifying Information" table, a "Phone Number" table, and an "Address" table, could I have them built into one form so the user would not have to navigate three different pages?

Also, if someone wanted to go back and query this information to display/export, would I just have to build one using the Query Wizard?

 

[theDBguy]

So is it possible to nest multiple tables into a single form? For example, if I had an "Identifying Information" table, a "Phone Number" table, and an "Address" table, could I have them built into one form so the user would not have to navigate three different pages?

Also, if someone wanted to go back and query this information to display/export, would I just have to build one using the Query Wizard?

For the first question, re multiple tables in one form, I would not recommend this. You can still display multiple subforms on a form without using pages as long as you have enough room on the form.
As for the second question, re creating query to display/export data, yes, you can create a single query joining all related tables for your report or to export the data into Excel or Text file.

 

[Cronk]

So is it possible to nest multiple tables into a single form?

Data from multiple tables is shown in multiple sub forms on a parent form.


You can see examples of this and other Access features in the Microsoft Northwind sample database. Instructions from MS's site are below for installing Northwind.


I don't think Northwind has an example of multiple phone numbers for a person, you can see something similar by opening up an order. Open Northwind and click on 'View Orders' on the right hand side. Double click on any order on the form that opens to see an example.




Get the Northwind sample database for Microsoft Access

The Northwind sample database for Microsoft Access is not available on the Microsoft Download Center. To install Northwind directly from within Access, do the following things:

1. Open Access.
2. Enter Northwind in the Search for Online Templates box, and then select Enter.
3. On the results screen, select Northwind. A new window opens with a description of the Northwind database.
4. In the new window, in the File Name text box, provide a filename for your copy of the Northwind database.
5. Select Create. Access downloads the Northwind database and prepares the file.
6. When this process is complete, the database opens with a Welcome screen.

 

[mike60smart]

Hi

Attached is an example of a Subform for Multiple Phone Numbers

View attachment MultiPhones.zip

 

[DFlynn24]

Thank you all for your replies and patience with my questions. I will take a look at the most recent replies and report back with any success!

 

[The_Doc_Man]

Since you are new, may I suggest some reading?

You need to understand "database normalization" as a way to keep your data organized in a neat and clean manner. This will include learning about child tables.

You have a second question about whether you would see multiple addresses if you had them for a single person. Read up on "JOIN queries" as a way to find all child records related to a given parent (and learn about the "GROUP BY" option for such joined tables.)

 

[gemma-the-husky]

The trouble is with putting several phone numbers in a list, is that it becomes very difficult to see ALL the phone numbers you have, or search for a number, because of the way the data is structured. Now it depends what you want to do with the phone numbers. If you just need to view them, it may be fine. If you want to use an auto-dialler it won't. If you want to automatically include just one of the phone numbers in a form or report, it's tricky, because you don't necessarily know which one you want, and it's a bit tricky to extract one of them from the list.

On the other hand it's easy to just create a single field, rather than faffing about with a second table. so it's a matter of choice, knowing the pros and cons.

To avoid having subsidiary tables, MS Access brought in something called a MVF (multi-value field) which does what you want, but does all the management behind the scenes. A lot of advisors recommend not using these, as it is non-standard database technology, and would prevent you upsizing your data to, say, SQL Server, without fixing this. But this would also do what you want, and might also be an alternative.

As others have said, adding columns such as phone1, phone2, phone3 or address 1, address2, address3, also presents similar problems to having multiple values in a single field, and makes searching for a record difficult, because you are having to search multiple columns. Do-able, but not recommended, as it's spreadsheet thinking (short-wide tables) rather than database-thinking (long-thin tables)

good luck.

 

[Cronk]

Dave, my smart phone provides for multiple phone numbers for a contact. I've done several Access apps that had need to store multiple phone numbers - home, work, mobile, emergency contact, secondary emergency contact and so on.

Where there has been a need to indicate the primary number, I've added a true/false field to indicate this.


My point is not to disagree with you but rather that, for me, the requirements determine what is stored and how. And I might even not have a child table of phone numbers if I was very sure there would never be more than one or two phone numbers.


But to each, his own.

 

[gemma-the-husky]

@cronk

I agree with you, I said it was a matter of taste, knowing the pros and cons.