Translate row and column to position

[Sean Clayton]

Hi all,

I'm working on moving the records on tissue samples in a medical lab
from an Excel spreadsheet into an Access database. At present, the
records look like this:

Case Shelf Rack Box Row
---- ----- ---- --- ---
932 2 C 14 1

A B C D E
F G H I
--------- --------- -------- ---------
-------- -------- --------- --------
--------
LL1U A LL1U B RTZB A RTZB B RL3B A RL3B B RL2B A RL2B B RL1B A


I need them to look like this:

Case Shelf Rack Box Row Position Sample
---- ----- ---- --- --- -------- ------
932 2 C 14 1 1 LL1U A
932 2 C 14 1 2 LL1U B
932 2 C 14 1 3 RTZB A
932 2 C 14 1 4 RTZB B
932 2 C 14 1 5 RL3B A
932 2 C 14 1 6 RL3B B
932 2 C 14 1 7 RL2B A
932 2 C 14 1 8 RL2B B
932 2 C 14 1 9 RL1B A

....where A1 is position 1, B1 is position 2, C1 is position 3, and so
forth. My biggest problem seems to be that I don't know how to
reference the column titles to update the Position field. Would
someone be so kind as to point me in the right direction?

Thank you,
Sean Clayton

 

[KARL DEWEY]

Does your spreadsheet include the ---- and A B C etc.?
Or did you add thenm for clarity? The reason I ask is because there is a
mix of spaces and tabs between then.

 

[Sean Clayton]

Does your spreadsheet include the   ----   and     A     B     C   etc.?
Or did you add thenm for clarity?   The reason I ask is because there is a
mix of spaces and tabs between then.

It does have the A B C and so forth; I tried to write it out here the
way it looked there, but the formatting bit me.

The sheet has the following columns: Case, Shelf, Rack, Box, Row, A,
B, C, D, E, F, G, H, and I. It is intended to visually represent the
location of tissue samples in our lab freezer. A through I contain the
actual sample ID numbers.

 

[John W. Vinson]

Case Shelf Rack Box Row
---- ----- ---- --- ---
932 2 C 14 1

A B C D E
F G H I
--------- --------- -------- ---------
-------- -------- --------- --------
--------
LL1U A LL1U B RTZB A RTZB B RL3B A RL3B B RL2B A RL2B B RL1B A


I need them to look like this:

Case Shelf Rack Box Row Position Sample
---- ----- ---- --- --- -------- ------
932 2 C 14 1 1 LL1U A
932 2 C 14 1 2 LL1U B
932 2 C 14 1 3 RTZB A
932 2 C 14 1 4 RTZB B
932 2 C 14 1 5 RL3B A
932 2 C 14 1 6 RL3B B
932 2 C 14 1 7 RL2B A
932 2 C 14 1 8 RL2B B
932 2 C 14 1 9 RL1B A

...where A1 is position 1, B1 is position 2, C1 is position 3, and so
forth. My biggest problem seems to be that I don't know how to
reference the column titles to update the Position field. Would
someone be so kind as to point me in the right direction?

A "Normalizing Union Query" is the ticket here. You need to extract the nine
columns individually, and then use UNION to string them together into a
tall-thin table. Import the spreadsheet as is, or link to it, and then use a
query like

SELECT Case, Shelf, Rack, Box, Row, (1) AS Position, [A] AS Sample
FROM spreadsheet WHERE [A] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (2) AS Position, AS Sample
FROM spreadsheet WHERE IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (3) AS Position, [C] AS Sample
FROM spreadsheet WHERE [C] IS NOT NULL
UNION ALL <etc etc>

etc. where A, B and C are the names of the spreadsheet fields.

 

[KARL DEWEY]

John's reply is the way to go.

It does have the A B C and so forth; I tried to write it out here the
way it looked there, but the formatting bit me.

The sheet has the following columns: Case, Shelf, Rack, Box, Row, A,
B, C, D, E, F, G, H, and I. It is intended to visually represent the
location of tissue samples in our lab freezer. A through I contain the
actual sample ID numbers.

 

[Sean Clayton]

Case            Shelf       Rack      Box  Row
----            -----       ----      ---     ---
932     2           C         14      1

A           B               C             D         E
F             G             H               I
---------       ---------       --------       ---------
--------        --------        ---------       --------

I need them to look like this:

Case        Shelf   Rack    Box     Row     Position        Sample
----        -----   ----    ---     ---     --------        ------
932 2       C       14      1       1               LL1U A
932 2       C       14      1       2               LL1U B
932 2       C       14      1       3               RTZB A
932 2       C       14      1       4               RTZB B
932 2       C       14      1       5               RL3B A
932 2       C       14      1       6               RL3B B
932 2       C       14      1       7               RL2B A
932 2       C       14      1       8               RL2B B
932 2       C       14      1       9               RL1B A

...where A1 is position 1, B1 is position 2, C1 is position 3, and so
forth. My biggest problem seems to be that I don't know how to
reference the column titles to update the Position field. Would
someone be so kind as to point me in the right direction?

A "Normalizing Union Query" is the ticket here. You need to extract the nine
columns individually, and then use UNION to string them together into a
tall-thin table. Import the spreadsheet as is, or link to it, and then use a
query like

SELECT Case, Shelf, Rack, Box, Row, (1) AS Position, [A] AS Sample
FROM spreadsheet WHERE [A] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (2) AS Position, AS Sample
FROM spreadsheet WHERE IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (3) AS Position, [C] AS Sample
FROM spreadsheet WHERE [C] IS NOT NULL
UNION ALL <etc etc>

etc. where A, B and C are the names of the spreadsheet fields.

That seems to work wonderfully! Just to clarify, though: since the
Position is a number assigned by me and not created by the query, I'll
need to write one statement for every line in the Excel file?

 

[John W. Vinson]

SELECT Case, Shelf, Rack, Box, Row, (1) AS Position, [A] AS Sample
FROM spreadsheet WHERE [A] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (2) AS Position, AS Sample
FROM spreadsheet WHERE IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (3) AS Position, [C] AS Sample
FROM spreadsheet WHERE [C] IS NOT NULL
UNION ALL <etc etc>

etc. where A, B and C are the names of the spreadsheet fields.

That seems to work wonderfully! Just to clarify, though: since the
Position is a number assigned by me and not created by the query, I'll
need to write one statement for every line in the Excel file?

Maybe I'm misunderstanding the structure of the Excel file! I thought that
your Position 1 corresponded to a particular *column* (not row) in the
spreadsheet. You would indeed need a separate SELECT statement in the union
query for each such column - but if it's *rows* then I am misinterpreting.

 

[Sean Clayton]

Maybe I'm misunderstanding the structure of the Excel file! I thought that

your Position 1 corresponded to a particular *column* (not row) in the
spreadsheet. You would indeed need a separate SELECT statement in the union
query for each such column - but if it's *rows* then I am misinterpreting..

I'm sorry; I probably wasn't very clear with what I'm trying to do.
It's kind of hard to explain.

What I've got is an Excel spreadsheet with somewhere around 1700
lines. Each line contains a case number, which is an identifying
number for the patient to which the tissue samples on that line
belong. After the case number there is a shelf number, a rack letter,
a box number, and a row number. Each line also contains 9 or 10 tissue
sample codes, residing in columns lettered from A to I or J.

Here's what one line of that looks like, uploaded to Access:
http://picasaweb.google.com/SeanCly10/AccessQuestions#5318966847448248194

What I want to do is parse each one of those sample codes out to its
own line, with the result that the case, shelf, rack, and box numbers
are reproduced, but with a position number that represents the column/
row combination in the Excel sheet. For example, column A row 1 would
become position 1, column B row one would be position 2, and so on.

Here's what the result might look like, actually taken from your UNION
query: http://picasaweb.google.com/SeanCly10/AccessQuestions#5318966835043132850

The difference between your UNION query and what I'm shooting for, if
possible, is to not have to put a position number directly in the
query for each line, since the original Excel sheet has over 1700
rows. If there is no way, then I'll just go with it, but I wanted to
see.

 

[John W. Vinson]

I'm sorry; I probably wasn't very clear with what I'm trying to do.
It's kind of hard to explain.

What I've got is an Excel spreadsheet with somewhere around 1700
lines. Each line contains a case number, which is an identifying
number for the patient to which the tissue samples on that line
belong. After the case number there is a shelf number, a rack letter,
a box number, and a row number. Each line also contains 9 or 10 tissue
sample codes, residing in columns lettered from A to I or J.

Here's what one line of that looks like, uploaded to Access:
http://picasaweb.google.com/SeanCly10/AccessQuestions#5318966847448248194

What I want to do is parse each one of those sample codes out to its
own line, with the result that the case, shelf, rack, and box numbers
are reproduced, but with a position number that represents the column/
row combination in the Excel sheet. For example, column A row 1 would
become position 1, column B row one would be position 2, and so on.

Here's what the result might look like, actually taken from your UNION
query: http://picasaweb.google.com/SeanCly10/AccessQuestions#5318966835043132850

The difference between your UNION query and what I'm shooting for, if
possible, is to not have to put a position number directly in the
query for each line, since the original Excel sheet has over 1700
rows. If there is no way, then I'll just go with it, but I wanted to
see.

I guess I'm completely lost.

My query does supply a position number FOR EACH COLUMN in the original
spreadsheet, and puts it in each line of the output table.

Everything in Column A ends up with a Position number 1, everything in Column
B ends up in Position number 2, for all rows in the spreadsheet.

It SOUNDS like that's what you're asking for. It appears to be what you're
getting based on the picture of the union query.

Clearly it's not what you want; how am I misunderstanding what it is that you
want??? Perhaps you could post the SQL view of the UNION and indicate in what
way it has you "put a position number directly in the query for each line"...

 

[Sean Clayton]

I guess I'm completely lost.

My query does supply a position number FOR EACH COLUMN in the original
spreadsheet, and puts it in each line of the output table.

Everything in Column A ends up with a Position number 1, everything in Column
B ends up in Position number 2, for all rows in the spreadsheet.

It SOUNDS like that's what you're asking for. It appears to be what you're
getting based on the picture of the union query.

Clearly it's not what you want; how am I misunderstanding what it is that you
want??? Perhaps you could post the SQL view of the UNION and indicate in what
way it has you "put a position number directly in the query for each line"...

I'm sorry, here's my SQL code:

SELECT Case, Shelf, Rack, Box, Row, (1) AS Position, [A] AS Sample
FROM SpecimenTest WHERE [A] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (2) AS Position, AS Sample
FROM SpecimenTest WHERE IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (3) AS Position, [C] AS Sample
FROM SpecimenTest WHERE [C] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (4) AS Position, [D] AS Sample
FROM SpecimenTest WHERE [D] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (5) AS Position, [E] AS Sample
FROM SpecimenTest WHERE [E] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (6) AS Position, [F] AS Sample
FROM SpecimenTest WHERE [F] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (7) AS Position, [G] AS Sample
FROM SpecimenTest WHERE [G] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (8) AS Position, [H] AS Sample
FROM SpecimenTest WHERE [H] IS NOT NULL
UNION ALL
SELECT Case, Shelf, Rack, Box, Row, (9) AS Position, AS Sample
FROM SpecimenTest WHERE IS NOT NULL
UNION ALL SELECT Case, Shelf, Rack, Box, Row, (10) AS Position, [J] AS
Sample
FROM SpecimenTest WHERE [J] IS NOT NULL;

This seems to require me to reproduce one SELECT...FROM statement for
each sample, and restricts the Position number that it gives to each
Sample to just what I put in the '(X) as Position' line.

 

[John W. Vinson]

This seems to require me to reproduce one SELECT...FROM statement for
each sample,

Try it with a table with multiple rows. It requires you to use one SELECT
statement for each *COLUMN* in the spreadsheet which might contain a sample -
*not* one SELECT for each row. Each SELECT statement will include all data
from column A, no matter how many rows there are; NULL values will not be
included.

and restricts the Position number that it gives to each
Sample to just what I put in the '(X) as Position' line.

Yes. That's what you asked for. If that's not what you intended to ask for,
please explain.