Query Relationships

[Ryan0001]

ive got a bookshop database + have a problem with 2 of the tbls: 'BookExpenditure' (money spent on books) + 'MiscExpenditure' (rent, bills etc.) i also have a report based upon a parameter query which shows the expenditure when you type the begining and end date. the problem is that the report only shows expenditure for dates which are equal on both tbls, which is due to the relationships. i tried changing the query relationship, but there were only 2 other options: to show all dates on 'BookExpenditure' tbl and only those that are the same from 'MiscExpenditure' tbl or vice versa. i want to show ALL dates from BOTH tbls with the corresponding expenditure. also, i want them to be 2 seperate tbls.
I would appreciate any help. Thankds

 

[r937]

your query is probably a join query

what you want is a union query

not sure if you need to totally rebuild it, or whether you can just go into Design view and click the query type for Union

holler if you want me to convert the sql for you

rudy

 

[Ryan0001]

Thanks a lot…the union query was the solution and it worked. However ive got a few more questions concerning this topic and would appreciate if anyone could answer them:
1- I wanted to put a parameter on the query, but it wouldn’t work. So I created a normal query, added all the fields from the union query and put the parameter in the criteria box which worked. However, I want to get rid of this query and put the parameter on the union query. Is this possible, and how would I do so with the following union query and parameter:-
Union Query:
SELECT [Amount], [Date], [Details]
FROM [MiscExpenditure]
UNION SELECT [Total], [PurchaseDate], “Purchase stock from” &“ ”& [CompanyName]
FROM [ExpenditureQuery]
ORDER BY [Date];
Parameter: Between [Enter start date:] And [Enter end date:]

2- Also in the union query I created I had to create a further query (ExpenditureQuery) where I performed all calculations and then referred to this in the union query. Is it possible to just have the union query and perform the calculations in that????
APPRECIATE ANY HELP