Combine Fields from two Tables

[mrussell71]

I have two tables, tblPart and tblFootnote, and I need to combine data from both of the tables. tblPart contains data for all parts available on a particular vehicle. Fields for tblPart are:
VID, MFG, PART_NUM, PART_DEF, DESCRIPTION, FN1, FN2, FN3, FN4, and FN5

Fields for the tblFootnote are:
FootnoteID, and Footnote

What I want to do is concatenate the DESCRIPTION field with the Footnote depending what FootnoteID is displayed in the FN* fields.

Any help would be greatly appreciated.

 

[oldduffer]

You can concatenate two fields using the plus symbol and collect the correct footnote using a simple where clause, try the following:

SELECT Part.VID, Part.MFG, Part.DESCRIPTION + Foot.Footnote
FROM tblPart as Part, tblFoot as Foot
WHERE Part.FN1 = Foot.FootNoteID
OR Part.FN2 = Foot.FootNoteID ....etc.


see how you go with this

 

[mrussell71]

Thanks oldduffer.
This works to an extent, however if a record has more than one 'footnote', then this query will not add both of these footnotes to the DESCRIPTION field.
ex.
If a Part has an value in FN1 and in FN2, it will make two records, the first has DESCRIPTION + FN1 and the second has DESCRIPTION + FN2.

What I would like to happen would be to have the query
(1)look at FN1 and if it is null, then just have a record based on the fields in tblPart
(2)if FN1 has a value, concatenate DESCRIPTION + FN1
(3)if FN2 has a value, concatenate DESCRIPTION + FN1 + FN2
....

If there is a value in FN2 then there has to be a value in FN1 and so on through FN5.

Thanks for you help.