Reduce query column number

[georgesOne]

Hi,

I have one of these rare cases where a query MAY result in more than 255 columns (and fails). This case is actually highly unlikely, but you never know...
Some of these columns may actually be empty though.
Is there any way to QUICKLY test for empty columns, or even better to run a query, which excludes columns which are empty right away?

Thanks for any hint.

Cheers, Georges

 

[SkipVought]

Hi,

Why would you have columns without data?

Please post the SQL for your query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fneily]

In a relational database, you have NO columns with empty data. I'll bet your tables are not normalized. So right now, you don't have an Access table.
Post your table structures, eg.
tblCustomers
CustID
FirstName
LastName
etc.

See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[georgesOne]

Hi Skip and fneily,

I certainly (but to a certain extent only partially) agree to your comments. I believe I may have posted the wrong question, but the right question is more or less a question of design (which resulted in failure to normalization). So, here is the problem:

I am trying to develop a db for fragrance formulations containing an indefinite amounts of components. These components and their weights, dilutions, etc. are added via a form which uses normalized tables with joins. However, this results in a difficult to read output structure, and some people ask for a table type they are used to from Excel. And this table can have more than 255 columns. Actually it does not yet, but I do not want a formulator having the situation that he/she can not add components anymore over a certain limit, or that they are not recorded in the excel-type table.

So, depending on the query details the excel type table can have a structure like this:

.... field1.....field15.....field36.....field45...field 312
val11 val12 val13
val21 val22
val31 val32
val41 val42

In this case, I would like to have column of field 36 not displayed in the query producing an excel type appearance.

Does this make sense, or how could that be tackled otherwise?

Regards, Georges

 

[PHV]

Have you tried a crosstab query ?

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

 

[georgesOne]

Hi PHV,

I believe my excel type table is actually 'similar' to a crosstab query (but not generated via a crosstab query). Would the crosstab query eliminate empty columns?

Thanks, Georges

 

[dhookom]

By default, the crosstab will only generate columns based on the unique values from your filtered records.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Dear All:

I have played with crosstab query... it does nopt show empty rows, but I think it does not allow me to do what I want.

My crosstab query:

TRANSFORM First(tbl_Transfer.Name) AS FirstOfName
SELECT tbl_Transfer.Head
FROM tbl_Transfer
GROUP BY tbl_Transfer.Head
ORDER BY Format(tbl_Transfer.Hier,"000") & tbl_Transfer.Cat
PIVOT Format(tbl_Transfer.Hier,"000") & tbl_Transfer.Cat;

The underlying table structure is:

ID Head HLot Hier Cat Name Ratio Lot Mw Fract Solid Solv
29656 F08L2094 #01 14 Arto1Name FTCPMA/CNNMA/GBLMA-? 40/20/40 TG6051 #0407061 100 100
29657 F08L2094 #01 56 PAR1Name SPTNf No ID 108#1 562 30 100
29658 F08L2094 #01 92 Base1Name DIPA No ID 148#2 177 23 1 MAK
29659 F08L2094 #01 146 Surf1Name HC-4430 No ID 205#2 120 10 MAK
29660 F08L2094 #01 164 Solv1Name MAK No ID 208 0 0 0 70
29661 F08L2094 #01 168 Solv2Name PGME No ID 209 0 0 0 30
29662 F08L2095 #01 14 Arto1Name FTAdMA/FTCPMA/CNNMA/GBLMA-? 20/20/20/40 TG6054 #0407064 100 100
29663 F08L2095 #01 56 PAR1Name SPTNf No ID 108#1 562 30 100
29664 F08L2095 #01 62 PAR2Name MPMSNf No ID 051#1 496 30 100
29665 F08L2095 #01 92 Base1Name DIPA No ID 148#2 177 23 1 MAK
29666 F08L2095 #01 146 Surf1Name HC-4430 No ID 205#2 120 10 MAK
29667 F08L2095 #01 164 Solv1Name MAK No ID 208 0 100 0

I actually want 'Name', 'Ratio', 'Lot', 'Mw', 'Fract', 'Solid' and 'Solv' in the crosstab.
Also I do not know, how to order on 'Hier' but get 'Cat' in the column headers.

Thanks for any comments.

Regards, Georg