Query a Column of Data to a Field NAME

[Ozzie via AccessMonster.com]

Hi,

I have a problem which hopefully someone can help me with or at least point
me in the right direction.

I have 2 tables of data;

Table1 has a column of data that just contains the following Text, Mth1 or
Mth2 etc

Table2 has 12 columns with the following Field Nmaes, Mth1, Mth2 etc right up
to Mth12

I need to be able to query where the data in Table1 = The Field Name in
Table2 to return the data in Table2

Anybody any idea's?, absolutely anything appreciated,

Many thanks

 

[Bob Barrows [MVP]]

Hi,

I have a problem which hopefully someone can help me with or at least
point me in the right direction.

I have 2 tables of data;

Table1 has a column of data that just contains the following Text,
Mth1 or Mth2 etc

Table2 has 12 columns with the following Field Nmaes, Mth1, Mth2 etc
right up to Mth12

Not good. This is a database, not a spreadsheet. Tables should be long and
narrow. Think "12 rows" of data instead of 12 columns. This "spreadsheet"
design makes the table very difficult to handle in queries where you wsh to
aggregate the data in those columns.

I need to be able to query where the data in Table1 = The Field Name
in Table2 to return the data in Table2

I'm having trouble visualizing what you mean by this statement. It is
impossible to use metadata in query criteria. You can only use data. If I am
understanding you correctly, your first step needs to be: get the metadata
into the data. I am assuming you have a field in table 2 that identifies
each record, a primary key column. I will assume it is called "RecordID".
Create a query in design view then switch to SQL view and enter this union
query:

select RecordID, "Mth1" as RecordMonth, Mth1 FROM table2
union all
select RecordID, "Mth2" as RecordMonth, Mth2 FROM table2
....
union all
select RecordID, "Mth12" as RecordMonth, Mth12 FROM table2

Save the query as "FoldedData", since "folding" is what the operation being
performed on your data is called.

Now you can do this
select ...
from table1 t1 join FoldedData f on t1.columnname= f.RecordMonth


If this does not completely answer your question, please provide a couple
rows of sample data in tabular format followed by the desired results, also
in tabular format.

 

[John Spencer]

The only thing I can think of is to use a normalizing union query to fix
table 2.

SELECT Mth1 as TheData, "Mth1" as TheDataType
FROM Table2
UNION ALL
SELECT Mth2 as TheData, "Mth2" as TheDataType
FROM Table2
UNION ALL
SELECT Mth3 as TheData, "Mth3" as TheDataType
FROM Table2
....

Then you could join the saved union query with table1 (of course if all you
have in table 1 is the original field names, you don't even need to do that)

SELECT Q.TheDataType, Q.TheData
FROM qUnionTheData as Q INNER JOIN Table1
ON Q.TheDataType = Table1.TheField

Now if your table2 was correctly structured. This would be much simpler.

 

[Ozzie via AccessMonster.com]

Thanks John / Bob for you replys,

I tried the Union but it fails due to volume of data, I have however got a
work around, not the best though, I have built an IIF formula which seems to
be working, but isn't very flexable because the IIF formula is hard coded.

Anyway many thanks to you both,

Regards

David

The only thing I can think of is to use a normalizing union query to fix
table 2.

SELECT Mth1 as TheData, "Mth1" as TheDataType
FROM Table2
UNION ALL
SELECT Mth2 as TheData, "Mth2" as TheDataType
FROM Table2
UNION ALL
SELECT Mth3 as TheData, "Mth3" as TheDataType
FROM Table2
...

Then you could join the saved union query with table1 (of course if all you
have in table 1 is the original field names, you don't even need to do that)

SELECT Q.TheDataType, Q.TheData
FROM qUnionTheData as Q INNER JOIN Table1
ON Q.TheDataType = Table1.TheField

Now if your table2 was correctly structured. This would be much simpler.

Hi,

[quoted text clipped - 17 lines]

Many thanks