date

[Samual]

NEED MORE HELP: union queries

the database is a bookshop. ive got a problem concerning 2 oif the tables: misc. expenditure (rent , bills etc.) + book expenditure (purchasing books fom suppliers). ive also done a report based upon a parametr query which allows produces details of expenditure in the specified period. the problem is that because they are 2 seperate tables, the report only shows the expenditure where the expenditure ID in both tables is the same. so how can i get it to show ALL expenditure from BOTH tables on ALL dates
thanks

 

[dcx693]

I haven't done exactly what you're asking about before, but I think the solution is a union query. The union query basically takes two set of data and makes them into one. (It's nice if the data structures that you're melding are similar, but not necessary.)

The basic form of the query is SELECT (field list) FROM tblMiscExpenditure UNION SELECT (field list) FROM tblBookExpenditure.

You cannot graphically see a union query in the Access QBE design window. So when you have the MiscExpenditure and the BookExpenditure tables open in query design view, go to the Query menu, choose SQL Specific, and then Union. You'll have to type in the text of the query itself.

 

[Mile-O]

Samual, how many forums are you intending to ask this question in?

Your first post was enough.

 

[jeremie_ingram]

A simplistic way to create a union query is to use the query builder and make the selections from the first table, switch to SQL view, then COPY the SQL statement. Clear the builder and then add the table and select the needed fields for the other potion of the union. Switch to the SQL view, add the text UNION ALL and PASTE the code copied from the first SQL statement.
This is very useful, fast, and prevents typing errors.

 

[Rich]

The best way is just to have One expenditure table!

 

[Samual]

UNION QUERIES

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