Multiple conditions in calculated fields

[Maxihero]

Hi guys,

Please, is there any way to put into the calculated field (in expression builder) conditions? What I need is something like

Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)

I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

Nothing really complicated, but I am not sure about the syntax (and even if it's feasible).

Thank you!

 

[pr2-eugin]

You would be better off using VBA for this sort of calculation Max.. In my opinion.. It is quiet easy..

 

[plog]

the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

Your words are getting in the way of your idea: Tables can't be in fields and I have no idea what you actually mean with the word 'actual'.

Can you post some sample data from your tables (along with table and field names) and then what you want the result to be based on that sample data?

 

[Maxihero]

I agree Paul, in VBA it's very straight forward. But where should I put the code, so that the (calculated) value will be properly updated every time there are some relevant changes?

plog: Sorry for the confusion - I meant that the VALUE is from the actual table (the table where is the calculated value) from the Field2. So l have two tables, let's say (I rather won't use my actual tables, as it would be even more confusing) - "Animal classes" and "Animal classes specifications". In the table "Animal classes" I want a field "# of animals in each class". This field will count the number of matching classes in the table "Animal classes specifications" with "S" in the "Specification" field. As follows:

Animal classes:
Class # of animals in each class
dogs 2
cats 1
cows 0

Animal classes specifications:
Class Specification
dogs S
dogs S
dogs K
cats S
cats K
cows P
cows K

Hope it's clearer now. Thank you!

 

[pr2-eugin]

In the table "Animal classes" I want a field "# of animals in each class".

No you do not.. I do not see any need for this information to be inside the table.. Look into Allen Browne's site regarding 'Calculated filed storage'.. If you want to just display the value that can be achieved by VBA..

BUT a very simple and straight forward solution would be to use a Query..

This field will count the number of matching classes in the table "Animal classes specifications" with "S" in the "Specification" field. As follows:

Lookup on JOIN and GROUP BY queries to get you started.. Post back, if you find it hard..

 

[plog]

Maybe, I'm lost, but this seems like a simple aggregate query.

SELECT Class, COUNT(Class) AS AnimalsInClass
FROM AnimalClassSpecification
GROUP BY Class;

Then you left join from your Animal classes table to it.

 

[Maxihero]

No you do not..

Well.. ..I do. I don't see why you don't see the need for this information, but in my situation it is indeed necessary (at least users want to have it there). Allen Browne's site is dealing only with forms and after update property, so I'm still not sure where should I put the code in the case of the table field. This I'd like to know as similar things would be much easier (at least for me) to build using VBA instead of SQL and queries. However, thanks to your advice I've managed to build the right query. There's just one last thing - is it possible that the query would be updated automatically?

plog: You're not, it is indeed very basics.. Unfortunatelly, I don't have much experience in Access. :/

Then you left join from your Animal classes table to it.

Could you be please more precise? I'm not sure how to add these values to my "Animal classes" table? :/

Many thanks Paul and plog.

 

[plog]

So, you with your admited limited experience of Access absolutely know its indeed necessary to store a caculated value? Color me unconvinced. Now, convince me.

but in my situation it is indeed necessary ... is it possible that the query would be updated automatically...I really need to have them there...I don't have much experience in Access

You're actually arguing against yourself. You want to store this, since its imperative, but you also want them to update automatically. This mystical automatic update occurs when you don't store the value. Saying you want that, means you should calculate that value whenever you need it, not store it. I suggest you do just that--whenever you want the totals--you use this query to get them.

 

[Maxihero]

plog, I'm very sorry I didn't convince you.. (Nevertheless I really appreciate your help, I'm not sure your reaction was appropriate) But I'll try to ignore the way you expressed yourself and explain myself again: I really cannot see the problem in willing to store the calculated values. (I find it completely legitimate) It is the same kind of data as any others. I need to have them updated as the users need to SEE it correct and as it is used in another applications (and I want it linked to an Excel file (meaning that it will be exported there from the table)). I cannot use the query every time, as those will be the users who will make the changes and they will need to see these changes straight. Unless it can be used automatically. Yes, automatically..

Thank you very much for your time.

 

[pr2-eugin]

But I'll try to ignore the way you expressed yourself and explain myself again: I really cannot see the problem in willing to store the calculated values.

Maxi.. plog is a very experienced in this field.. sometimes it may 'feel' that it is very important to store such information, in the table.. I have thought the same several times while I started as a DB developer.. But as time went on it proved me wrong..

(I find it completely legitimate) It is the same kind of data as any others. I need to have them updated as the users need to SEE it correct

As explained earlier there is absolutely no need to store the information.. "Why" do you ask?? This is why..

is it possible that the query would be updated automatically...

Unlike Excel.. It will not do it automatically do it.. you have to manually update them.. which is why we do not consider storing calculated values to be even the last option..
As in your words.. they need only to SEE IT.. which can easily be accomplish with the Query plog has given you above..

and as it is used in another applications (and I want it linked to an Excel file (meaning that it will be exported there from the table)).

Queries can also be exported to Excel as well.. Again disproving the need for storing the information..

I cannot use the query every time, as those will be the users who will make the changes and they will need to see these changes straight. Unless it can be used automatically. Yes, automatically..

Automation process can be made swiftly.. If you wish to see the information.. straight away.. That is a different process.. So first getting the information right is what we need to concentrate on..

 

[Maxihero]

Paul, I have no doubts about plog's experience. But let's leave this. I've just thought that the requirement to have some data calculated (automatically (as in Excel)) might be completely understood. Running the query every time seems not very user-friendly and to build it automated yourself is quite complicated for such a natural thing. But be it. I'm convinced.

As I've said I've managed to build the query. The plog's query didn't count the 'S' values, so I did it as follows:

SELECT [Fund Level records].[SSB FUND CODE], Count([Class Level Records].[Snap/PD&Close]) AS [CountOfSnap/PD&Close]
FROM [Fund Level records] INNER JOIN [Class Level Records] ON [Fund Level records].[SSB FUND CODE] = [Class Level Records].[Fund Code]
WHERE ((([Class Level Records].[Snap/PD&Close])='S'))
GROUP BY [Fund Level records].[SSB FUND CODE];

The only problem is, that using WHERE, it doesn't show me the records with no 'S' values (if there's no 'S' record I'd like to see 0). Do you know the way how I could make this please?

Thanks Paul.

 

[plog]

Then you LEFT JOIN from your Animal classes table to it.

You save that query I gave you, then you create a new query based on it and a data source that has all the unique Animal classes you want to report on in it.

 

[Maxihero]

Thanks plog.

With your help I've finally found what I've been searching for:

http://timmurphy.org/2010/10/10/conditional-count-in-sql/

 

[Maxihero]

For the Access, there's need to use "iif", therefore:

SELECT [Fund Level records].[SSB FUND CODE],
SUM(iif ( [Class Level Records].[Snap/PD&Close]='S', 1, 0 )) AS [CountOfSnap/PD&Close]
FROM [Fund Level records] INNER JOIN [Class Level Records] ON ([Fund Level records].[SSB FUND CODE] = [Class Level Records].[Fund Code])
GROUP BY [Fund Level records].[SSB FUND CODE];