Inputting multiple numbers in one field 2

[Trazzel]

How do you data enter multiple numbers in one field for one person? For example, if an employee is responsible for several standards how would you input "Employee A has oversight of standards 1,7,9?" The field needs to be searchable.

Thank you.

 

[Remou]

In general, you wouldn't. You would have a table EmploeeStandards:

[tt]EmployeeID ) Combined PK
StandardCode )

Data:
a 1
a 7
a 9
b 4
c 9
b 2[tt]

You might like to read

http://r937.com/relational.html

http://lessthandot.com

 

[MajP]

In Access 2007 they introduced a concept of multivalued fields (it is a really bad idea, but they did it).

http://office.microsoft.com/en-us/access/HA012337221033.aspx

 

[PHV]

MajP,
AFAIK this new data type was introduced only for Sharepoint compatibility.

 

[MajP]

PHV,
Maybe the introduced it for compatibility with Sharepoint lists, but it there is nothing precluding a user from using them in there stand alone apps.

"In most database management systems you can store only a single value in a field, including earlier versions of Microsoft Access. In Microsoft Office Access 2007, you can also create a field that holds multiple values, such as a list of categories to which you have assigned an issue. Multivalued fields are appropriate for certain situations, such as when you use Office Access 2007 to work with information stored in a Microsoft Windows SharePoint Services 3.0 list, and that list contains a field that uses one of the multivalued field types available in Windows SharePoint Services"

The interesting thing it is actually normalized

"You might wonder why Office Access 2007 allows you to store more than one value in a field, when most relational database management systems forbid this. The answer is that the database engine in Office Access 2007 doesn't actually store the values in a single field. Even though what you see and work with appears to be a single field, the values are actually stored independently and managed in hidden, system tables. The Access database engine handles this for you, automatically separating the data and bringing it back together again to surface the values in one field.

 

[Trazzel]

Thank you for your answers. I thought that was the way that I would have to make the table, but I was so hoping that access would have made it simpler over the years.

 

[MajP]

but I was so hoping that access would have made it simpler over the years"
???
I think I showed conclusively that in fact they have.

 

[lespaul]

the structure suggested above for the EmployeeStandards table follows the normalization rules for designing a one to many relationship...that's the way it's supposed to be done.


Leslie

 

[MajP]

Leslie,
As I pointed out multi valued fields also follow the normalization rules, it just happens behind the scenes. As I stated, I would not recommend it just like I would never use lookups in a table. But it is normalized, and provides an easy (debatable) means of doing a one to many.

 

[Remou]

If you are familiar with design, you will not use it, if you are not familiar, you should not use it, because it means you don't really know what's going on.

http://lessthandot.com

 

[SkipVought]

If you are familiar with design, you will not use it, if you are not familiar, you should not use it, because it means you don't really know what's going on.

[red]
Brilliant!!![/red]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

Well I appear to be the only one familiar with the concept, and the bottom line is people are likely to use it more and more. If you have been following the trend in 2007 and looking at the capabilities in 2010 (which are pretty impressive), then you need to be aware of this. Why because clearly MS sees Sharepoint as the backbone for small buisness information exchange. In the last year in our company, I have not seen a stand alone Access App that is not integrated with Sharepoint.
So because of this trend, MS will make features like this easier to use, like it or not. I have seen a demo for a native many to many relationship, as part of future Access builds. Take a look at macros. Couple years back it was said that MS would deprecate them. Instead all the new wizards do only macros.
So all I am stating are the facts, and because of integration with Sharepoint lists, I really think you will be seeing this more, and need to be aware:
1. MS supports multivalued fields
2. These fields work integrated with SP or stand alone
3. The structure is normal
4. This structure is normalized behind the scenes in system tables.
5. "that's the way it's supposed to be done". Maybe up until now, and outside of Access. But this technique or some version of this may become the norm in the future. MS is not pushing this, but presenting this as a viable alternative.

It is the same thing with attachment data types. In 2007 Access handles the attachment datatype vastly different than prior OLE objects. The new attachments are far more efficient. I see MVPs all the time saying not to put images or other objects in the db. But again that paradigm is changing.

Bottom line you can decide to use or not use any of these features. However, I would not deny their existence hoping they will go away. I strongly believe as a Access developer/tech user you need to be aware in the migration to Sharepoint integration and its ramifications to Access.

 

[Trazzel]

I am using MicroSoft Access 2003. It does not allow multiple values when using the lookup wizard. Therefore, when making a recordset, is there a way to insert multiple copies of the same row of information in one step - for example using a do---loop statement or some other conditional statement? The current process involves importing an Excel spreadsheet to which I manually add the number of rows that is required before the import. I just know that there must be a more efficient and better way of doing this. For example, unique individuals who share the same address. I would like to automatically repeat the shared information and then add the unique identifiers. Easy, right?