One Charge, None/One/Many Payments

[Peg]

I have two tables. The first is service, with id, service_code,
program, service_date, payer, fee, etc... fields. The second table,
payment, contains payer, payment_date, payment_amount,
payment_adjustment, etc... fields for none, one or many payments for
each service in the service table. The id from the service table is a
field in the payment table. In the service table, the id is the
primary key; the payment table has a contatenated primary key. The
data source is an external data system; MS Access 97 is the version I
have to use at work.

I need to sum the fee and all payments and adjustments for every
selected service by program and payer (by Month/Year, eventually, for
a 3-year period). When I join the two tables, however, the
service.fee is duplicated for each payment. I need service.fee once
only.

I haven't worked with relational tables or MS Access in 10 years, and
have forgotten most of what I knew (and lots I didn't know!). It
seems the one service-to-none/one/many payments scenario would be a
common one, but I can't get my brain around how to get the results I
need. Prolly more than a simple INNER JOIN...

TIA,
Peg

 

[Jeff Boyce]

Peg

Open a new query in design mode. Select the two tables. Drag a link from
the "parent" ([service], right?) ID to the same field in the "child"
([payment]).

Select the join line and alter its property to reflect ALL of the parent,
and ANY of the child.

Add in the fields you want to see and run it!

 

[John Vinson]

I need to sum the fee and all payments and adjustments for every
selected service by program and payer (by Month/Year, eventually, for
a 3-year period). When I join the two tables, however, the
service.fee is duplicated for each payment. I need service.fee once
only.

I'd suggest that you create a Totals query on the Payments table to
sum the multiple payments; then join this query to the Service table.
This will give you a single fee value, and a single (aggregate)
payment value.

Alternatively, you can use the FIRST aggregate function on the fee,
and SUM on the payments.