Query that links two tables for a report

[SheilaAlighieri]

Hi guys!

I have been struggeling with a Access problem for days but I reckon it is very easy to solve for one of you professionals So here goes:

I am trying to link two tables (named "Budget" respectively "Invoice&quot to acquire certain information for a report. I joined the tables on the basis of three fields (namely "Budget Year", "Quarter" and "Account Name&quot. As for my problem. When the data in the two tables correspond for a specific year, quarter and account all data is shown and nothing is wrong. But when the budget table contains data for a certain budget year, quarter and account name which aren't represented in the invoice table, they are left out! I wish for all data in both tables to be seen, but I can't get it to work. To clarify matters somewhat I will attach my sql code below.

SELECT [Invoice].[Budget Year], [Invoice].Quarter, [Invoice].[Account Name], [Invoice].[Amount in Euro], [Budget].Budget
FROM [Invoice] LEFT JOIN [Budget] ON ([Invoice].[Account Name] = [Budget].[Account Name]) AND ([Invoice].[Budget Year] = [Budget].[Budget Year]) AND ([Invoice].Quarter = [Budget].Quarter)
GROUP BY [Invoice].[Budget Year], [Invoice].Quarter, [Invoice].[Account Name], [Invoice].[Amount in Euro], [Budget].Budget;

I tried to change the join to an OUTER JOIN but in that case the program gives me an error I hope I've been clear enough, cause I am terrible at explaining these sort of things. Any help would be greatly appreciated.

Thanks!

Sheila

 

[Rolliee]

Sheila,

Let the wiz build you a query. By showing both tables and the keys (primary and foreign) that like them you can get what you wish in the query. See that the recs you wish are there. Then shift to design and to view/sql \. Copy the SQl statement to the clipboard and then past it in the report.

If you cannot do that, send me a bare bones version - sans provate stuff- by ZIP and I will set it up for you.
rollie@bwsys.net