Query which takes data from two tables for report

[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

I made a query but my problem is that I only get data where there are available in both table. So in my example quarter 1 and 4 would show, but 2 and 3 would be left out. I tried a left and right join but in that case either

I want all four of them to be visible! Even when
for one of the quarters no data is available in one table.

Can anyone tell me what to do? I have no clue and I have been at it for weeks now

Sheila

 

[lespaul]

If you post your existing query (since you've almost got it!) maybe we can get it working. Sounds like you need a left or right join instead of a inner join in order to get "missing data" to be included.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SheilaAlighieri]

Here is the query I am using now Hope it is clear

SELECT DISTINCTROW DataEntry.[Account Code], Budget.[Budget Year], DataEntry.[Amount in Euro], Budget.[Budget]
FROM (Account INNER JOIN Budget ON Account.[Account Code] = Budget.[Account Code]) INNER JOIN DataEntry ON Account.[Account Code] = DataEntry.[Account Code]
GROUP BY DataEntry.[Account Code], Budget.[Budget Year], DataEntry.[Amount in Euro], Budget.[Budget];

 

[lespaul]

I think if you change it to:

SELECT DISTINCTROW DataEntry.[Account Code], Budget.[Budget Year], DataEntry.[Amount in Euro], Budget.[Budget]
FROM (Account RIGHT JOIN Budget ON Account.[Account Code] = Budget.[Account Code]) RIGHT JOIN DataEntry ON Account.[Account Code] = DataEntry.[Account Code]
GROUP BY DataEntry.[Account Code], Budget.[Budget Year], DataEntry.[Amount in Euro], Budget.[Budget];

it should work. It may need to be RIGHT OUTER.

HTH



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SheilaAlighieri]

Hi Leslie!

I am think I am very near. 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?

Thanks

SHeila

 

[DataNick]

Sheila,
What perseverence to be at this problem for weeks! I'm probably not as smart as the other guys, but I don't mind trying. If it's OK, send me a screenshot of your ERD (Access relationships window) and from what you've posted already, I'll work on it. If I have more questions, I'll post them. Good luck,
Nick

javanick@tfb.com

 

[SheilaAlighieri]

Nick,

Is it ok, when I send you the database with only the tables and the query I am working on?

Thanks for your help!

Sheila

 

[DataNick]

Sheila,
Sure that's OK. I got the zip file. I'll work on it tonight and post later,
Nick

 

[SheilaAlighieri]

Hi everybody,

Unfortunately Nick has been very busy the past week en het did not had the time to look into my problem. So if anyone else can offer me a helping hand, I would greatly appreciate it You can see all the details above. If you want me to send you the database, just post your e-mailaddress here or send a message to Sheila_Alighieri@hotmail.com

Thanks !

Sheila