MAKE TABLE QUERY INVOLVING UNION QUERY

Question

Hello everybody

I want to write an MS Access Make Table query in order to create a table called tblMyNewTable.

This query will use records retrieved from another MS Access query described below.

Is it possible? If so, how can I make it? I suppose I have to use the INTO clause.

Thanks in advance.

 NGF

---

SELECT tblA.name, First(tblA.address) AS FirstAdd, First(tblA.city) AS FirstCity

FROM tblA LEFT JOIN tblB ON tblA.NumInsc= tblB.NumInsc

GROUP BY tblA.name

HAVING tblA.name Is Not Null

UNION SELECT "Unknown", Null, Null

FROM tblA

ORDER BY tblA.name;

Answer 1

Use the UNION query as a subquery:

SELECT MyUnion.* INTO tblMyNewtable

FROM

   (SELECT tblA.name, First(tblA.address) AS FirstAdd, First(tblA.city) AS FirstCity

    FROM tblA LEFT JOIN tblB ON tblA.NumInsc= tblB.NumInsc

    GROUP BY tblA.name

    HAVING tblA.name Is Not Null

    UNION SELECT "Unknown", Null, Null

    FROM tblA

    ORDER BY tblA.name)  AS MyUnion;

However you are joining tblA to tblB with a LEFT OUTER JOIN, but not returning any columns from tblB, so including tblB in the query serves no purpose.  You are also using a HAVING clause, but a HAVING clause operates on the data after grouping and is used for things like HAVING SUM(OrderAmount) > 10000, e.g. to return customers for whom the sum of the values in an OrderAmount column exceeds 10,000 currency units.  In your case you simply wish to restrict the results to those where the Name column contains a value, so a WHERE clause should be used.

So, putting those points together:

SELECT MyUnion.* INTO tblMyNewtable

FROM

   (SELECT tblA.name, FIRST(tblA.address) AS FirstAdd, FIRST(tblA.city) AS FirstCity

    FROM tblA

    WHERE  tblA.name IS NOT NULL

    GROUP BY tblA.name

    UNION

    SELECT "Unknown", Null, Null

    FROM tblA

    ORDER BY tblA.name) AS MyUnion;

I assume that you are aware that the FIRST operator in essence returns a value from a random row, not the first in any sort order:  It is useful for ensuring that both values are from the same row, which I take it is why you've used it here, so long as the row in question is an arbitrary choice.

One final point; it's advisable to avoid Name as a column name as it's the name of a built in property, so as a 'reserved' word should not be used as an object name.

Answer 2

Mr Marshall

The warnings that you give me are all relevant, but since the data I received to work with were inconsistent I had to make some workaround (not so simple like you suggested) to solve the problem with the inconsistent data.

Thanks for your attention

Answer 3

Precisely in target, Mr Ken.

The warnings that you give me are all relevant, but since the data I received to work with were inconsistent I had to make some workaround to solve the problem with the inconsistent data.

Thanks a lot.

Nathan

Answer 4

Your use of GROUP BY without doing any aggregation appears to be a way to elimiate duplicate result records.  But the UNION part is also eliminating duplicates so the GROUP BY is not needed.

THe same kind of thinking appears to be the reason for the not null Where (should not use Having that way) along with the Union is just to get a single Unknown record.

If my reasoning is sound, then you can get the same results with the much simpler and potentially faster query:

SELECT DISTINCT Nz(tblA.name, "Unknown") As PersonName, address, city

FROM tblA

Answer 5

Why?  

Why not just use IIF(tblA.name = "Unknown", Null, tblA.Address and samw for city field?