Report query - two table combining problem

[SheilaAlighieri]

Hi!

I would like to make a report which shows and combines data from two tables, i.e. 'Budget' and 'Invoice'.

I am using 5 fields, namely:

1. Budget Year 'from Budget table (the field is also available in the Invoice table);
2. Quarter 'from Budget Table (also entered in Invoice table);
3. Account Name 'from Budget Table (also entered in Invoice Table);
4. Budget 'from Budget Table;
5. Actual spendings 'from Invoice Table.

The budget year shows as 1980/1981, 1981/1982 and so on. The budget year starts in april. Therefore quarter two of the calendar year is quarter one of my budget year. Those switch codes are working perfectly. I classified the 'budget year' and 'quarter' field as text fields.

Ok, now I would like to make a report, which shows the budget and the spendings by budget year divided by quarter. For example:

Account Name: Sheila

2001/2002

quarter Budget Spendings
1. 100 95
2. 100 -
3. - 10
4. 100 200

However, when I enter the following code:

SELECT Budget.[Account Code], Budget.[Budget Year], Budget.Quarter, Nz([Budget],0) AS Expr1, Nz([Amount in Euro],0) AS Expr2
FROM DataEntry RIGHT OUTER JOIN Budget ON (DataEntry.Quarter = Budget.Quarter) AND (DataEntry.[Budget Year] = Budget.[Budget Year]);


The query generates these results:

Account Name: Budget Year: Quarter: BUDGET: AcCCOUNT:
Sheila - 1981/1982 - 3 - 1000 - 0

But when I enter an Left OUter Join I get:

Account Name: Budget Year: Quarter: BUDGET: AcCCOUNT:
- - - 0 - 100

But I would like to get:
Account Name: Budget Year: Quarter: BUDGET: AcCCOUNT:
Sheila - 1981/1982 - 3 - 1000 - 0
Sheila - 1980/1981 - 3 - 0 - 1000

Any clue what I could be doing wrong? Is the perhaps something wrong with my relationships?
If you want me to send you the database, just post your e-mailaddress here or send a message to Sheila_Alighieri@hotmail.com. I have no clue and I have been at it for weeks now Any help is really appreciated.

Sheila

 

[tracy1234]

Hey, this one is a little confusing. From what I gather, you have two tables with the following columns:

Budget
Budget Year
Quarter
Acct Name
Budget

Invoice
Spending
Budget Year
Quarter
Acct Name

I would think you'd want something like:

select Budget.Budget Year, Budget.Quarter, Budget.Acct Name, Budget.Budget, Invoice.Spending
from Budget, Invoice
where Budget.Acct Name = Invoice.Acct Name (+)

But your code looks like you're trying to join the two tables on both quarter and year. This to me is puzzling. Maybe I've just missed the boat completely on this one.