Adding A Formula as a Field

[Guest]

Is it possible to have a formula in a field. I would like to add this formula
to my database (this is an excel formula)

countif($A$2:A2,A2)=1

Thank you in advance.

 

[Joseph Meehan]

Is it possible to have a formula in a field. I would like to add this
formula to my database (this is an excel formula)

countif($A$2:A2,A2)=1

Thank you in advance.

Well you can all kinds of formulas although I don't think the one you
listed is valid.

You would not add them to the table. You can add them to queries, forms
and reports depending on your needs.

 

[Guest]

Arrgh! You are doing what is known as "committing spreadsheet." ;-)

No you can not do this in a table. Tables are only for holding data. They
might look like spreadsheets; however, they are not.

Having said that, you can put a formula in a query that is based on a table.
I am not familier with the countif function in Excel. Aha! The formula below
would count the number of times apples show up in cells A2 thru A5.
=COUNTIF(A2:A5,"apples")

You probably wouldn't use a formula at all in Access. Something like below
would work if txtFruitName contained the same data as Column A and the table
was named tblFruit.

SELECT txtFruitName, count(txtFruitName)
FROM tblFruit
GROUP BY txtFruitName
HAVING txtFruitName = "apples";

 

[John Vinson]

Is it possible to have a formula in a field. I would like to add this formula
to my database (this is an excel formula)

countif($A$2:A2,A2)=1

Thank you in advance.

Excel is a spreadsheet, a good one.
Access is a relational database.
THEY ARE DIFFERENT!

Tables are *NOT* spreadsheets, do not contain formulas, and should not
be used for anything but debugging.

Could you explain the structure of your table, and just what you want
this formula to do? I'm sure it can be done using a Query or a Form
(the appropriate tools for formulas in Access) but it's not clear what
you're trying to accomplish or what you're starting with.

John W. Vinson[MVP]

 

[Guest]

Thank you John. The excel formula identifies (return "true") for the first
occurance of a value.

My database has multiple occurances of a given value. I need to filter this
down to just the first occurance.

Is that possible in Access ?

 

[John Vinson]

Thank you John. The excel formula identifies (return "true") for the first
occurance of a value.

My database has multiple occurances of a given value. I need to filter this
down to just the first occurance.

Is that possible in Access ?

Not as stated; you're still "committing spreadsheet".

A Table - unlike a spreadsheet - has no usable order. It's like a
bucketful of records. There is no (usable) concept of "first" or
"next" in a Table; only in a Query sorted by some value in the fields
of the table.

Stop, step back a bit, and take the time to read up a bit on
relational databases and how they work. Access is not "a big version
of Excel", and trying to apply spreadsheet logic to it will get you
nothing but major frustration!


John W. Vinson[MVP]