Calculated fields in a Query based on fields in Table 1

[MSealy]

Hi, this is a bit of a biggie, but I was wondering if anyone could help please.

I'm creating a database for loans of season tickets and I'm having problems with calculated fields in a query. The Database is based on a spreadsheet, which goes like this:

Name AmtofLoan PeriodLoanPaid NoOfRepayments FirstRepay OtherPaym DateofFirstPay LoanNo
Bloggs, Joe £1000 12/07 12 83.37 83.33 30/04/08 200801
Smith, John £500 01/08 12 41.74 41.66 31/05/08 200802
Jones, Peter £700 02/08 9 77.84 77.77 30/06/08 200803
Doe, John £1500 02/08 10 150.00 150.00 31/05/08 200804

Period is for financial year i.e. 12/07 would be March 2007. The Loan number is the year plus the ID.

The First Repayment and Other Payment fields are caluclated based on the No. of repayments. The formula of which is...

First Repayment: =AmtofLoan-((NoOfRepayments-1)*OtherPaym
OtherPaym: =ROUNDDOWN(AmtofLoan/NoOfRepayments,2)

This works fine for a spreadsheet, but recreating it in Access produces problems. First of all, I created my table with the following:-

Name (Text)
LoanAmt (Currency)
PeriodPaid (Text)
Repayments (Number)
DateFirstRep (Date/Time)
LoanYearNo (Number) - also primary key.

Then I entered some dummy data as above, then created my Query.

My query is as follows. It contains all the fields from the table, plus:
Field: [FirstRepayment]
Criteria: [LoanAmt]-([Repayments]-1)*[OtherPayments]

Field: [OtherPayments]
Criteria: Round([LoanAmt]/[Repayments],2)

Now this should work, but instead of showing me the results, it prompts me for these boxes and of course shows me nothing when I leave them blank.

Am I going about this in the wrong way ? Should I use the calculated fields in the table instead - if that's possible?

Any help would be appreciated, thanks.

 

[fneily]

Calulations are not criteria. Try:

Field: [FirstRepayment]:[LoanAmt]-([Repayments]-1)*[OtherPayments]

And you have no field called OtherPayments.

The column heading is to the left of the colon, the calulation is to the right of the colon.

 

[Golom]

Criteria" are conditions that restrict the records that will be retrieved from the table. They do not do calculations that appear in the result.

Your expressions should appear in the definition of the fields in query designer ... not on the criteria line.

You also can't refer to the result of one calculated field in another calculated field. You must repeat the calculation.

In SQL view it should look something like

SELECT ... Other Fields ...,

       ([LoanAmt]-([Repayments]-1)*(Round([LoanAmt]/[Repayments],2))) As FirstRepayment,

       Round([LoanAmt]/[Repayments],2) As OtherPayments

From ...

 

[MSealy]

Thank You - once I'd put all the fields in (instead of the .*) it worked !

 

[MSealy]

Now if I was to put the names in a separate table, called Employees, then link it to a field called NameID in the Loans table, presumably I would need an INNER JOIN for this query, yes ?

 

[lespaul]

Yes, that's right, have you read:

Fundamentals of Relational Database Design
and
Understanding SQL Joins

Leslie

Have you met Hardy Heron?