[adamng7]

Hi,
I have created the following tables:

Progress: Table
- Progress_Day
- Progress_Date

Expenditure Details: Table
- Code
- Expense Description

Planned Cost: Table:
- Progress_Day
- Code
- Total_Amount

Actual Cost: Table
- Progress_Day
- Code
- Total_Amount

The progress table and expenditure details table are linked to both planned and actual cost tables in a one-to-many relationship. In the progress table, it has records from 1 to 143 days.

In planned cost table, record is only entered up til 132 days, but in acutal cost, record is entered up til 143 days.

To generate an analysis between actual and planned cost, I created a query with the following fields
- Progress.Progress_Day
- Expenditure Details.Expense_Description
- Actual Cost.Total_Amount
- Planned Cost.Total_Amount
- Expr1: [Planned Cost.Total_Amount]-[Actual Cost.Total_Amount]

When I generate the query, I get only results up til 132 days. I then tried to replace Progress.Progress_Day witih Actual Cost.Progress_Day, but I got my data got messed up, and the Planned Cost.Total_Amount field returned with a single number for all records, in my case $1000.

Is there anyway I can generate all 143 days of data, and where planned cost do not contain the data, it will just return a zero value.

Thanks.

[HiTechCoach]

First you will need to chande the join type between Progress and [Planned Cost] to be the typoe that included all record from Progress and only the maqtch from [Planned Cost]

Next change your expression to be:
Nz([Planned Cost.Total_Amount],0)-[Actual Cost.Total_Amount]

[adamng7]

Hi HiTechCoach,
Thanks for the reply. What do you mean by the first part where I have to change the join type? I don't exactly get it.

[OBP]

What HiTechCoach means is that the join line between the tables is adjustable, if you right click on the line you get 3 choices All the records from both tables where the records are equal, all the records from the left table and only those matching from the right table and the last version is the opposite of that.
The default is the first one and you have found what happens when you use that with uneven amounts of records in the 2 tables.
So you need to choose the Table with the most records as the table that displays the "All" records.
However there is a "catch" to doing so, when you have more than 2 tables if you don't also use the same type of "join" on the other side of the table you will get an "Uneven Inner or Outer Join" error.
So you may have to work from left to right or right to left using the same type of join throughout, or split the query in to more than one query where you can make the joins that you need.

[adamng7]

Hi OBP, HiTechCoach,
I did change the join properties, but I think you got it there in the tricky part. When I try to run the query, there is an error saying "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement."

How do I get past this?

[adamng7]

Hi guys,
In addition to the issues I posted earlier, it seem some of the results of the query is wrong. Let me elaborate. In addition to the query I created described in the beginning of this thread, I also created 2 seperate queries to show the planned and actual cost seperately.

Query: Planned Cost
- Progress.Progress Day
- Expenditure Details.Expense_Description
- Planned Cost.Total_Amount

Query: Actual Cost
- Progress.Progress Day
- Expenditure Details.Expense_Description
- Actual Cost.Total_Amount

Take for example a particular expense like barge cost. In the individual query, for a selection of day 1 to day 10, the actual cost is $20,000, while planned cost is $21,000 (which I checked is correct). However, in the combined query which shows the calculation of actual - planned cost, the actual barge cost is showing as $120,000, while the planned costs is showing $126,000. Also, in some cases of the cost like rental, both planned and actual cost shows $300,000, but yet a difference of $13,000 arise. In other cases, the calculation are fine.

I wonder if there is anything I did wrongly.

[OBP]

Can you create a copy of your database, delete any forms in the copy and then save it as Access 2003 format so that I can take a look at the tables & Queries?

[adamng7]

Hi,
I have uploaded my file. It's in .rar format as zipping it did not compress the file to the maximum allowable size.

THanks!

[OBP]

Have a look at this test query design, which you can use as a "Pre-Query" to your main query, I think it does basically what you want.
Add this to your Query3 to replace the 2 tables.
I have to go out otherwise I would provide more help.

[adamng7]

Hi OBP,
I tried the method but it doesn't work. It is only showing the data from the actual table. One thing to note is that the transaction ID in the planned cost table and the transaction ID in the actual cost table is different.

Would you have other suggestion?

Thanks a lot.

[OBP]

Try reversing the Join like the attachment.

[adamng7]

Hi OBP,
Based on the reverse, I got the opposite result, i.e. only planned cost. However, I think we made some good progress. What I did further was to create two seperate query based on both your advice. From each of the query, I was able to obtain the planned and actual cost. Then I created a third query where I show the result of the two earlier query.

The result was good as I was able to obtain an accurate of the total planned and actual cost.

However, what I am struggling further is how do I input other fields like description or progress_days. I tried to input via the progress table, but it kind of messed up the data again.

I attach a new file for your reference.

[adamng7]

Hi OBP,
I think I solved it. For Query 1, what I did was to link Progress table to Actual Cost table, and include all records from Progress. Then I linked Actual Cost table to Planned Cost table as described above. The same was done for query 2.

In Query 3, I linked the progress table to both Query 1 and Quey 2, and problem solved. Not sure what's the reasoning behind it though.

Now that this is done, wonder if you could help me in the formattable report thread?

[OBP]

adam, unfortunately I don't have Access 2007 which has a different Report User Interface to Access 2000-2003. But I did respond on the thread showing where you can find the Field list to add the necessary fields form the Query to the Report.