One to Many Relationship

  • Thread starter Thread starter tmacdonald
  • Start date Start date
T

tmacdonald

Here's my issue, I have 2 tables: 1st one (Cash) has 3 fields: ID, Date,
Deposit. The second one (Debit) has 6 fields: ID, Deposit ID, Payor,
Description, Acct to Debit, Debit. I have a one to many relationship between
the two tables, with the primary key on (Cash) table being the ID field and a
foriegn key on the (Debit) table being Deposit ID. however when I run the
query the amount i specified for Deposit shows up for each record in the
(Debit) table.

What I want it to do is just show up once, because when I pull a report on
the data and sum the fields the Deposit amount is showing up for each of
those records instead of just once. As you can imagine this is throwing my
Deposit Total for the report way off.

Any assistance would be greatly welcomed.

Terry
 
You could base your main report on the 1st one only and then include a
subreport of the related table for the details. This will allow you to sum
the Deposit field.
 
I often create separate "totaler" queries to get various sums I need, then
include them in the report query and display them where I want them - just
scrapping the totaller function in the report generator.

A typical row of the composite query might then be:

id date deposit sumofdeposit payor debit ...

"sumofdeposit" comes from the separate query and is the number you display
in the totals row, rather than the access sum
 
Back
Top