Need help normalizing data 1

[cutestuff]

hi,

I have an access database that has a linked table in Excel. This excel worksheet is always updated by someone and they would like to keep it in excel (because not everyone has Access).

I have a list of system numbers: System1, System2, System3,...etc. SO if a user selects System2 from the combo box, the report should show all items under System2.

My problem is this: The linked table in Excel has the following format:
(Column Fields)
Item System1 System2 System3.......System116

I want to normalize the data and get the following output so it would be easier for me to query it by system:
Item System
==== =======
A System1
B System3
AW System4

I tried Union queries but it gives me a "query too complex" error. I tried dividing the Union query into 3 small union queries but this is too slow and if I try to use it in other queries I also get a "query too complex" error.

Please, please, please. Any ideas? How do I get around this?

 

[Golom]

You will probably need to dynamically construct the SQL

Public Function SelectSystem(SystemNumber As Long) As String

   SelectSystem = "Select Item, System" & Trim(Str(SystemNumber)) & " " & _
                  "From MyTable"
End Function

Then use the SQL returned by that function to populate the form or recordset that you want to display. Virtually any code (SQL or VBA) to generate a normalized table from this will either be more than SQL can handle or will be too slow to be practical in a production setting.