Show the data text corrosponding to the ID

[Guest]

I am trying to create a query.
I would like the output to look like this in 17 columns:
Name N_name1 N_name2 N_name3 ... N_name16
John ten twenty thirty ... onehundredsixty
Jane thirty fifty twenty ... ten

I have 3 tables. the first looks like this with 17 colums:
Name N1 N2 N3 ... N16
John 10 20 30 ... 160
Jane 30 50 20 ... 10

the second and third look like this with 2 columns
N N_name
10 ten
20 twenty
30 thirty
40 forty
50 fifty

These 2 tables could be combined, but this is how they are right now.

The relationship connects the N to N1,N2,N3,...,N16

Can anyone help? I would really appreciate it.

Thanks in advance

 

[Duane Hookom]

Normalize your table with 17 columns and change the field named name to some
other name that is not name. If you are stuck with the structure then:
-Create a union query
SELECT [Name] as FirstName, N1 as N, "N_name1" As ColHead
FROM tblWith17Columns
UNION ALL
SELECT [Name], N2, "N_name2"
FROM tblWith17Columns
UNION ALL
SELECT [Name], N3, "N_name3"
FROM tblWith17Columns
UNION ALL
SELECT [Name], N4, "N_name4"
FROM tblWith17Columns
UNION ALL
--etc--
SELECT [Name], N16, "N_name16"
FROM tblWith17Columns;

Then create a crosstab query based on the union query and tblSecond. Set the
FirstName as the Row Heading, ColHead as the Column Heading, and First Of
N_Name as the Value.

 

[Guest]

THANK YOU SO MUCH.

This worked wonders!

Normalize your table with 17 columns and change the field named name to some
other name that is not name. If you are stuck with the structure then:
-Create a union query
SELECT [Name] as FirstName, N1 as N, "N_name1" As ColHead
FROM tblWith17Columns
UNION ALL
SELECT [Name], N2, "N_name2"
FROM tblWith17Columns
UNION ALL
SELECT [Name], N3, "N_name3"
FROM tblWith17Columns
UNION ALL
SELECT [Name], N4, "N_name4"
FROM tblWith17Columns
UNION ALL
--etc--
SELECT [Name], N16, "N_name16"
FROM tblWith17Columns;

Then create a crosstab query based on the union query and tblSecond. Set the
FirstName as the Row Heading, ColHead as the Column Heading, and First Of
N_Name as the Value.
--
Duane Hookom
MS Access MVP

I am trying to create a query.
I would like the output to look like this in 17 columns:
Name N_name1 N_name2 N_name3 ... N_name16
John ten twenty thirty ... onehundredsixty
Jane thirty fifty twenty ... ten

I have 3 tables. the first looks like this with 17 colums:
Name N1 N2 N3 ... N16
John 10 20 30 ... 160
Jane 30 50 20 ... 10

the second and third look like this with 2 columns
N N_name
10 ten
20 twenty
30 thirty
40 forty
50 fifty

These 2 tables could be combined, but this is how they are right now.

The relationship connects the N to N1,N2,N3,...,N16

Can anyone help? I would really appreciate it.

Thanks in advance