Best way to merge multiple columns of a Table into one for Reports

[templeowls]

I'll try to explain this the best I can. Basically I've got a form on a website that lets my managers report incidents at their store. They select a primary category, and then the that splits into sub categories.



This is relevant as this online form automatically feeds into an access form. Its great, I've got forms and reports all feeding off this. My only issue is that these sub categories all have their own individual column in my table. Meaning I'll have data in one column for an entry and then all the other sub category columns are empty.

Makes it very hard to incorporate sub category data into a form or report.

Does anyone know the best way to combine all the sub category data into one column? I do not want to change my online form, nor my table design. I know that might be the easier way, but this is such a well working system so far. I don't want to change everything because of this.

I am open to any way to capture this info all into one column

[JoeM]

I do not want to change my online form, nor my table design. I know that might be the easier way, but this is such a well working system so far. I don't want to change everything because of this.

Actually, it is the contrary. You just proved that this is NOT working well. You have a table design that is not normalized. When you do that, seemingly simple tasks and made much more difficult.
The rules of normalization say you should never have repeating similar fields in a table design. Rather, than having four Sub-Category fields, you would have one Sub Category Number field, and one Sub Category Value field. So, if a particular record had multiple Sub-Categories, instead of having one record with two Sub-Category fields filled out, you would have two distinct records.

While that may seem counter intuitive, think of the ramifications. Here is a real world example. Let's say that you had to locate all records that have a certain Sub Category value. In your example, you would need to search four different fields. In the normalized structure, you would only need to search one. Searching four fields might not seem like that big of a deal, but what if you had 20 Sub Category fields? Then it becomes a nightmare!

Also, the normalized structure allows for growth without having to edit your table design (i.e. add new fields). In the normalized design, all you have to do to add more Sub Categories is to add new records to the existing table.
In your design, you would have to add new fields to all tables, queries, forms, and reports that use those fields. Once again, creating a potential nightmare scenario just to add more fields.

Being down the road that you currently are on, I would highly recommend changing your table structure. Yes, it will be a little more work up front, but save you down the road. You may be able to simplify it further, depending on a few details, such as:
- Do any records actually have more than one Sub Category field filled out? Can they?
- In your example, I noticed that all Sub Category 1 records have the same value ("Employee Fall"), and the same is true for the other 3. Can they ever differ, or is each Sub Category number associated with just one single value?

If you really do not want to change anything, and each record will only have one Sub-Category field filled out, you could do a calculated field like:

SubCategory: [Sub- Category 1] & [Sub Category 2] & [Sub Category 3] & [Sub Category 4]

[templeowls]

I understand that, in theory, having one sub category makes more sense. I totally get that.

However, the web application I'm using links into Access perfectly. And there is simply no way to make the online application feed into Access with only one sub-category. It's simply not possible. The table structure is a reflection of the web application.

I do not want to change my entire web application because of this issue. I'm merely asking if there is a work around way of collecting all the sub category data into one column, strictly for reporting use.

I will try the code you recommended. Thanks!

[templeowls]

And each entry will only ever have one sub category filled out. And they never differ. So if there's data in sub-category 1, it will always be "employee fall" or else it will be blank.

[Micron]

Try a UNION query to get un-normalized columnar data into one field; it's the usual approach.
Would be interesting to know what this "web application" is that imposes such an impediment. Surely it's not a web based Access database? If not, perhaps you should be mining it for data and normalizing it in Access so that you can work on it without having to develop work-arounds all the time.