Update a field that counts records (1 Viewer)

rockell333 · Dec 13, 2017

I have a table that may contain 2 or more records for certain account numbers. I added a field called Account Count that I want to do an update query for populating it similarly to excel's countif.

I simply want to count how many times that account number is in the table.

So if for instance Account number 12345 is in there 3x, for each of the 3 records this field should say 3.And if account number 54321 is in there only once, it would be populated with a 1

Gasman · Dec 13, 2017

Use Dcount()

rockell333 · Dec 13, 2017

DCount([YEAR_IN_REVIEW]![Account VIS],[YEAR_IN_REVIEW]) ???? is that correct. It is asking for a parameter value

I want to update a Count field that counts the account vis in the year in review table.

isladogs · Dec 13, 2017

As Gasman says, use DCount BUT do so in a query when you need that info.

You should not be storing calculated values in a table - as the calculated values will constantly go out of date

EDIT - following on from your reply:
1. You can include the parameter value in your query criteria
2. You need to use quote marks in your DCount expression

Code:

DCount("[Account VIS]","[YEAR_IN_REVIEW]")

rockell333 · Dec 13, 2017

we use a crm this data will not be changing. I actually need the one time update. If there isnt a simple way, I could just update in excel and re-import. But I was hoping there was a way.

isladogs · Dec 13, 2017

It can certainly be done using DCount.

However, even if you think the data will never be updated, its not using Access in the most efficient way

rockell333 · Dec 13, 2017

for what i am doing its exactly what i need.. but back to Dcount.. I posted in my previous post however it is asking for a parameter

DCount("[Account VIS]","[YEAR_IN_REVIEW]")

rockell333 · Dec 13, 2017

"I post the formula" in the previous post... is what I meant to say

Gasman · Dec 13, 2017

rockell333 said:
for what i am doing its exactly what i need.. but back to Dcount.. I posted in my previous post however it is asking for a parameter

DCount("[Account VIS]","[YEAR_IN_REVIEW]")

For Dcount you need the following
DCount(Expression, Domain, Criteria [optional])

so you need to check for whatever the account number value is
DCount("*","YEAR_IN_REVIEW"," [Account VIS]=" & Me.AccountVis)

where Me.AccountVis is the control on your form holding the account number

However check your names as the criteria is optional and if you had everything correct as you coded the function I'd expect it to give you the number of record in the table?

HTH

rockell333 · Dec 13, 2017

So I just tried his version and that doesnt work at all. Account VIS is a field in the year in review table. It is easier for me to just do this in excel and re-import or just repull from Salesforce through the server and add it to the pull.

What parameter could it have asked for, any ideas

rockell333 · Dec 13, 2017

Gasman trying that now

so that is saying
Count all in the year in review where the account vis field = account vis field.. right

rockell333 · Dec 13, 2017

ok.. one sec here.. i think it is backwards

year in review is the table everything is in
account vis is what i am trying to count
count vis is the field I am trying to update

Gasman · Dec 13, 2017

rockell333 said:
ok.. one sec here.. i think it is backwards

year in review is the table everything is in
account vis is what i am trying to count
count vis is the field I am trying to update

You need ctriteria.
You have the table correct, we can use the * as we are not bothered on what is returned as we are after a count.
You then need to compare values, so in your initial post it would be along the lines of

Code:

DCount("*","YEAR_IN_REVIEW"," [Account VIS]=12345")

but as you want it for any account number use the control that holds the account number.?

Code:

DCount("*","YEAR_IN_REVIEW"," [Account VIS]=" & <YourAccountNumberHere>)

Make sense now?

How you update the control is up to you. You can do it from the source, prefix the dcount with =, or set in VBA
Me.[Count Vis] = DCount("*","YEAR_IN_REVIEW"," [Account VIS]=" & <YourAccountNumberHere>)
probably in the Current event of the form

rockell333 · Dec 13, 2017

ok.. yea I think I get it.. I have to get ready to go in.. I will test this again when I get there

arnelgp · Dec 13, 2017

Use Update Query to update the field:

UPDATE [YEAR_IN_REVIEW] SET [YEAR_IN_REVIEW].[Count VIS] = DCount("*","[YEAR_IN_REVIEW]","[Account VIS]='" & [YEAR_IN_REVIEW].[Account VIS] & "'");

rockell333 · Dec 13, 2017

Perfect

+arnelgp

Thank you

Gasman · Dec 13, 2017

rockell333 said:
Perfect

+arnelgp

Thank you

My apologies rockell333, for some reason I thought you were trying to update a control on a form? and so my advice was leading you in the wrong direction.

Hopefully though it will come in useful later on?

rockell333 · Dec 13, 2017

+Gasman - no worries. It is just an export from Salesforce that I am using for a mailing and I want to check something..

As for what you gave me... I store everything.... I am sure it will come in handy at some point.. Thanks

Update a field that counts records (1 Viewer)

rockell333

Registered User.

Gasman

Enthusiastic Amateur

rockell333

Registered User.

isladogs

MVP / VIP

rockell333

Registered User.

isladogs

MVP / VIP

rockell333

Registered User.

rockell333

Registered User.

Gasman

Enthusiastic Amateur

rockell333

Registered User.

rockell333

Registered User.

rockell333

Registered User.

Gasman

Enthusiastic Amateur

rockell333

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

rockell333

Registered User.

Gasman

Enthusiastic Amateur

rockell333

Registered User.

Similar threads

Users who are viewing this thread