Question Mathmatical field based on number of records

[Core]

Hello,

My goal is to have a database with two tables and a form.

The tables will be linked. The first table with have a field titled "Number of Positions" the table linked to it will be people that have positions. there will be a field in the first table titled "Number of positions remaining". I want that to have a formula such as this:

'number of positions' - 'record count of people that have positions'

The number of positions will come from the field in table a and subtracted from that will be the count of linked records in table b.

Does that make sense? Sorry I am not the best at explaining.

Kind Regards,

 

[Trevor G]

You can't have a calculation in a field in any Access Database that is lower than 2010. So the question is what version are you using?

Can you upload a sample of your database?

Either a form or query could be used to do this calculation.

 

[Core]

You can't have a calculation in a field in any Access Database that is lower than 2010. So the question is what version are you using?

Can you upload a sample of your database?

Either a form or query could be used to do this calculation.

Sorry the field wont be a field in the table, just a text boc on a form. I tried this but it seems to only return 1, even when their are 2 records:

=[ep_POffered]-Count(DLookUp("[ID]","tbl_Placements","[EmpID] = " & [ID]))

ep_POffered is a field in a table with a numerical value (the total number of placements offered by a company)

Count(DLookUp("[ID]","tbl_Placements","[EmpID] = " & [ID])) I was hoping would return the total number of records in the placements table, EmpID is the ID of the master table which is linked to.

 

[Trevor G]

There is a number of options about counting records in a table look at this function

Function CountRecords()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim queryNameOrSQL As String
queryNameOrSQL = "qryCount"
Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
rs.MoveLast
rsCount = rs.RecordCount
'Me.txtTot.Value = rsCount
Debug.Print rsCount
End Function

Then also you can use the DCount like this behind the textbox

=DCount("*","tblEmployee")

It maybe useful to upload a copy of the database so I can take a look.

 

[Core]

I can work with that. Thank you very much!!!

There is a number of options about counting records in a table look at this function



Then also you can use the DCount like this behind the textbox

=DCount("*","tblEmployee")

It maybe useful to upload a copy of the database so I can take a look.

 

[Trevor G]

Hope it works for you. Glad to assist.

 

[smig]

you don't need the "Number of positions remaining" field at all (this is calculated field)

create one query using table2 only that will count the number of positions taken grouped by your table's1 key (the key must be in table2 to link them)

create a second query and link this fist query to table1. in this query calculate "NumberOfPositions" - "CountOf_PostionsTaken"
"NumberOfPositions" can be taken from a table or from a field on a form