Multiplying form field with query result

[Chrisqld]

I have a test db, very simple and I need to count the number of ticked boxes on the form (Range Fee) and then multiple the result by the cost which is derived from a query . I have attached the test DB.

Chris D.

 

[Ranman256]

you dont count items on the 'form', you count items in the TABLE.
Is this what you mean?
If so, build a query, add the fields: Member, RangeFee.
turn on the COUNT button
set RangeFee to Count
run query

 

[sneuberg]

I see a couple of problems. First the Member field in the RangeField would be a foreign key of the Members Table so it should be the primary key of the Members table (ID) rather than the Member field or You could make Member the primary key of the Members table (It would have to be unique). Secondly there's no relationship between the Fees table and the RangeFee table. One way to fix this would be to change the checkbox into a number field fee which would have the ID from the Fees table for the applicable fee. No fee could be indicated by a null entry.

I've changed things around in the attached database to a structure that might work better for you. You can look in Relationships to see how I recommend setting this up. I added some primary keys and foreign keys and created relationships between the tables. I changed the RangeFee form to accommodate these changes. If you are wondering why it is displaying the existing records now it's because I set the Date Entry property to No. I change the Fees Query to an aggregate query which shows the total of range fees by member for all of the records in the RangeFee table, i.e., I didn't constraint the query to a date range.

 

[Chrisqld]

OK. The form is based on the table 'RangeFee' and is used to populate that table. The form works exactly as its was intended ie to add the date, members name and a check box as to the payment (or not) of range fee to the RangeFee table. No data about the total of range fee payments for that day are required to be kept in the table. As to the source of the count, I don't know, but the calculation is done on the FORM at Text box 8 in the form footer. The calculation is =Sum(Abs(RangeFee)) to get the total number of check boxes checked and this part works fine. Then I can add =Sum(Abs(RangeFee))*12 to get the total range fee on the form and that works fine as well. However the Cost of the RangeFee can vary from time to time and I would like the calculation to select the CURRENT RangeFee Cost from the Fees table. I have a query which returns the correct value but I cannot, for the life of me, work out how to multiply the Sum(Abs(RangeFee)) by the Fees Query and get a correct output on the form.
The next step (once this is solved ) is to create a report based on the form. That bit is simple. Just the calc eludes me.

 

[sneuberg]

Maybe you could use DLookup to get the RangeFee.

 

[Chrisqld]

Thanks Steve, I had thought about the DLookup but was put-off by all the negative comments throughout the forum. HOWEVER persistence pays, all good now with the text box control source =Sum(Abs(RangeFee))*(DLookup("[Cost]","[Fees]","[ID]=1")). Counts the range fee ticks and multiplies by the range fee cost in the Fees table to produce a total of range fees for the day!
(So that we can check the cash in hand against the number of members who have paid their range fee)

Regards
Chris Devitt

 

[sneuberg]

Thanks Steve, I had thought about the DLookup but was put-off by all the negative comments throughout the forum.

DLookup gets negative comments because of the performance impact it can have and because most times the use of it indicates problems with the database structure. This is true with your database. It not in normal form and the relationships haven't been defined. The problems you had getting this simple calculation will continue as long as you stick to the structure you have.

Good luck with your project.