query to produce table where ID may be in one table but not the other

[kwooten]

I am trying to create a table based on a projects actual expense and budget. The budget for each project is given a 4 digit code and the report we use for actual expense also tells us the same 4 digit code. So getting a query to produce a table for actual spend vs budget is easy based on where the 4 digit codes are equal; however I also need the query to produce results for where there is budget but no actual expense as well as actual expense but no budget. How do I get a query to output all the information into one table?

[June7]

Why would a project not have a budget?

You need a dataset of all possible projects.

Do aggregate query for the expenses.

Join the query and the budget table to the projects dataset.

[kwooten]

project could have closed early so we could run a deficit until the budgeted close date.

I have thought about that but it doesn't solve the problem of having budget but 0 expense.. Projects get delayed so we could run surplus budget for a couple months. I seem to be able to make it where I can show budget for all the expense that has posted or all of the budget even if there is no expense but nothing that shows both.

[June7]

All projects will show if you use an ALL PROJECTS dataset. Then join the other datasets to that one but don't use INNER JOIN.

[kwooten]

that is part of the problem.. I don't have an "All Projects" dataset.. I have to use the actual expense file (which could/will change each month as new project start taking expense) and the budget file. Is there a way to create an "All Projects" dataset based on these two tables?

[kwooten]

think I got it to work.. it isn't exactly pretty but basically I took the actual expense file and created a table of the project ID's then took the budget file and appended the project ID's (excluding duplicates) from that table into the table created from the actual expense.. this gave me a list of all the ID's then I used the actual tables and joined them to my new "All projects" list to get the other information I needed... Is this what you would have done?

[June7]

Only if you intend to maintain that table. It does make sense to have Projects table with general info about project.

However, could have done a UNION query to generate the dataset of all project numbers.

[kwooten]

I have never done a UNION query before.. if that would eliminate the need to maintain the table then that would be preferable.. How do those work?

[June7]

Search web. Here is a start http://www.w3schools.com/SQl/sql_union.asp

[kwooten]

yeah the UNION is much easier..