Solved: Converting four columns of binary data into one column of text

[fboehlandt]

Hi everyone,
I have a problem regarding the presentation of data in one of the tables of my database. Let's assume for simplicity's sake the table looks like the following (Fundcode could be the unique primary key):

<Fundname> <Fundcode> <Europe> <America> <Asia> <Other>
<Fund 1> <101> <-1> < 0> < 0> < 0>
<Fund 2> <102> < 0> <-1> < 0> < 0>
<Fund 3> <103> < 0> < 0> <-1> < 0>
<Fund 4> <104> <-1> < 0> < 0> < 0>
etc...

Where Europe, America etc. are regions an investment fund can be invested into. (-1) here denotes that the fund is invested, (0) indicates that the fund is not invested. Thus, those variables are infact binary switch variables (why no check boxes were used is beyond me but the same principle applies). I would like the table to be displayed in a more condensed form:

<Fundname> <Fundcode> <Region>
<Fund 1> <101> <Europe>
<Fund 2> <102> <America>
<Fund 3> <103> <Asia>
<Fund 4> <104> <Europe>
etc...

To make matters worse a given fund can be invested in more than one region, e.g.:

<Fundname> <Fundcode> <Europe> <America> <Asia> <Other>
<Fund 5> <103> < 0> < 0> <-1> <-1>
<Fund 6> <104> <-1> < 0> < 0> <-1>

In which case two or more rows must be included to account for the different regions (this would obviously require a new primary key):

<ID> <Fundname> <Fundcode> <Region>
<1> <Fund 1> <101> <Europe>
<2> <Fund 2> <102> <America>
<3> <Fund 3> <103> <Asia>
<4> <Fund 4> <104> <Europe>
<5> <Fund 5> <105> <Asia>
<6> <Fund 5> <105> <Other>
<7> <Fund 6> <106> <Europe>
<8> <Fund 6> <106> <Other>
etc...

Ideally I would like to implement this as a SQL query but I realize this might be a bit optimistic. I recon a VBA script cannot be avoided but I have no idea how to go about it. Maybe converting the (-1)/(0) entries to checkboxes makes things a bit easier? Any input is greatly appreciated. Thanks in advance

[CreganTur]

I may be missing the mark here, but have you considered using 2 different tables for this? It would be more relational.

You could have a Funds table that would contain the FundName and FundCode. FundCode would be this table's Primary Key

The second table would be an Investment table that would use FundCode as a foreign key field, and then have a field for all of your Regions. Each region would have a binary switch (this is actually a Yes/No data type field) that would show if that fund is invested.

[fboehlandt]

I absolutely agree but unfortunately I'm not the administrator of the database. I can only run queries or write VBA code to get what I want. The tables are received from a third party database vendor in the format presented here. I have found a 'feasable' although not ideal solution, a rather lengthy query that returns the information in an acceptable format (Consequtive union queries with clauses: WHERE Europe = -1 etc.). Thanks for your input!
Cheers

[CreganTur]

Do you know anything about data shaping? It's a method of getting a recordset via ADO connections and creating a coherant, distinct recordset out of multiple tables (it basically gets rid of unneeded duplicates).

It's a really big aspect of ADO and VBA programming, so it would be better for you to find some good articles on the web than me trying to explain it... but I'll do everything I can to help you if you think data shaping is what you need for this.

[fboehlandt]

I'll look into it and pick up the thread again should I have any further questions. It sounds like this could resolve most issues with this incoherent database of mine,
thanks again
cheers