Complex Query >255 fields 2

[SirTECH]

Scenario: An item can be assessed under 15 different categories, each category has between 15-25 sub categories. Most of the time a report would not need all of these categories and sub-categories. But (however rare it may) that all categories/sub-categores would be used, thus giving a report with nearly 300 fields. Of course Access will not allow a query to have more than 255 fields.
Using Access XP the complex query taking in every category and sub-category would look something like this...

Select tbl00.revID, tbl01.cat1, tbl01.cat2, ...tbl01.cat25, tbl02.cat1, tbl02.cat2, ...tbl02.cat25,...tbl15.cat25 FROM tbl00 left join on tbl00.revID=tbl01.revID ...etc

I hope that the above query is understood well enough without having to type out the entire (nearly) 300 fields. However, if more info is needed, please let me know.

Question: So, my question is this, does anyone have a workaround for this problem. A way to approach this that would delivery a report no matter how many categories were used for a given item - even if all 300 fields were requested?

(Note: I have managed to build a report having 15 sub-reports, where each sub-report held only one main category and it's sub-categories. This has worked, but has bloated the front-end's size).

Thanks.

 

[MajP]

So, my question is this, does anyone have a workaround for this problem

Yes you need to completely redesign your database. Once you normalize your data the whole thing would be orders of magnitude easier to work with. You need to google "relational database design" and do a little studying. Once you properly build the tables you can use different queries to "normalize" your structure into the correct structure.
Because of your current design you are very limited in what you can do.

 

[SirTECH]

Thank you for your advise. I should have stated that I had considered at least the first 3 levels normalization by placing each unique category into it's own table. There are 15 tables (one for each category). Each table holds no more than 25 fields representing it's own unique sub-category. The task I have is to provide a report that will show all the categories and sub-categories combined. It is this task that has exceeded the 255 field limit.

 

[MajP]

It sounds to me that this should have been done in a single self referencing table.

TblData
itemID
categoryID
categoryData
parentCategoryID

You still could not have 300 columns, if in fact that even makes sense. I would think you would present this as a tree view with 15 levels.

If I had to make a report with really 300 columns, I would dynamically loop my data and write to Excel.

Can you explain what this data is and how you use it, and why you would look at this as 300 columns and not 300 rows?

 

[SirTECH]

The data is a detailed analysis of a case. For example, "CaseA" could be represented on a report as...

Case A:
Category 1:
SubCat 1
SubCat 2
...etc
SubCat 15
Category 2:
SubCat 1
SubCat 2
...etc
SubCat 15
...etc
Category 15:
SubCat 1
...etc

Each Category and it's sub-categories are stored in separate tables. Most of the time a case will never use all categories, but it is potentially possible, and so I wanted to be able to incorporate all categories and sub-categories into one report.
When I first wrote the query that joined all of these tables and fields, it was too large. I need to generate this report, but will have to find a way that doesn't rely on a single query.

 

[PHV]

Each Category and it's sub-categories are stored in separate tables
Can't you use an UNION query instead of join all of these tables ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Each Category and it's sub-categories are stored in separate tables
I think this is where your problem begins. As MajP has suggested, if you create a CATEGORIES table, you could move all the data from these tables into one centralized area. It would certainly make your job much easier.


Randy

 

[SirTECH]

Thanks, Randy & MajP! I'm beginning to see the light (sorry for my slow uptake). Certainly converting columns to rows by using a single Categories table should work!
Why I have to make my work more complicated than it should be is beyond me. Thank you both for your valuable advice.
Now I've got some work to finish!
Cheers.

 

[MajP]

Can you explain the data and how it is used? If this is hierrarchial data then it can go into a single self-referencing table. However, getting data into that format is easy, but working with it and displaying it can be real tricky. I have posted numerous times on this subject "equipment hierrachies", "Nested BOMs", "Gannt charts", and there are lots of tips and tricks if in fact you have a hierrarchial structure.

 

[SirTECH]

The data is an analysis on an item. The item is analysed based on 15 categories. Each of the 15 categories has a various number of sub-categories.
The item table has a one-to-one relationship to the 15 category tables (eg. tblCases.ItemID=Cat1.ItemID). Each category table has it's own unique set of sub-categories.

tblCASES (table)
---> Cat1 (table)
SubCat1 (column/field)
SubCat2 (column/field)
etc (more columns/fields)
---> Cat2 (table)
SubCat1 (column/field)
SubCat2 (column/field)
etc (more columns/fields)
----> etc (more tables)
----> Cat3 (table)
SubCat1 (column/field)
etc (more columns/fields)

I initially tried to query the item table and all 15 category tables, and ended up with a query having about 300 fields (the total number of sub-categories), which of course I could not do.

 

[MajP]

I think some of the confusion is that your nomenclature is wrong. You do not have any subcategories as far as I can see, you only have "measures", "descriptors", or "values". A sub category could not be described by a single field. A sub category would have multiple items. Your structure is simply
Category1
Measure 1
measure 2
Measure 3
....
Category2
Measure 1
Measure 2
....

Not
Category 1
Sub Category 1
Measure 1
Measure 2
Measure N
sub Category 2
Measure 1
Measure 2

Are these measures (sub cat) all the same datatype or are they different (text, boolean, number, curr)?
That would determine how to design this.

 

[SirTECH]

The measures are all the same datatype (text), for each measure is also a rating (number).

 

[MajP]

If that is is case I believe you can make a very simple table structure

tblAnalysis
CategoryID_fk (this is a foreign key relating to the table of categories)
measureName (a description of what is measured)
measureValue (a textual value)
measureRating (a rating)

tblCategories
categoryID (primary key)
categoryName (name of the category)
categoryAbbrev (maybe an a short name for the category)
categoryNotes (maybe some info about the category)
categorySort (maybe a sort order for the category)

Now the trick is to also have a reference table of measures. So when you add an item it gets a record for each measure. Sounds like it inserts about 300 records.

tblReferenceMeasures
measureID
measureCategory
measureName

If that design makes sense then in truth the tblAnalysis can be modified to

tblAnalysis
measureID_fk (a foreign key back to the reference table)
measureValue
measureRating

 

[SirTECH]

Sorry for delaying getting back to you. The family pet passed away yesterday.
Anyway, I greatly appreciate the detailed and useful reponse.
I had a categories table similar to the one you suggested, but didn't have a measures reference table (and btw the term measures does make so much more sense).
This design has greatly assisted me with avoiding the 255 column limitation.
Thanks you very much.