Multitable Multifield query

[bbulla]

Hi,

I have two tables with similar data. Table A has ToMH and FromMH. Table B has MH.

I need to get a comprehensive list of all MHs from both tables, grouped so there are no duplicates.

How would I do this in a query?

Thanks,

 

[jdraw]

How about describing what you are trying to do. And some sample data.

 

[nanscombe]

I did this in 2 parts:


qry_bbulla01

SELECT tblBB1.ToMH AS MH
FROM tblBB1
UNION ALL
SELECT tblBB1.FromMH AS MH
FROM tblBB1
UNION ALL
SELECT tblBB2.MH
FROM tblBB2;

qry_bbulla01 is a UNION query which pulls together a list of ALL of the values in ToMH, FromMH and MH and calls them all MH.
The output from this query contains duplicates and NULL fields.


qry_bbulla02

SELECT DISTINCT qry_bbulla01.MH
FROM qry_bbulla01
ORDER BY qry_bbulla01.MH;

This query takes the results of qry_bbulla01 and turns it into a set of distinct values.

I hope this helps.

 

[bbulla]

That's exactly what I needed. I think I was getting messed up in the UNION query by not giving a common alias to each one....like MH. Here is my final query:

SELECT DISTINCT MH
FROM [SELECT JETTING.toMH as MH
FROM JETTING
UNION ALL
SELECT JETTING.fromMH as MH
FROM JETTING
UNION ALL
SELECT MH_INSP.FACID as MH
FROM MH_INSP]. AS [%$##@_Alias]
ORDER BY MH;

I'm not sure why Access throws in that wierd part at the end (. AS [%$##@_Alias]) once I go back into design view after I save it.