Avoiding Duplicate data

[dsm521]

I am pulling data from two Access tables. One contains budget data, broken down by project number and again by line item number. My difficulties begin as additional data is entered as the budget data by line number may be increased or decreased during the course of a year.

The second table also lists line item numbers and associated project number to track purchasing data against each budget line and project. Descriptions and amounts vary with each purchase in this table.

I need to create a report pulling data from both tables where the Project Number is a group with the associated line item number activity (both budget and purchasing)within that group. For example, with each line #1, there is an associated budget description and amount plus associated purchasing activity and expense. The problem is that when line #1 has two budget entries (an adjustment was made), my report wants to list the purchasing activity under each budget entry, duplicating my purchasing amounts.

I've tried incorporating a master project number table in an effort to create the "one to many" relationship with the budget and purchasing tables, however, no matter what I try, I keep getting duplication.

 

[synapsevampire]

It seems that the problem stems from NOT having a unique budget line item, rather a budget group.

I think I get it.

You have something like:

Project Budget Line Item/$ purchasing Line Item/$
1 1/$100 1/$300
1 1/$200 1/$300

where the second row is really just an adjustment to the first line item, but because of the join, you get $600 erroneously displayed in Purchasing, correct?

The simplest means is to create an Access query to build the data first using a Query (subqueries summing the values for each line item) since the table design is poorly conceived.

Within CR you are probably forced into using a subquery, with the main report having one total, and the subreport summing the other.

But then I'm kinda worn out, there may be a simpler means.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com