Sporadic order of data within field

[Cori]

I recently updated the data within a multi value field for one record and noticed that when I pulled the report that the data didn't display alphabetically. The weird part is that if I select some values it displays in ascending order correctly however if I select other values it doesn't, all in the same field using the same source.

For example, the field originally displayed A, I, U.
On one record, I deleted "U" and added "O" and it correctly displays as A, I, O.


However if I delete "U" and add "E" it incorrectly displays as A, I, E instead of A, E, I.

The original table has the values sorted ascending correctly. I have retyped the erring values (thinking there may be hidden blank spaces, etc) but that didn't fix anything.

Can anyone assist?
Thanks,
Cori

[ssanfu]

I never use multi value fields (MFV's). IMO they cause more trouble than they are worth.

From what I understand, MFV's were created to help Access integrate with SharePoint. Apparently they do create a proper one-to-many structure behind the scenes, but Access developers can't get to that interface.


Also see
http://windowssecrets.com/forums/sho...od-idea-or-not
http://www.utteraccess.com/forum/Mul...-t1984616.html

[Micron]

I never use multi value fields.

I second and third that notion!

The original table has the values sorted ascending correctly. I have retyped the erring values

If you mean the table that you see, then from what little I know of these fields I'd say it's because what you see is actually a representation of the REAL tables that you can't see, which Access creates for MVF's. Apparently, you can successfully sort the representation in a table view, but the hidden tables are the ones that you are pulling the data from when you query or report. One thing you can try is to query the MFV table and apply a sort, then base your report on another query that uses the sorting query as a table. As inferred, what I know of these fields is from whatever I've read about them - foremost which was to never use them.

[Cori]

I guess this a two part question then. I have a database I inherited where there are a lot of these multivalue fields. Maybe I'm calling it the wrong thing, I don't know.

To clarify, I have a massive database I inherited and I'm sure the database could have been set up better in the beginning but I'm coming in very late so don't have the luxury of changing the setup (at least not in the near future).

There is one main table that contains the data from a ton of other individual tables. Within this main table, some lookups allows for multiple values and others don't. They then have many filtered query's (with less fields) based on this main table, one for each "area", with various forms/reports based on these filtered queries. The forms/reports use combo boxes where I can select more than one value.


For the first question, if I shouldn't use multivalue fields how would I go about changing my database? Also, how then do I select more than one value for a particular "item"?

For the second question, I've gone to the individual table for this particular "item" and have made sure that the values are sorted ascending. Is there something I can do on the query or the main table that everything is based on?

Thanks.

[Micron]

some lookups allows for multiple values

Lookup/Lookup Field defines particular things and should not be used to describe a combo box, for example. I don't know what the context is for your use of the term/word.
A lookup field and a multi value field are not the same thing. A lookup field is in a table, and presents a combo box type of control whose row source is some table or query. The value you choose from the list gets stored in the field. A mvf is one that contains two or more values in the same field. I presume if you make the field wide and high enough in table datasheet view, you'd see more than one value, but as I said, I've never used them so part of what I know is from reading and helping others figure out their issues. Anyone with actual experience with them should feel free to chime in. So if you don't know which it is you have, I'm not sure how anyone can definitively answer your questions. One test you can try is create a small query that returns only one record from the table that contains what might be a mv field, but leave that field out. Then add the suspected mv field but don't put criteria on that field and run the query again. If you now get more than one record, safe to say you have a mv field.

For the first question, if I shouldn't use multivalue fields how would I go about changing my database?

My answer to this and your second question could be the same, and is in my first post. If you cannot make major design changes to the db, basing your reports/forms on a new sorting query instead of whatever they are based on now is the only way I know of fixing a sort issue, assuming you cannot fix a sort by report grouping/sorting property settings. If you have the wherewithal to redesign the db, then the simple answer is to break out the mv data into its own tables(s) following normalization principles. For help on that, it would be a new thread, and possibly a whole new can of worms!

Lastly, for additional help on determining if you have a mvf in a table or not, create a new db with a make table query that inserts a few rows. Edit data if you have privacy concerns, then zip and post the db here and we can look at it.

[orange]

I agree with Micron. If you can, go for a redesign of the MV fields. Use this forums for as much assistance as you need.
If you have inherited a "mess", and management is either aware of the "structural issue" or is having difficulty in retrieving info, then you could try to use those facts for improving the design and turnaround time for info.

If it has been "dropped on you" as the person to solve things, then you may use this as an opportunity for training or redevelopment.

Good luck.

[Cori]

Thanks all. I'll see what I can do this month to start correcting things.