one to many creates duplicate records in query

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

Guest

I have 2 tables. Debits on one side with a primary key. Credits on the other
side which can have multiple receipts for payments. The primary key is
related to the many side in the relationship. When I create a select query
the debits appear as many times as there are receipts on the other side of
the relationship.

I am trying to create a query that selects the debit once (the one side) and
all the receipts (from the many side). From this I will run a client
statement with progressive balances based on the payments made.

Help!!!!!
 
I have 2 tables. Debits on one side with a primary key. Credits on the other
side which can have multiple receipts for payments. The primary key is
related to the many side in the relationship. When I create a select query
the debits appear as many times as there are receipts on the other side of
the relationship.
Click to expand...

Exactly; that's precisely how relational queries are designed to work.
Each record in the "one" side table is combined with every matching
record in the "many" side table, and you see all of the possible
combinations.
I am trying to create a query that selects the debit once (the one side) and
all the receipts (from the many side). From this I will run a client
statement with progressive balances based on the payments made.
Click to expand...

You can't do that in a simple Query. I'd suggest instead using a
Report based on the debits table, with a Subreport based on the
credits table.


John W. Vinson[MVP]
 
Thank you I will give that a try

John Vinson said:
Exactly; that's precisely how relational queries are designed to work.
Each record in the "one" side table is combined with every matching
record in the "many" side table, and you see all of the possible
combinations.


You can't do that in a simple Query. I'd suggest instead using a
Report based on the debits table, with a Subreport based on the
credits table.


John W. Vinson[MVP]
Click to expand...
 
New TO Access said:
I have 2 tables. Debits on one side with a primary key. Credits on the other
side which can have multiple receipts for payments. The primary key is
related to the many side in the relationship. When I create a select query
the debits appear as many times as there are receipts on the other side of
the relationship.

I am trying to create a query that selects the debit once (the one side) and
all the receipts (from the many side). From this I will run a client
statement with progressive balances based on the payments made.

Help!!!!!
Click to expand...

You can't do that with a query, the results of a query are simply a
two-dimensional table where every row has the same columns as every other
row.

What you need to do is to take the query you have already created, and base
a report on it to produce your statements. Within your report, you can
create a grouping level to give a header for each debit, with the credits
grouped underneath it
 
I would import the data into Excel and run Data =>subtotals and then Advanced
filter(check unique values box). Do Go to Special "Values Only" and cut and
paste the results in another worksheet.
 
Back
Top