I have 2 queries which both contain a field "month" . I want to connect both those queries when they have a common entry in "month" but I would also like to include those records which do not have a common field in "month" . I am aware of different type of joins but I cannot manipulate them to show all records from both sets and yet link those that are common. both tables have some entries in "month" which do not appear in the other so I cannot take all records from one and do an outer join to the other. Please help! Thanks!
Happy YN

Does this help:
http://www.access-programmers.co.uk/ubb/Forum3/HTML/000931.html

Yes Thanks I'll try it

no! I can join the fields normally but as soon as i try anouter join I get a message that the sql cannot be done due to ambiguous outer joins and I should first create a query with one join and then connect with that

I forgot to say that this field "month" contains duplicate values but There is another field which makes them unique and which I need to join aswell to the second table.
I know it sounds complicated but it isn't really
I will give more info if required to simplify the problem
thankx

If you cannot identify a unique record using the month field then you shouldn't be using it as a join. Use the unique field.

If I'm confused, please post an example of what you are trying to achieve, or better still post it to my email(A97 version) and I'll try and resolve it for you.

Ian

Basically I have a database which tracks income and expenses of different houses. I have built queries which list all the expenses of all houses for each month and another that does the same for income. Not all months have income for every house and same goes for expenses. I want to be able to print a report (hence creating a query for it) for each house so that I can have a record for each month with something in either the expenses column or the income column or both.But when i add these two queries and join them on both the house ID and the month, I can only either get a result which includes records which have identical month field or all expenses and those from income that match or vice versa. I tried aunion join of the two tables but of course then I will have two records for each month, one for income and one for expenses SO! where do I go from here?
Thanks for your continued support
Happy YN

Fornatian pointed you to a post where I explained how a full outer join can be implemented in Access. Did you read it? Did you understand it? Do you realize that that is what you need to do?

You also need to join on both Year and Month fields. Month won't be sufficient assuming that the data in your table spans multiple years.

Post your SQL for the three queries and someone will look at it.

[This message has been edited by Pat Hartman (edited 04-25-2002).]

Thanks Pat Yes I did read it & understand it. I think my problem is that the months are not unique because there are a few houses and each house can use that month. What I intend now to do is to use a criteria to limit each query to one house at a timebefore joining them on the month field e.g combo, then there will be a unique field in month.
By the way the month and year are in the same field. This was automatcally generated by access when I built the query and the wizard asked if I want to sum by month.quarter etc. BUT this actually seems to be giving me a problem as the field loooks like this "April 2002" and it seems to be recognizing it as a text field. That means it is ordering it alphabetically .Any idea how I can force it to order by date i.e. to convince access that it is a date field and Jan comes before april!?
Thanks

Add the following to your query, Order By it
Year([YourTable].[DateByMonth])*12+DatePart("m",[YourTable].[DateByMonth])-1

Thanks Rich
Actually that field had already been automatically created I just had to name it so I can order by it
The m was in single quotes not double. I wish I could master the secret of when to use double and when single! perhaps someone could enlighten me
Thanx again