How can I make the name of a record an attribute in a table?

[George Chen]

Hello,

I have several tables that look like this:
ProteinName Signal ProteinAmount
A 10 20
B 5 20
C 30 30

(about 1000 proteins)

I have about 50 of these tables.
I want to make a larger table such that all the signal readings for protein
A are side by side, all the readings for protein B are side by side, and so
on for the remaining 998 proteins.

So, something like this:

ProteinName Table1 Table2 Table3
A 10 20 30
B 30 20 10
C 10 20 30
(and so on for the remaining 997 proteins)

Could somebody tell me if this is possible and how I should do it or where I
should start reading to figure out how to do it.

Thanks for your help in advance.

 

[Dorian]

You could do a UNION query to combine all 50 tables into one result set.
You'll need to create a artificial column to hold the 'which table' indicator.
Then you could make a crosstab query to extract your final data.

You might want to ask yourself if your database design is really meeting
your needs. These type of table gyrations could indicate a weakness in the
original design.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

Hello,

I have several tables that look like this:
ProteinName Signal ProteinAmount
A 10 20
B 5 20
C 30 30

(about 1000 proteins)

I have about 50 of these tables.
I want to make a larger table such that all the signal readings for protein
A are side by side, all the readings for protein B are side by side, and so
on for the remaining 998 proteins.

So, something like this:

ProteinName Table1 Table2 Table3
A 10 20 30
B 30 20 10
C 10 20 30
(and so on for the remaining 997 proteins)

Could somebody tell me if this is possible and how I should do it or where I
should start reading to figure out how to do it.

Thanks for your help in advance.

STOP.

Your current design is badly flawed. Storing data in a tablename is never a
good design. I presume each table is a different kind of "reading", some sort
of measurement? If so you need one table with fields:

ProteinName
ReadingType <what's currently your table name>
Reading <what's currently the Signal field in your table>
ProteinAmount <what amount generated this reading>

This will give you a "tall thin" table instead of your current two different
spreadsheet approaches. This table will have 50000 rows; fear not, 5 million
rows is very practical with Access.

If you could describe the meaning of your current tables we might be able to
help come up with a properly normalized design.