Indentical Records that need differentiation

[Danglez]

I built a database that keeps track of bank deals transacted by a bank. I am new to access so when I created the database I set no primary key, in order to keep the db flexible as I was building/tweaking it.

The current problem is that on occasion we have two deals on the same day for the same amount, dates, and interest rate with the same bank. When the second record is entered, it is not factored into the calculations that i set up. A penny must be added to the amount to make sure that access counts it as a different transaction.

Any ideas on what can be done to get rid of the problem???

Thank you all

 

[PHV]

Why not simply add an autonumber field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Danglez]

I just tried that. I simply added a transactionid field and it was autonumbered. The records had diff ids but the calculations still did not take into account the second amount.

 

[Danglez]

I tried setting the field as primary key as well, no difference. While playing around with it I discovered that Regardless of the interest rate, if the counterparty and amount are identical, the resulting calculation will not count the second record's amount.

 

[Golom]

The issue sounds like it's with the "calculation method" and not with the table itself. To that end, can you post the VB and/or SQL you are using to do the calculation?

 

[Danglez]

Forgive my choppy work, but im a lowly intern that tought myself enough access to cobble this db together.

Transactions (Main Table)
Fields: Ticket Number/Transaction Date/Counterparty/Type/Amount/Branch/Interest Rate/Value Date/Maturity Date

This was then created to manipulate the entered data (amt *interest * # of days)
CashFlow Calculations (Calculates deal length and cashflow)
SELECT Transactions.[Ticket Number], Transactions.[Transaction Date], Transactions.Counterparty, Transactions.Type, Transactions.Amount, Transactions.Branch, Transactions.[Interest Rate], Transactions.[Value Date], Transactions.[Maturity Date], Transactions.[Maturity Date]-[Value Date] AS Days, ((([Amount]*([Interest Rate]/100))/360)*[Days])*-1 AS Interest, ([Amount]*-1)+[Interest] AS Cashflow
FROM Transactions;

Deals Valued Today (Displays all deals made today that are valued Today)
SELECT Transactions_1.[Transaction Date], Transactions_1.Counterparty, Transactions_1.Amount, Transactions_1.[Value Date], Transactions_1.Branch
FROM Transactions AS Transactions_1
GROUP BY Transactions_1.[Transaction Date], Transactions_1.Counterparty, Transactions_1.Amount, Transactions_1.[Value Date], Transactions_1.Branch
HAVING (((Transactions_1.[Transaction Date])=Date()) AND ((Transactions_1.[Value Date])=Date()));

These two were created to sum the amounts by branch (we have three)

Branch Sums Maturity (Sums of deals maturing today by branch)
SELECT [CashFlow Calculations].Branch, [CashFlow Calculations].[Maturity Date], Sum([CashFlow Calculations].Cashflow) AS SumOfCashflow
FROM [CashFlow Calculations]
GROUP BY [CashFlow Calculations].Branch, [CashFlow Calculations].[Maturity Date]
HAVING ((([CashFlow Calculations].[Maturity Date])=Date()));

Branch Sum Value (Sum of deals value today by branch)
SELECT [CashFlow Calculations].Branch, [CashFlow Calculations].[Value Date], Sum([CashFlow Calculations].Amount) AS SumOfAmount
FROM [CashFlow Calculations]
WHERE ((([CashFlow Calculations].[Transaction Date])<>Date()))
GROUP BY [CashFlow Calculations].Branch, [CashFlow Calculations].[Value Date]
HAVING ((([CashFlow Calculations].[Value Date])=Date()));

Based on the two “Branch Sum Queries” I made

Opening Maturity (Sum of ‘Branch Sums Maturity’)
SELECT Sum([Branch Sums Maturity].SumOfCashflow) AS SumOfSumOfCashflow
FROM [Branch Sums Maturity];

Opening Value (Sum of ‘Branch Sums Value’)
SELECT Sum([Branch Sums Value].SumOfAmount) AS SumOfSumOfAmount
FROM [Branch Sums Value];

After those two sums I created

Current Position (Calculates Current Position w/o fx & opening)
SELECT nz(nz([Opening Maturity]!SumOfSumOfCashflow)+nz([Opening Value]!SumOfSumOfAmount)+nz(SumToday!SumOfAmount)) AS [Current Position]
FROM [Opening Maturity], SumToday, [Opening Value];

Then on my form I have the box linked to current position with:
=nz(nz(DLookUp(" [Current Position]![Current Position] ","Current Position"))+nz([Opening])+nz([Forex])+nz([Loan])+nz([misc])) The last three boxes are inputed on my form in the morning.

I hope this helps.

Danglez

 

[Danglez]

Any ideas on what the problem might be in my calculations?