K
katjelko via AccessMonster.com
There are two tables.
tblPoints
Autonumber
StudyID
EmployeeID
Points (Only positive number can be inputted)
tblPointRedemption
Autonumber
EmployeeID
Redemption Type
Redemption Date
Redemption Amount (Only negative number can be inputted)
I created one query named qryPatientPointsBalance and added the following
fields
from tblPoints
StudyID
EmployeeID
Points
from tblPointRedemption
Redemption Type
Redemption Date
Redemption Amount
I linked these two tables in the query by EmployeeID. The join type is
include all records from tblPoints and only those from tblPointRedemption
where EmployeeID is equal. The reason for this is that an employee cannot
redeem points until they are earned.
I then created a calculated field in the query next to Redemption Amount
field.
Patient Balance: [Points]+[Redemption Amount]
The problem is that the query is showing duplicate records each time I add a
redemption award. Let's say I awarded EmployeeID 583953 an amount of 50
points. Every time I add a redemption to decrease the 50 points, the query
duplicates the 50 points. So instead of only showing 50 points the patient
now shows two records with 50 points for a total of 100 points..
My ultimate goal is to create report that shows all the Studies the patient
participated in and all the awards that they redeemed. I am able to create
this report based on the query, but can't seem to avoid the duplicates.
Thanks for your assistance.
tblPoints
Autonumber
StudyID
EmployeeID
Points (Only positive number can be inputted)
tblPointRedemption
Autonumber
EmployeeID
Redemption Type
Redemption Date
Redemption Amount (Only negative number can be inputted)
I created one query named qryPatientPointsBalance and added the following
fields
from tblPoints
StudyID
EmployeeID
Points
from tblPointRedemption
Redemption Type
Redemption Date
Redemption Amount
I linked these two tables in the query by EmployeeID. The join type is
include all records from tblPoints and only those from tblPointRedemption
where EmployeeID is equal. The reason for this is that an employee cannot
redeem points until they are earned.
I then created a calculated field in the query next to Redemption Amount
field.
Patient Balance: [Points]+[Redemption Amount]
The problem is that the query is showing duplicate records each time I add a
redemption award. Let's say I awarded EmployeeID 583953 an amount of 50
points. Every time I add a redemption to decrease the 50 points, the query
duplicates the 50 points. So instead of only showing 50 points the patient
now shows two records with 50 points for a total of 100 points..
My ultimate goal is to create report that shows all the Studies the patient
participated in and all the awards that they redeemed. I am able to create
this report based on the query, but can't seem to avoid the duplicates.
Thanks for your assistance.