Creating an expression with two tables

[dssrun]

I have the scenario below to capture the final charge and payments due where facility and report dates are primary keys in both tables:

Table Productivity: Facility, Report Date, Charges, Payments

Table Adjustments: Facility, Report Date, Charges, Payments

Query Final: Facility, Report Date, (Productivity.Charge+Adjustments.Charges), (Productivity.Payments+Adjustments.Adjustments)

In the table adjustments there is not always a facility and run date associated with a pair in the productivity table. So therefore, my query only returns values where there are pairs in both tables through a LEFT JOIN and a one to one relationship. (e.g. Facility A 10/1/2010 has to be in both tables for my query to work or else a blank value will be in the query, but there is not always adjustments every report date). I am new to access so maybe I am missing something obvious. The SQL looks like this for just the charge portion:

SELECT Productivity.Facility, Productivity.[Report Date], [Productivity].[Charge]+[Adjustments].[Charge] AS Charge
FROM Productivity LEFT JOIN [Adjustments] ON (Productivity.[Report Date] = [Adjustments].[Report Date]) AND (Productivity.Facility = [Adjustments].Facility);

[jzwp11]

What does the report date represent? Is it just the date of the charge/payment or something else?

If a facility can have many charges/payments then that describes a one-to-many relationship.

First a table to hold the facility info

tblFacilities
-pkFacilityID primary key, autonumber
-txtFacilityName

The a table to hold the charge/payments of each facility

tblFacilityTransactions
-pkFacilityTransID primary key, autonumber
-fkFacilityID foreign key to tblFacilities
-dteTrans (transaction date)
-currTransAmt (+ for payments, - for charges)

You can enter in any adjustments in the tblFacilityTransactions. You may want to add an additional field to identify those transactions that are adjustments versus other transactions. In terms of your query, you would just add up the transaction amounts by facility.

[dssrun]

The report date represents when the report is run, e.g. the Productivity table is updated automatically in November with October's numbers. The problem with making one table a transaction table is the Productivity table is updated automatically by another programing importing the information so that is why I separated the two tables (and this program separates charges and payments in two fields)

The adjustments are manually inputed. If i touch the structure of the productivity table, the data will not be imported.

Unless I create a union all query on system charge and system payment and then create another query as a transaction table?

[jzwp11]

I would probably keep your original tables as a placeholder of the imported data and then migrate the data monthly into the table structure I suggested. That way you would be working with a normalized structure which will make your life easier in the long run when doing queries, forms and reports.

However, if you want to continue to use your current structure, then I would have 1 query that does an inner join to take care of records where the report date and facility match. Assuming that when an adjustment is made, it is ALWAYS tied to a facility but may not have a date, then I would create a separate query for adjustments where report date is Null.

Then I would create a UNION query between the two above queries. Then from there you can create an aggregate query to get your sums.