Combining 2 queries Access 2010

[JayDee]

I need some help with forming a query
I have 2 queris: Accounting an Budget, and would like to make a query that contain information from both query's


Both queries have the following fields:


CustNo
Account
Year
Period
Movement
ClosingBalance


The fields I would like to have in the final query is: (A=Accounting/B=Budget)
CustNo
Account
Year
Period
A.Movement
A.ClosingBalanse
B.Movement
B.ClosingBalanse


For records in the Accounting Query that does not have a matching record in the Budget Query, the fields B.Movement and B.ClosingBalanse should be empty or zero in the final query


For records in the Budget Query that does not have a matching record in the Accounting Query, the fields A.Movement and A.ClosingBalanse should be empty or zero in the final query


Where there are matcing records in both query's all fields should have values in the final query


I have solved this problem by using VBA and ADO, but on big datasett this takes a very long time, so I was hoping that with querys, it will go faster






Any advice on how to proceed?

[lfpm062010]

This should work.

Use one query to get all the unique record. I don't have information about what make unique in your table Account and Budget.

Then use the query to LEFT JOIN back to the table Account and Budget to get your final table.

[JayDee]

Thank you for your quick answer, but I am not sure I understand what you mean buy unique records.
Could you explain a litle bit more in detail what you are thinking about.
Do you mean records in Accounting that does not exist in Budget and records in Budget that does not exist in Accounting?

[lfpm062010]

Unique records most of the time means your Primary Key. It is what makes this record different from every other record.
It can be made up with one or more fields.

[JayDee]

I am still not understanding what you are trying to explain to me.
Can you give me an example on how to construct the query that pull out the unique records. The field names are in my first post.

[lfpm062010]

The first post still missing a few thing. They are query fields.

What are the table Names? Are they called "Accounting" and "Budget" and more....? What is the primary key for those tables? Is the primary key "CustNo" or "Account" or "Year" or "Period" or any combination?

If you can post the database, maybe it will be easier (remove any confidential information before posting the database).

[JayDee]

I have made a small testbase to illustrate my problem. In the testbase I have made 2 tables. Accounting and Budget. In my real project this is queries. I have also made a table called Result. This is how I would like the final query to look like.

[lfpm062010]

Hope this is what you looking for.
Testbase.accdb

[JayDee]

Thank you very much. Thsi did the trick.