Union Query problem 1

[Xenocide]

I'm doing this :

SELECT [Table850].[IdEngagementA] as Engagement1, [Table850].[Description] as Description1
FROM Table850
WHERE ((([Table850].[IdEngagementA])="00-000000-00"))

UNION SELECT [Table807].[IdEngagementA] as Engagement1, [Table807].[Description] as Description1
FROM Table807
WHERE ((([Table807].[IdEngagementA])="00-000000-00"));

The problem is that it only give me the result from the first select. I know that the problem come from my WHERE since if I take out the second WHERE it give me result from the second select

Why I can,t do that is that it give me all idEgagementA from the second table and I just want the 00-000000-00 one.

How can I modify my query so I can get result from both select as well as having my WHERE

 

[Golom]

Try UNION ALL. The UNION statement eliminates duplicates.

 

[Xenocide]

That doesn,t work.. the result it still the same...

normally it should give me

Budget Annuel 850
Budget Annuel 807

but until now if only give me

Budget Annuel 850

if I take out the where from the second select.. it give me

Budget Annuel 850
Budget Annuel 807
and another bunch of description from the Table807

 

[PHV]

What is the value of Table807.IdEngagementA when Table807.Description="Budget Annuel 807" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

the value is 00-000000-00

exactly as the one in table850

I just tried to change 00-000000-00 to 00-000000-01 for Table807 in the table to see if it was because IdEngagementA for Table850 and Table807 where the same but doesn't change anything so I guess it's not because of that

 

[lespaul]

If you just run the second query:

SELECT [Table807].[IdEngagementA]  as Engagement1, [Table807].[Description] as Description1
FROM Table807
WHERE ((([Table807].[IdEngagementA])="00-000000-00"));

do you get the results you are expecting?

Leslie

 

[Golom]

I assume that "Budget Annuel 850" and "Budget Annuel 807" are values in the "Description1" fields from the two tables. What values are displayed for the "Engagement1" field that correspond to those "Description1" field values?

If you run this as a stand-alone query

SELECT [IdEngagementA]  as Engagement1,
       [Description]    as Description1

FROM Table807

WHERE IdEngagementA = "00-000000-00"

(i.e. the second SELECT in the UNION) ... what do you get? If it returns nothing as a stand-alone query then it will contribute nothing to the union.

 

[Xenocide]

ok now it work.. altought I still wonder

The where in the Table807 one was causing trouble even on it's own. Even if it's really wrote 00-000000-00 and I even set the table so you can only enter 00-000000-AA;;_ for IdDescriptionA I had to write

WHERE IdEngagementA = "0000000000" without the -

Now I'm trying to figure out.. what the hell? Table807 was created before Table850.. which is only a copy paste of the 807 one. and

WHERE IdEngagementA = "00-000000-00" work really well in Table850

 

[PHV]

Check the DataType and InputMask of each IdEngagementA field when in table design view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

Exactly the same.. that's what's weird -_-'

Well I'll try rebuilding the Table807 table from scratch to see

 

[Xenocide]

ok I redid my table807 ... still does the same type of error... I still have to write 0000000000 instead of
00-000000-00 in the WHERE

my Datatype is Text like in table805 (the last two 0 can sometime be letter)

and the Inputmask is 00-000000-AA;;_ exactly like table850... I really don't understand what is wrong in this table if someone can help me I'll be glad

btw thanks all for yur previous help

 

[PHV]

ok I redid my table807
Rename table807 to, say, table807copie
Copy'n'paste table850 to table807
In datasheet view modify the contents of the Table807.Description fields
Now retest the WHERE clause for this new table807

Another test to do, against table850:
add a new record and check if the criteria is the same for this newly created record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

did change anything.. stuck with the same problem.. I'll find a way around it eventually =^_^+

Thanks for yur help all

oh and can a Update query have an UNION?

like in this one

UPDATE Table850 SET MontantActuel = [Forms]![TransfertEngagement]![txt2]
WHERE IdEngagementA=[Forms]![TransfertBudget]![lst2]

UNION

UPDATE Table807 SET MontantActuel = [Forms]![TransfertEngagement]![txt2]
WHERE IdEngagementA=[Forms]![TransfertBudget]![lst2];

which doesn,t work by the way

 

[PHV]

The keyword UNION is only valid between two SELECT instructions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

thanks PHV you hepled a lot =^_^+

I still have a question to finish all my stuff but I posted it under a new name