Re: Union Query not working yet

Hi,


I *think* we have to explicitly mentioned the fields, when we use a UNION.


That can be done, in the query designer by double clicking on the "caption"
of the table, in the upper part, that will select all the fields, then drag
and drop them in the grid, that gives all the fields.



Hoping it may help,
Vanderghast, Access MVP


"Krizhek" <krizhek*gmail*com> wrote in message
news:4F3C3627-E171-4331-A31B-ADEE83B93FD0@xxxxxxxxxxxxxxxx
> Try this:
>
> SELECT [A].* FROM [Visa] as A
> UNION ALL SELECT [B].* FROM [Visa2] as B;
>
> Hope this works.
>
>
> "SheriTingle" wrote:
>
>> Hello again. I can't seem to get this union query thing going right. I
>> just
>> posted a question about 7 items down the list with the subject "union
>> query
>> still a problem". I have tried everything people have suggested to fix
>> the
>> union problem but what keeps happening is that when I make the suggested
>> changes the query runs without an error message but the data selected
>> from
>> the tables is not the correct information. Instead of selecting the most
>> recent date it selected the first date in the list or the first date in
>> the
>> current year. I need the original query to run as written to select the
>> most
>> recent date.
>>
>> Here's the original post:
>>
>> Thanks for the earlier help but I still can't seem to make this work. I'm
>> using Office 2003.
>>
>> I have 4 queries that hold credit card data. Each one finds the most
>> recent
>> date of the payment and therefore the current balance on that credit
>> card.
>> Now I want to make a union query that puts all four of them into one
>> query.
>> When I copy the SQL syntax from the original queries I get a message that
>> tells me the "ORDER BY expression ([table].field) includes fields that
>> are
>> not selected by the query. Only those fields requested in the first query
>> can
>> be included in an ORDER BY expression."
>>
>> When I posted this last night the suggestion was to use only one order by
>> query placing it at the end but when i did that the query gave me the
>> wrong
>> dates and data. Maybe with the exact syntax life would be easier. THANKS
>> !
>>
>> Here's my syntax:
>>
>> SELECT TOP 1 [VISA].StatementDate, [VISA].Category, [VISA].NewBalance,
>> [VISA].MinimumPaymentDue, [VISA].AccountNO
>> FROM [VISA]
>> WHERE ((([VISA].NewBalance)<>0))
>> ORDER BY [VISA].StatementDate DESC;
>>
>> UNION
>>
>> SELECT TOP 1 [VISA2].StatementDate, [VISA2].Category, [VISA2].NewBalance,
>> [VISA2].MinimumPaymentDue, [VISA2].AccountNO
>> FROM [VISA2]
>> WHERE ((([VISA2].NewBalance)<>0))
>> ORDER BY [VISA2].StatementDate DESC;
>>
>> You all rock being willing to help so much !!!
>>
>>


.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Management Studio & Views containing unions
    ... display the View, which I guess is OK, the old tool couldn't do that ... the syntax verification doesn't work either.. ... "Incorrect Syntax near UNION" error each time. ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)