Report Multi-value query

[sharkey_lsu]

Hi,

I have created a plant database for my office and I need some advice on a feature that I am currently working on. A little background, I am by no means an expert at Access but am self taught and have picked up on it pretty well. Anyhow, the issue that I need help with is that I want to appoint hardiness zones to each entry in my table but each entry almost always has multiple values. I have set up a field that allows me to select multiple values via a checkbox through the forms that I have created. This solution worked great but now that I want to analyze the info in these fields and create a report that filters my entries by Zone, the filter creates a category for each combination of values (an entry would be in a new category called 'Zone 1,2,3,4,5' rather than in the 5 different categories that I want them in). Is there any way to make them categorize correctly? I have worked on creating a multi-value query that I thought was going to work but it ended up categorizing them with the title 'Zone ?Name'.

By the way, the word 'Zone' is not present in my table, it is a label that I have created (just to avoid any confusion)

Not as important, but is there any way to make the data be displayed as 'Zones 1-5' Rather than Zones 1,2,3,4,5?

Thanks for any help!!!

[ssanfu]

I have not heard of any experienced Access programmer the do use multi-value fields.
I NEVER use multi-value fields. SQL, MySQL, Oracle do not support multi-value fields.

Using multi-value fields (IMO) break normalization rules.

If I ever use Sharepoint, I might have to change my stance, but right now, I don't use them.


Maybe this will be of help->> http://allenbrowne.com/func-concat.html

[sharkey_lsu]

Thank you for the link but I do not see how that will help with what I am trying to do. Also, I don't see this database being used by any SQL programs as this is strictly intended for inter-office use (I have no experience with SQL programs which is the main reason why). Maybe I am using the wrong program all together (Access), but so far it has got the job done.

The goal of the database in a nutshell is to create 'cutsheets' for office and client use that contain all the information about a plant. That part works great, but I now want to make it an easier to use database that can be easily organized as well as updated by persons with little knowledge of Access. I have done this through the use of reports that organize the entries by certain fields (so far I have organized by plant type, common name, and botanical name). The reason this works great is because those fields can only have one entry whereas other fields (pH requirements, USDA zones, plant height, etc.) all potentially have many different entries. I wish I could just show the database to someone and get some pointers about how to move forward.

I do not think it would be feasible to create a column for each value because there would be hundreds of columns which is what lead me to use the multi-value fields to avoid that issue.

I have not heard of any experienced Access programmer the do use multi-value fields.
I NEVER use multi-value fields. SQL, MySQL, Oracle do not support multi-value fields.

Using multi-value fields (IMO) break normalization rules.

If I ever use Sharepoint, I might have to change my stance, but right now, I don't use them.


Maybe this will be of help->> http://allenbrowne.com/func-concat.html

[ssanfu]

My point was that using multi-valued fields are a pain in the neck (but 2 feet lower). I would recommend not using multi-valued fields; much better to use a normalized structure. You might have to become the "expert" on multi-valued fields.

Maybe this link will help: http://office.microsoft.com/en-us/ac...010149297.aspx

Sorry I'm not much help.