ACCESS FEED: Re: [MS_AccessPros] MAKE TABLE QUERY INVOLVING UNION QUERY

Rabu, 21 November 2012

Re: [MS_AccessPros] MAKE TABLE QUERY INVOLVING UNION QUERY

Nathan-

I don't thing that's possible because your "input" query is a UNION. You
could try:

SELECT [Name], FirstAdd, FirstCity
INTO NewTable 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 X

But I don't think you can imbed a UNION query in a FROM clause.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: candrope <candrope@yahoo.com.br>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 21, 2012 9:48 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] MAKE TABLE QUERY INVOLVING UNION QUERY

Mr Viescas

I'd like not to save another query, ie, I'd prefer to insert the INTO
clause into my SQL code (appended below) so that just one query makes all
the task. Is it possible?

Nathan

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Nathan-
>
> If your query is giving you the results you want, then save it and give
>it
> a name. Then build a Make Table (SELECT INTO) or Append (INSERT) query
> using your saved query as the data source.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: candrope <candrope@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Wednesday, November 21, 2012 2:52 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: Re: [MS_AccessPros] MAKE TABLE QUERY INVOLVING UNION QUERY
>
> Hi Mr Viescas
>
> It is an honor receiving your feedback here in Brazil.
> I agree with you about your warning. It is not safe using First Of...to
> catch data, but to reach my goal (rebuild a table that I received with
> inconsistent data) it worked fine.
> So back to the merit of my issue, I tried to insert the INTO clause in
> different ways into my SQL code (appended below) but none of them worked.
> Could you help me?
>
> Nathan
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > Candrope (name?)-
> >
> > What are you trying to do here? Why are you using a Totals query?
>Using
> > First is totally unreliable - it fetches the "first" physical record
>that
> > meets the criteria. You could certainly build a Make Table query using
> > this as input, but not sure you'll get what you want.
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: candrope <candrope@>
> > Reply-To: <MS_Access_Professionals@yahoogroups.com>
> > Date: Tuesday, November 20, 2012 9:03 PM
> > To: <MS_Access_Professionals@yahoogroups.com>
> > Subject: [MS_AccessPros] MAKE TABLE QUERY INVOLVING UNION QUERY
> >
> > 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?
> > Thanks in advance.
> > ----------------------------------------------------------
> > 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;
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links