Newbie question: merge fields from subtable into query

[Joost Nicasie]

Hi all,

This is my problem.

Main table has an id-field called 'Code'. Subtable has 1 or more
records per code (one-to-many relation). I want to 'merge' a few
(text)-fields into one query-field.

Example:

Main table
==========
Code
Field1
Field2
etc.


Subtable
========
ID
Code
Number
FieldA
FieldB


Query
=====
Code
Field1
Field2
Q_1 (=FieldA & FieldB where Number=1)
Q_2 (=FieldA & FieldB where Number=2)

etc..

Hope you guys understand what I mean...

Cheers,
Joost


--
"I believe the light that shines on you
Will shine on you forever
And though I can't guarantee
There's nothing scary hiding under your bed
I’m gonna stand guard
Like a postcard of a Golden Retriever"
--Paul Simon

 

[John Spencer (MVP)]

SELECT A.Code, A.Field1, A.Field2,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q1,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q2
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code

Another way that may work for you

SELECT A.Code, A.Field1, A.Field2,
"Q" & S.[Number] As Title,
S.FieldA & S.FieldB As TitleValue
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code
GROUP BY A.Code, A.Field1, A.Field2,
"Q" & S.[Number],
S.FieldA & S.FieldB

 

[Joost Nicasie]

SELECT A.Code, A.Field1, A.Field2,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q1,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q2
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code

Thanks a lot, that almost solved my problem... but... I do get
multiple entries (per Code) in my query result, one with the Q1 field
filled, another one with the Q2 field filled, and another one with no
one filled... I'm sure there is a simple solution to this... but how?

Joost

 

[Joost Nicasie]

SELECT A.Code, A.Field1, A.Field2,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q1,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q2
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code

Thanks a lot, that almost solved my problem... but... I do get
multiple entries (per Code) in my query result, one with the Q1 field
filled, another one with the Q2 field filled, and another one with no
one filled... I'm sure there is a simple solution to this... but how?

Update: I get an entry for every record in the subtable...

Joost

--
"I believe the light that shines on you
Will shine on you forever
And though I can't guarantee
There's nothing scary hiding under your bed
I’m gonna stand guard
Like a postcard of a Golden Retriever"
--Paul Simon

 

[John Spencer (MVP)]

Yes, and I think I misunderstood your ultimate goal.

A minor change may come closer.
SELECT DISTINCT A.Code, A.Field1, A.Field2,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q1,
IIF(S.[Number]=2,S.FieldA & S.FieldB) as Q2
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code
WHERE S.[Number] in (1,2)

BUT I think what you may have to do is to make a UNION query

SELECT A.Code, A.Field1, A.Field2,
(S.FieldA & S.FieldB) as TheValue,
"Q1" as ColumnTitle
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code
WHERE S.[Number] = 1
UNION
SELECT A.Code, A.Field1, A.Field2,
(S.FieldA & S.FieldB) as TheValue,
"Q2" as ColumnTitle
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code
WHERE S.[Number] = 2
UNION
SELECT A.Code, A.Field1, A.Field2,
(S.FieldA & S.FieldB) as TheValue,
"Q3" as ColumnTitle
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code
WHERE S.[Number] = 3

Save that as SavedQuery and use it as the source of a crosstab query

TRANSFORM First(TheValue) as ShowThis
SELECT Code, Field1, Field2
FROM SavedQuery
GROUP BY Code, Field1, Field2
PIVOT [ColumnTitle]

SELECT A.Code, A.Field1, A.Field2,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q1,
IIF(S.[Number]=1,S.FieldA & S.FieldB) as Q2
FROM [Main Table] as A INNER JOIN [Subtable] as S
ON A.Code = S.Code

Thanks a lot, that almost solved my problem... but... I do get
multiple entries (per Code) in my query result, one with the Q1 field
filled, another one with the Q2 field filled, and another one with no
one filled... I'm sure there is a simple solution to this... but how?

Update: I get an entry for every record in the subtable...

Joost

--
"I believe the light that shines on you
Will shine on you forever
And though I can't guarantee
There's nothing scary hiding under your bed
I’m gonna stand guard
Like a postcard of a Golden Retriever"
--Paul Simon

 

[Joost Nicasie]

BUT I think what you may have to do is to make a UNION query

Allright... almost there. The only problem is, that not al the
Q*-fields seem to get filled... The SavedQuery-query seems to work
allright, but I keep getting some 'blanks' in the Crosstab Table...
Does this ring a bell? There doesn't seem to be any system in it, with
some records the Q2 is blank, in others the Q3 or Q4...

Thanks this far...
Joost

--
"I believe the light that shines on you
Will shine on you forever
And though I can't guarantee
There's nothing scary hiding under your bed
I’m gonna stand guard
Like a postcard of a Golden Retriever"
--Paul Simon

 

[Joost Nicasie]

Update: if I delete all Field1, Field2 etc. from the SELECT and the
GROUP BY statement in the crosstab query, the Q1-6 fields are properly
filled. So that works fine for me...

Thanks John. You're great.

Joost
--
"I believe the light that shines on you
Will shine on you forever
And though I can't guarantee
There's nothing scary hiding under your bed
I’m gonna stand guard
Like a postcard of a Golden Retriever"
--Paul Simon

 

[John Spencer (MVP)]

I'm stuck at this point. You will need to play with this and see if you can
determine why some are missing. Sorry.