UNION QUERY NOT WORKING

[Guest]

i have created a field in a table that is based on (retrieves data) from two
different fields from two different tables. the table is working fine but the
form i have created based on the table fails to work properly with the
particular field.

the code is: "SELECT [DESCRIPTION] AS [liabilities]
FROM [IN-OUT-TBL] WHERE [TYPE]="DEBIT"

UNION SELECT [EXPENSES] AS [liabilities]
FROM [EXPENSES-MIX-FIX-TBL]

the error message i get when working with the form specifies that the code
contains punctuation errors and it is too long!

but why is the table working then?

pls give me a hint!

 

[Jeff Boyce]

Angie

How did you do that? How did you get Access to retrieve data into one
table, based on other tables' data?

More info, please...

Jeff Boyce
<Access MVP>

 

[Guest]

i have a table named IN-OUT-TBL containing a field named DESCRIPTION.
then a have a table named EXPENSES-MIX-FIX-TBL containing a field named
EXPENSES.
finally i have a table named PROFIT-TBL containing a lookup field based on
the following union query (in order to create a field that retrieves data
from two fields from two different tables):
"SELECT [DESCRIPTION] AS [liabilities]
FROM [IN-OUT-TBL] WHERE [TYPE]="DEBIT"

UNION SELECT [EXPENSES] AS [liabilities]
FROM [EXPENSES-MIX-FIX-TBL]"

does this help???!
Ο χÏήστης "Jeff Boyce" έγγÏαψε:

Angie

How did you do that? How did you get Access to retrieve data into one
table, based on other tables' data?

More info, please...

Jeff Boyce
<Access MVP>

i have created a field in a table that is based on (retrieves data) from two
different fields from two different tables. the table is working fine but the
form i have created based on the table fails to work properly with the
particular field.

the code is: "SELECT [DESCRIPTION] AS [liabilities]
FROM [IN-OUT-TBL] WHERE [TYPE]="DEBIT"

UNION SELECT [EXPENSES] AS [liabilities]
FROM [EXPENSES-MIX-FIX-TBL]

the error message i get when working with the form specifies that the code
contains punctuation errors and it is too long!

but why is the table working then?

pls give me a hint!

 

[Jeff Boyce]

Angie

It may be that I'm interpreting the terminology you are using differently
than you are.

First, a UNION query is typically used to combine two sources into one list.
For it to work AND be meaningful, you need both the same number of fields
and the same "type" of fields. The SQL you provided implies (if the
fieldnames are indicative) that you are combining a field that describes
something ([DESCRIPTION]) and a field that gives an amount ([EXPENSES]) into
a single field/list. These do NOT appear to be the same type of data.

Your SQL statement below implies that you consider both of these
"liabilities". How does this relate to the third table you've described?

I'm wondering if it would help if you described your underlying data
structure a bit more, and provided a description of what (business need) you
are trying to satisfy. You've been describing "how" you want to do
something, but I'm not clear on the "why" of that thing, whatever it may be.

--
Good luck

Jeff Boyce
<Access MVP>

i have a table named IN-OUT-TBL containing a field named DESCRIPTION.
then a have a table named EXPENSES-MIX-FIX-TBL containing a field named
EXPENSES.
finally i have a table named PROFIT-TBL containing a lookup field based on
the following union query (in order to create a field that retrieves data
from two fields from two different tables):
"SELECT [DESCRIPTION] AS [liabilities]
FROM [IN-OUT-TBL] WHERE [TYPE]="DEBIT"

UNION SELECT [EXPENSES] AS [liabilities]
FROM [EXPENSES-MIX-FIX-TBL]"

does this help???!
Ο χÏήστης "Jeff Boyce" έγγÏαψε:

Angie

How did you do that? How did you get Access to retrieve data into one
table, based on other tables' data?

More info, please...

Jeff Boyce
<Access MVP>

i have created a field in a table that is based on (retrieves data)

from
two

different fields from two different tables. the table is working fine

but
the

form i have created based on the table fails to work properly with the
particular field.

the code is: "SELECT [DESCRIPTION] AS [liabilities]
FROM [IN-OUT-TBL] WHERE [TYPE]="DEBIT"

UNION SELECT [EXPENSES] AS [liabilities]
FROM [EXPENSES-MIX-FIX-TBL]

the error message i get when working with the form specifies that the code
contains punctuation errors and it is too long!

but why is the table working then?

pls give me a hint!