Complicated Query (Many to Many Merge)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables, Bills, BillAdjustments, BillPeriods, There is a many to many
relationship between Bills and BillAdjustments.
The Bills have a consolidated number for Adjustments by month by lease,
however, sometimes there might be 2 bills with 2 adjustments for a lease in
one month. There can be multiple adjustments in the BillAdjustments Table
for each month for any lease.

I want to see all bills and adjustments for each lease by month. ANY
SUGGESTIONS?
 
What are the fields in Bills, BillAdjustments and BillPeriods? Do you want
just a consolidated figure for each lease, or do you want to see each one
individually?
 
David,
Thanx for responding.
There are 590 adjustment records (lease #, bill period, adj period,
adjustment-amount, description)and 643 bills (lease #, bill period, rent,
fees, adjustment-amount, total bill).
There can be multiple bills in a month for the same lease, and there can be
multiple adjustments for the same lease in a month. All adjustments per
month should equal, however, it is not clear how many records from each table
comprise the total.(ie 2 bills during MONTHX with 2 separate adjustment
amounts = 5 adjustments during MONTHX from the adjustments table).
I want to print out the information by lease so that if there are 5
adjustments and 2 bills for the Month there would be 7 lines totaled per
lease.
 
I want all the information displayed once,
David,
Thanx for responding.
There are 590 adjustment records (lease #, bill period, adj period,
adjustment-amount, description)and 643 bills (lease #, bill period, rent,
fees, adjustment-amount, total bill).
There can be multiple bills in a month for the same lease, and there can be
multiple adjustments for the same lease in a month. All adjustments per
month should equal, however, it is not clear how many records from each table
comprise the total.(ie 2 bills during MONTHX with 2 separate adjustment
amounts = 5 adjustments during MONTHX from the adjustments table).
I want to print out the information by lease so that if there are 5
adjustments and 2 bills for the Month there would be 7 lines totaled per
lease.
Click to expand...
 
OK, thatnks for posting those table structures - makes things a lot easier.

You've got two approaches that you can take to this, one involving a UNION
query and another that doesn't - it may come down to an issue of perofrmance,
but I'm not sure.

The simplest way is using a UNION to build a query that has everything in it,
which you can then sum - let's call it BillingLines:
SELECT [lease #], [bill period], [total bill] as [line total] from Bills
UNION
SELECT [lease #], [bill period], [adjustment-amount] from BillAdjustments

The summary query is easy then:
SELECT BillingLines.[lease #], BillingLines.[bill period], Sum(BillingLines.
[line total]) AS [SumOfline total]
FROM BillingLines
GROUP BY BillingLines.[lease #], BillingLines.[bill period];

The other approach is to build summary queries for each table, and then join
them together using BillingPeriod; it would help a lot if you had a lease #
master table in the same way that you a BillPeriods table, otherwise you're
going to need a quite a number of quieries and possibly a UNION query anyway..
..
 
David said:
OK, thatnks for posting those table structures - makes things a lot easier.

You've got two approaches that you can take to this, one involving a UNION
query and another that doesn't - it may come down to an issue of perofrmance,
but I'm not sure.

The simplest way is using a UNION to build a query that has everything in it,
which you can then sum - let's call it BillingLines:
SELECT [lease #], [bill period], [total bill] as [line total] from Bills
UNION
SELECT [lease #], [bill period], [adjustment-amount] from BillAdjustments

The summary query is easy then:
SELECT BillingLines.[lease #], BillingLines.[bill period], Sum(BillingLines.
[line total]) AS [SumOfline total]
FROM BillingLines
GROUP BY BillingLines.[lease #], BillingLines.[bill period];

The other approach is to build summary queries for each table, and then join
them together using BillingPeriod; it would help a lot if you had a lease #
master table in the same way that you a BillPeriods table, otherwise you're
going to need a quite a number of quieries and possibly a UNION query anyway..
.
Click to expand...
Thanks so much David,

I will try that this morning and let you know if it works. Have a great day~!
 
Back
Top