Another Join Question

[JohnW]

I am sorry to raise another question about table joins but, here goes....

Using the following SQL code:

SELECT

• .[Vendor_No],
  • .[Item_Desc],
    • .[Desc_2],
      • .[Prime_Vendor],
        • .[Production_Cat],
          • .[Production_Sub_Cat],
            [Where].[Foundry Ref #], [Where].[Location], [Where].[Style], [Where].[Date
            In/Out]
            FROM [Where] LEFT JOIN
            • 
              ON [Where].[Foundry Ref #] =
              • .[Vendor_No];
                UNION
                SELECT
                • .[Vendor_No],
                  • .[Item_Desc],
                    • .[Desc_2],
                      • .[Prime_Vendor],
                        • .[Production_Cat],
                          • .[Production_Sub_Cat],
                            [Where].[Foundry Ref #], [Where].[Location], [Where].[Style], [Where].[Date
                            In/Out]
                            FROM [Where] RIGHT JOIN
                            • 
                              ON [Where].[Foundry Ref #] =
                              • .[Vendor_No]
                                
                                
                                The result is ok, but the table has a collumn for Foundry Ref# and one for
                                Vendor_No e.g.
                                
                                FRef# OtherFields Vendor_No
                                101 blaha
                                102 blahb
                                103 blahc 103
                                104 blahd 104
                                blahe 105
                                blahf 106
                                
                                
                                What I really expect is a single collumn that represents Foundry Ref# &
                                Vendor_No e.g.
                                
                                FR/VN OtherFields
                                101 blaha
                                102 blahb
                                103 blahc
                                104 blahd
                                105 blahe
                                106 blahf
                                
                                Any ideas? I am sure there is an easy fix.
                                
                                Thanks,
                                JohnW

 

[Ken Snell \(MVP\)]

Look at your list of fields in the SELECT clause. The first one is Vendor_No
in both subqueries in the UNION query. The seventh field is Foundry Ref#. To
get those two fields to be in the same column, they must be in the same
position in the field list, for example:

SELECT Vendor_No
FROM Table1
UNION
SELECT [Foundry Ref#]
FROM Table1


So, if you want them combined, just show one field and alias it:

SELECT

• .[Vendor_No] AS [FR/VN],
  • .[Item_Desc],
    • .[Desc_2],
      • .[Prime_Vendor],
        • .[Production_Cat],
          • .[Production_Sub_Cat],
            [Where].[Location], [Where].[Style], [Where].[Date
            In/Out]
            FROM [Where] LEFT JOIN
            • 
              ON [Where].[Foundry Ref #] =
              • .[Vendor_No];
                UNION
                SELECT
                • .[Vendor_No],
                  • .[Item_Desc],
                    • .[Desc_2],
                      • .[Prime_Vendor],
                        • .[Production_Cat],
                          • .[Production_Sub_Cat],
                            [Where].[Location], [Where].[Style], [Where].[Date
                            In/Out]
                            FROM [Where] RIGHT JOIN
                            • 
                              ON [Where].[Foundry Ref #] =
                              • .[Vendor_No]

 

[John Spencer]

Use a Calculated field in your queries to get the first value.

SELECT NZ(

• .[Vendor_No],[Where].[Foundry Ref #]) as [FR/VN]
  ,
  • .[Item_Desc]
    ,
    • .[Desc_2]
      ,
      • .[Prime_Vendor]
        ,
        • .[Production_Cat]
          ,
          • .[Production_Sub_Cat]
            , [Where].[Location]
            , [Where].[Style]
            , [Where].[Date In/Out]
            FROM [Where] LEFT JOIN
            • 
              ON [Where].[Foundry Ref #] =
              • .[Vendor_No];
                UNION
                SELECT NZ(
                • .[Vendor_No],[Where].[Foundry Ref #]) as [FR/VN]
                  ,
                  • .[Item_Desc]
                    ,
                    • .[Desc_2]
                      ,
                      • .[Prime_Vendor]
                        ,
                        • .[Production_Cat]
                          ,
                          • .[Production_Sub_Cat]
                            , [Where].[Location]
                            , [Where].[Style]
                            , [Where].[Date In/Out]
                            FROM [Where] RIGHT JOIN
                            • 
                              ON [Where].[Foundry Ref #] =
                              • .[Vendor_No]
                                
                                
                                '====================================================
                                John Spencer
                                Access MVP 2002-2005, 2007-2008
                                The Hilltop Institute
                                University of Maryland Baltimore County
                                '====================================================

 

[JohnW]

Thankyou, worked first time!

Use a Calculated field in your queries to get the first value.

SELECT NZ(

• .[Vendor_No],[Where].[Foundry Ref #]) as [FR/VN]
  ,
  • .[Item_Desc]
    ,
    • .[Desc_2]
      ,
      • .[Prime_Vendor]
        ,
        • .[Production_Cat]
          ,
          • .[Production_Sub_Cat]
            , [Where].[Location]
            , [Where].[Style]
            , [Where].[Date In/Out]
            FROM [Where] LEFT JOIN
            • 
              ON [Where].[Foundry Ref #] =
              • .[Vendor_No];
                UNION
                SELECT NZ(
                • .[Vendor_No],[Where].[Foundry Ref #]) as [FR/VN]
                  ,
                  • .[Item_Desc]
                    ,
                    • .[Desc_2]
                      ,
                      • .[Prime_Vendor]
                        ,
                        • .[Production_Cat]
                          ,
                          • .[Production_Sub_Cat]
                            , [Where].[Location]
                            , [Where].[Style]
                            , [Where].[Date In/Out]
                            FROM [Where] RIGHT JOIN
                            • 
                              ON [Where].[Foundry Ref #] =
                              • .[Vendor_No]
                                
                                
                                '====================================================
                                John Spencer
                                Access MVP 2002-2005, 2007-2008
                                The Hilltop Institute
                                University of Maryland Baltimore County
                                '====================================================
                                
                                

                                I am sorry to raise another question about table joins but, here goes....
                                
                                Using the following SQL code:
                                
                                SELECT

                                • .[Vendor_No],
                                  • .[Item_Desc],
                                    • .[Desc_2],
                                      • .[Prime_Vendor],
                                        • .[Production_Cat],
                                          • .[Production_Sub_Cat],
                                            [Where].[Foundry Ref #], [Where].[Location], [Where].[Style], [Where].[Date
                                            In/Out]
                                            FROM [Where] LEFT JOIN
                                            • 
                                              ON [Where].[Foundry Ref #] =
                                              • .[Vendor_No];
                                                UNION
                                                SELECT
                                                • .[Vendor_No],
                                                  • .[Item_Desc],
                                                    • .[Desc_2],
                                                      • .[Prime_Vendor],
                                                        • .[Production_Cat],
                                                          • .[Production_Sub_Cat],
                                                            [Where].[Foundry Ref #], [Where].[Location], [Where].[Style], [Where].[Date
                                                            In/Out]
                                                            FROM [Where] RIGHT JOIN
                                                            • 
                                                              ON [Where].[Foundry Ref #] =
                                                              • .[Vendor_No]
                                                                
                                                                
                                                                The result is ok, but the table has a collumn for Foundry Ref# and one for
                                                                Vendor_No e.g.
                                                                
                                                                FRef# OtherFields Vendor_No
                                                                101 blaha
                                                                102 blahb
                                                                103 blahc 103
                                                                104 blahd 104
                                                                blahe 105
                                                                blahf 106
                                                                
                                                                
                                                                What I really expect is a single collumn that represents Foundry Ref# &
                                                                Vendor_No e.g.
                                                                
                                                                FR/VN OtherFields
                                                                101 blaha
                                                                102 blahb
                                                                103 blahc
                                                                104 blahd
                                                                105 blahe
                                                                106 blahf
                                                                
                                                                Any ideas? I am sure there is an easy fix.
                                                                
                                                                Thanks,
                                                                JohnW