Last try - Report using data from two tables 1

[SheilaAlighieri]

Hi! Please take the time to read about my problem. I have been at it for weeks and if I can't find a solution this time, I guess I have to give up my project But here goes:

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: Spendings:
Sheila - 1981/1982 - 3 - 1000 - 0

But when I enter an Left OUter Join I get:

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

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

Any clue what I could be doing wrong? Is there 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. Any help is really appreciated.

Greetings from Italy

Sheila

 

[dhookom]

I thought you would have needed to include the AccountName field also?

I solved a problem like this by creating a union query to get all possible combinations.


SELECT DISTINCT [Account Code], [Budget Year], Quarter
FROM Budget
UNION
SELECT DISTINCT [Account Code], [Budget Year], Quarter
FROM DataEntry;

Then use this union query with your tables and select all records from the union.



Duane
MS Access MVP

 

[vise]

hey,
I dont want to start another thread, i've started one in ms access tables and relationships. i want help with creating a monthly crosstab query.
Thread700-621207 for your help,
`vise

 

[SheilaAlighieri]

Hi Dhookom!

Thanks a lot for your reply It got me a bit closer to my goal. I still have one small problem though. I now use the following query:

SELECT [Account Union Query].[Account Code], [Account Union Query].[Budget Year], [Account Union Query].Quarter, Sum(Nz([Budget],0)) AS [Budget Sum], Sum(Nz([Amount in Euro],0)) AS [Actual Sum]
FROM Budget, [Account Union Query], DataEntry
GROUP BY [Account Union Query].[Account Code], [Account Union Query].[Budget Year], [Account Union Query].Quarter;

And I have entered the following test data:

Invoice:

Account Code Budget Year Quarter Sum
105355 1999/2000 2 100

Budget:

Account Code Budget Year Quarter Budget
105355 1999/2000 4 5000


And I now get:

Account Code Budget Year Quarter Budget Sum
105355 1999/2000 2 5000 100
105355 1999/2000 4 5000 100

Where I should get:

Account Code Budget Year Quarter Budget Sum
105355 1999/2000 2 0 100
105355 1999/2000 4 5000 0

Do you have any clue what I am doing wrong? It is probably somewhere in my relationships, right?

Thanks Again,

SheilA

 

[dhookom]

I don't see any joins in your sql. You need to join the [Account Code], [Budget Year], and Quarter fields.

Duane
MS Access MVP

 

[SheilaAlighieri]

Hi everybody!

I just wanted to let everybody know that I got my problem sorted out! Thanks a bunch to everyone who lend me a helping hand

Sheila

 

[SheilaAlighieri]

Hi Duane. I thought my database was finished but when I had a closer look I realized my sum totals were incorrect. I am not sure what is happening but dependent on the number of records available in a table the sum gets multiplied. For example.. when the invoice conatins three records corresponding to a certain budget field.. this budget amount gets multiplied by three It is likewise the other way around (but this is ofcourse less likely to happen), when there are three budgets for one record in the invoice table, the amount gets multiplied by three. Here is my query, maybe you can look into it. Thanks.

SELECT [Account Union Quarter Query].[Account Code], [Account Union Quarter Query].[Budget Year], [Account Union Quarter Query].Quarter, Sum(Nz([Budget],0)) AS [Budget Sum], Sum(Nz([Planned],0)) AS Plan, Sum(Nz([Amount in Euro],0)) AS [Actual Sum], Sum(Nz([CommittedBudget],0)) AS [Committed by Budget], Sum(Nz([Committed in Euro],0)) AS [Committed by Invoice], [Committed by Budget]-[Committed by Invoice] AS [Rest Committed], [Budget Sum]-Abs([Rest Committed])-Abs([Actual Sum]) AS [Rest Budget]
FROM ([Account Union Quarter Query] LEFT JOIN Budget ON ([Account Union Quarter Query].[Account Code] = Budget.[Account Code]) AND ([Account Union Quarter Query].[Budget Year] = Budget.[Budget Year]) AND ([Account Union Quarter Query].Quarter = Budget.Quarter)) LEFT JOIN DataEntry ON ([Account Union Quarter Query].[Budget Year] = DataEntry.[Budget Year]) AND ([Account Union Quarter Query].Quarter = DataEntry.Quarter) AND ([Account Union Quarter Query].[Account Code] = DataEntry.[Account Code])
GROUP BY [Account Union Quarter Query].[Account Code], [Account Union Quarter Query].[Budget Year], [Account Union Quarter Query].Quarter;

Sheila!

 

[dhookom]

I have no idea about your data. You may need to Group records in one of your tables prior to adding it to this query so that you create one total record where there are three. This might mean totaling your invoice table ([DataEntry]) in a totals query by Year, Quarter, and Account Code and then substituting this new query in your query above.

Duane
MS Access MVP