how to join two query in one

[confuse]

I have a query named netincome query, c0mpose of two query named petshop query and clinic query. I want to display the total value of this two query.

Following are example of data that i want to display

Date--------Petshop query------Klinik Query--- Sales
09/10/08----260.00-- ----------500.00--------$750.00
09/10/08----300.00----------------------------300.00
09/10/08----354.55----------------------------354.55

But when i try join the two query i cant get this result, and if i use Left Join i can get only the values of one table.


Can anyone help me to join this two table and display both tables.

Hope to find answer...Thanks

 

[redneckgeek]

What are the common fields between the two queries? There must be something other than date. Is it possible to have value in the clinic query without having a value in petshop AND vice versa?

 

[confuse]

What are the common fields between the two queries? There must be something other than date. Is it possible to have value in the clinic query without having a value in petshop AND vice versa?

The common field is OrderId.yeah its possible that one query dont have the value.


Thanks for the reply....Hope you can help me....

 

[Rich]

Use a Union query, examples can be found in the Help files

 

[confuse]

Use a Union query, examples can be found in the Help files

Thanks for your idea rich.... i applied that, but im not sure if i got the correct syntax, because instead of displaying 4 fields(orderid,orderdate,total,totalcharges) it display two fields only the value of total and totalcharges field was inserted to orderdate.

here is my sample code...
SELECT [OrderID], [OrderDate] FROM [Orders]
UNION SELECT [OrderID], [Total] FROM [Order Details Petshop Query];
UNION SELECT [OrderID],[TotalCharges] FROM [Order Details Query Query];

 

[Rich]

That's because there are only two fields in your Union query, use alias's to get it up to four

 

[confuse]

That's because there are only two fields in your Union query, use alias's to get it up to four

sorry....
Is this correct??

SELECT [OrderID], [OrderDate] FROM [Orders]
UNION SELECT [OrderID], [Total] AS [Petshop] FROM [Order Details Petshop Query];
UNION SELECT [OrderID],[TotalCharges] AS [Klinik] FROM [Order Details Query Query];

It doesnt work to me....

Apology....

 

[confuse]

i got it rich....thanks for your idea....anyway i got another problem, how to put ZERO value if no data in one of the field??

 

[confuse]

so much headache with this..

i use Nz([fieldname],0), it works with both field are null, but if one field have value then it Nz doesnt work..and it will affect my report...


Please do help guys.....

 

[Rich]

so much headache with this..

i use Nz([fieldname],0), it works with both field are null, but if one field have value then it Nz doesnt work..and it will affect my report...


Please do help guys.....

The easiest way is to use and Update query on the original tables and replace the Nulls with 0