If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One to Many Relationship
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 |
#2
|
|||
|
|||
One to Many Relationship
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. -- Duane Hookom Microsoft Access MVP "tmacdonald" wrote: 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 |
#3
|
|||
|
|||
One to Many Relationship
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 "tmacdonald" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|