Full Join Query/Union

[Esaw]

I have created the following UNION query to get all of the contracts and all
of the royalties together, even if either do not have a contract number. This
UNION query is not showing the Royalties that do not have a Contract Number
assigned to them but it is showing the Contracts that do not have a Contract
Number. I've tried to add WHERE [Royalties
Query].Royalties.ContractNumber.Value IS NULL and that does not change the
results. Does anyone have any suggestions?

SELECT ContractsQuery.ClientName, ContractsQuery.ContractNumber,
ContractsQuery.ArtistName, ContractsQuery.StyleNumber,
ContractsQuery.StyleDescription, ContractsQuery.ContractStart,
ContractsQuery.ContractEnd, ContractsQuery.Product, [Royalties
Query].RoyaltyAmount, [Royalties Query].[TT%], [Royalties Query].DatePaid,
[Royalties Query].Notes, [RoyaltyAmount]*[TT%] AS TTSCommission,
[RoyaltyAmount]-[TTSCommission] AS ArtistCommission
FROM ContractsQuery LEFT JOIN [Royalties Query] ON
ContractsQuery.ContractNumber = [Royalties
Query].Royalties.ContractNumber.Value;
UNION SELECT ContractsQuery.ClientName, ContractsQuery.ContractNumber,
ContractsQuery.ArtistName, ContractsQuery.StyleNumber,
ContractsQuery.StyleDescription, ContractsQuery.ContractStart,
ContractsQuery.ContractEnd, ContractsQuery.Product, [Royalties
Query].RoyaltyAmount, [Royalties Query].[TT%], [Royalties Query].DatePaid,
[Royalties Query].Notes, [RoyaltyAmount]*[TT%] AS TTSCommission,
[RoyaltyAmount]-[TTSCommission] AS ArtistCommission
FROM ContractsQuery RIGHT JOIN [Royalties Query] ON
ContractsQuery.ContractNumber = [Royalties
Query].Royalties.ContractNumber.Value;

 

[Esaw]

Okay so it is showing me the Royalties without contract Numbers. But now I
would like to be able to show the ClientName, ArtistName, StyleNumber, Style
Description from the Royalties Query but only if there is no contract number
or contract info connected to it. Is there a way to do this?

 

[Lord Kelvan]

i have no idea why you are using a union query because each part is
exatally the same

what i understand is you want all royalties information if there is
not contact details or if there are contact details but no contact
number

i dont understand what this means

[Royalties Query].Royalties.ContractNumber.Value

is that a form or something cause you have a sereies of dot notations

because as far as i can see you are joining the queries on the contact
number if there is no contact number in the contact query for that
royalty then there are no contact details
so all you need is

SELECT ContractsQuery.ClientName, ContractsQuery.ContractNumber,
ContractsQuery.ArtistName, ContractsQuery.StyleNumber,
ContractsQuery.StyleDescription, ContractsQuery.ContractStart,
ContractsQuery.ContractEnd, ContractsQuery.Product, [Royalties
Query].RoyaltyAmount, [Royalties Query].[TT%], [Royalties
Query].DatePaid,
[Royalties Query].Notes, [RoyaltyAmount]*[TT%] AS TTSCommission,
[RoyaltyAmount]-[TTSCommission] AS ArtistCommission
FROM ContractsQuery INNER JOIN [Royalties Query] ON
ContractsQuery.ContractNumber = [Royalties
Query].ContractNumber
where [Royalties Query].ContractNumber is null

i changed that line i didnt understand to
[Royalties Query].ContractNumber
changed left join to inner join
and added the where clause
where [Royalties Query].ContractNumber is null

 

[Esaw]

I guess I don't really know why I'm using a union query either, but I do know
that's how I've been able to get the info I need. What was going on was if
I did a left join I got all of the contracts, including those without
royalties and if I did a right join I got all Royalties even if there is not
a contract to go with them. So I put them together and was able to get all of
them together.

[Royalties Query].Royalties.ContractNumber.Value I think this must be the
multi value box I set up so royalties could be connected to more than one
ContractNumber.

I tried the query you gave me and there is a Syntax Error in JOIN operation

 

[Lord Kelvan]

you would have to paste yout query structure tell me each field that
is in your queries so i can try to understand