Union query

  • Thread starter Thread starter Steve167
  • Start date Start date
S

Steve167

There got two tables and I needed join them into one:

-------------------------------------------
Table1 (Master)
-------------------------------------------
Parts1 Desc1
Parts2 Desc2
Parts3 Desc3
:

-------------------------------------------
Table2 (Child)
-------------------------------------------
SubPart1 Parts1 Vendor1 rev.0
SubPart2 Parts1 Vendor1 rev.1
SubPart3 Parts1 Vendor2 rev.0
SubPart4 Parts1 Vendor3 rev.0
SubPart5 Parts2 Vendorx rev.0
SubPart6 Parts2 Vendorx rev.1
:

==========================
The final result is look like this, all SubParts with same Vendor but in old
revsion will be excluded:

Parts1 SubPart2 Parts1 Vendor1
rev.1
Parts1 SubPart3 Parts1 Vendor2
rev.0
Parts1 SubPart4 Parts1 Vendor3
rev.0
Parts2 SubPart6 Parts2 Vendorx
rev.1
Parts3 .....



I have try SELECT DISTINCT & LEFT JOIN, but cannot get the above result.
Is it possible for MS Access 2000 to doing this?
 
I don't think you want to use a union query. It looks like you need a query
that joins the two tables via the Master Part. However, it is unclear why,
for example, SubPart1 doesn't show up in your result at all. If it is simply
because you only want the highest revision part from a specific vendor, then
you need to create another query that returns this information:

SELECT Vendor, Max(Revision) AS MaxRev FROM Table2 GROUP BY Vendor;

Then you link vendor and revision to Table2 so that only those results can be
returned. HTH
 
Thanks!! Its worked. I wrote 2 queries to join the master & child tables.

It would be great if the joined table can be formed by a single query (may
be its impossible).
 
Back
Top