Conditional values in the table.

[Maxihero]

Hi guys,

I have two tables "Funds" and "Classes" with one-to-many relationship, resp. (through the "Fund_name" fields). There is the field "Active" in the "Funds" table and the field "Open/Closed" in the "Classes" table. What I would like to do is to put "No" value into the "Active" field record if the corresponding (with respect to the relation of course) "Open/Closed" field records have all values "closed". Otherwise put "yes". Is there any way, how I could make this?

Many thanks,

Michal

 

[Insane_ai]

Use a query or DCount statement to count the "Open/Closed" field for values of "open". If the count >0 then use an update query to set the active field to yes, filtering those that are already Yes to begin with.

Example:

Dim intCount as Integer
intcount = DCount("open/closed","Classes","Classes.[open/closed] = 'open'")

If intCount >0 then
run update query
End if.

This is pseudocode, it may not work properly on your machine but it should point you in the right direction

 

[Maxihero]

Hi ai,

Thanks for your answer. I already have a query counting the number of "open" values, but I am not sure how to update the values in my "active" field in the "Funds" table based on these results. Could you be please bit more specific? Like what code exactly I should make and where exactly should I put it (I'm not so skilled in Access..)?

Many thanks,

Michal

 

[Insane_ai]

If you have a query like this to count the open Items:
Query: closed_count

SELECT Classes.fund_name, Count(Classes.[Open/Closed]) AS [CountOfOpen/Closed]
FROM Classes
GROUP BY Classes.fund_name
HAVING (((Count(Classes.[Open/Closed]))="open"));

You would create an update query like this:

Query: Update_Active_Flag
UPDATE Funds INNER JOIN closed_count ON Funds.fund_name = closed_count.fund_name SET Funds.active =Yes
WHERE (((closed_count.[CountOfOpen/Closed])>0) AND ((Funds.active)=No));

The examples are working queries based on the information provided that I created in a test database.

 

[Maxihero]

Many thanks ai, this is exactly what I've needed. However, I'm getting the following error message:

"operation must use an updatable query"

.

I've copied exactly your query, so I'm not sure what might be wrong:

UPDATE [Fund Level Records] INNER JOIN [Stats Open] ON [Fund Level Records].SSB_FUND_CODE = [Stats Open].SSB_FUND_CODE SET [Fund Level Records].Active =Yes
WHERE ((([Stats Open].[#OfOpenClasses/Fund])>0) AND (([Fund Level Records].Active)=No));

 

[gemma-the-husky]

Note. once you have a totals query, (ie using sum, or count) then this query or any query using this query is automatically non updateable. ie the results can be viewed, but not updated.

Now - generally, if you can derive the value of Active from other data in the same table, or even anywhere in the database, then DO NOT store it.

eg - what happens if somehow you get a wrongly set value of Active. The data suggests "true", but setting is "False". Say you correctly set "Active" as false, but then one of the classes becomes "open", then you need to reset "Active" as true.

what you do, is just apply the basic rule (generally with a query) each time you need to evaluate "Active"

This is a standard point of normalisation. You really do need special considered reasons to decide to store calculated data, and this is not likely to be one of them, as if it was, you probably wouldn't need to ask the question


In this case "Active" is simply determined by having any item with "class" of "open", I would think.

Try a query from the classes table selecting unique funds where "open" setting is true. that ought to work.

 

[Maxihero]

Hi Dave,

I'm not sure if I understand.

what you do, is just apply the basic rule (generally with a query) each time you need to evaluate "Active".

The question is how?

 

[gemma-the-husky]

i mean do not calculate and store whether the fund is active or not.

if you are using a query to determine whether the fund is active, then you can always use this query when you need to know whether the fund is active.

if a fuind can never become active again, after it becomes inactive then the duplication may not be an issue but if there is ever a chance that a fund may flip between active, and non active, then do not store the setting


so create a query joining the funds to the classes, selecting those classes which are active. make this query a "unique values" query. This query shows the active funds. You do not need to store the active setting in the funds table

use an unmatched query based on this query to find the inactive funds

 

[Maxihero]

The problem is, that in the form I need to separate the active and inactive funds and it must be automatic (meaning, the actuality of the fund must be chosen automaticaly (not manualy by user)). I want to have two subforms - one Master - "Funds level info" and child - "Classes level info" and I need some option to easily switch between active and inactive funds (in the funds level info). Funds can flip between active and inactive. But I might just give up on Access. It's so pain to make anything at least a bit dynamic or user friendly (even the really natural things)..

 

[Insane_ai]

Try this instead:

UPDATE Funds INNER JOIN Classes ON (Funds.Fund_Name = Classes.Fund_Name) AND (Funds.Fund_Name = Classes.Fund_Name) SET Funds.Active = Yes
WHERE (((Funds.Active)=No) AND ((Classes.[Open/Closed])="Open"));


This bypasses the count issue. It will result in duplication of updates if there are more than one related Open record in Classes but it will still work.

 

[Maxihero]

Thanks a lot Insane ai! I really like your solution. The only problem is, that the query doesn't include the changes from "Yes" to "No" (in case all classes are closed). But I guess here we really cannot avoid counting.

 

[Maxihero]

Well, at the end it's not so difficult. The one just need to run first the update query to update all active values to "No" and after run the Insane ai's query. It's very cumbersome (as nearly everything in access..), but it works. Thanks again!

 

[gemma-the-husky]

The problem is, that in the form I need to separate the active and inactive funds and it must be automatic (meaning, the actuality of the fund must be chosen automaticaly (not manualy by user)). I want to have two subforms - one Master - "Funds level info" and child - "Classes level info" and I need some option to easily switch between active and inactive funds (in the funds level info). Funds can flip between active and inactive. But I might just give up on Access. It's so pain to make anything at least a bit dynamic or user friendly (even the really natural things)..

yes - but you use a QUERY based on the funds table to select/sort the items - which again avoids needing any need to STORE anything in the Funds table

it isn't a pain. but it's not excel, and there is a learning curve.

and access is completely friendly, when you get used to it

 

[Maxihero]

Thanks for your advice, Dave. However, I don't fully understand - ok, let's say that I don't store anything in the Funds table. I run the query, which gives me the activity of the funds (this query is based on the Classes table). But I still cannot see the way how I could make (without storing this data) the form (with subforms) I've described above (to be honest, I've been trying it many ways (without storing), but I wasn't succesfull). So, finally I did it as follows - in the "On Exit" event of the Classes subform I've incuded the following code (the names are slightly different):

CurrentDb.Execute "UPDATE tbl_FundLevelRecords SET tbl_FundLevelRecords.Active = 'No';", dbFailOnError
CurrentDb.Execute "UPDATE tbl_FundLevelRecords INNER JOIN tbl_ClassLevelRecords ON (tbl_FundLevelRecords.SSB_FUND_CODE = tbl_ClassLevelRecords.[Fund Code]) AND (tbl_FundLevelRecords.SSB_FUND_CODE = tbl_ClassLevelRecords.[Fund Code]) SET tbl_FundLevelRecords.Active = 'Yes' WHERE (((tbl_FundLevelRecords.Active)='No') AND ((tbl_ClassLevelRecords.Type)='Open'));", dbFailOnError

And it works fine. I have my two Master - child subforms and in the main form the possibility to filter the Master (Funds) subform to "active" or "inactive" funds. But this still involves storing a data in the Funds table.. Is there something wrong on doing it this way?

You're right, I'm used to work with Excel/VBA, but I still think that those are just very fundamental needs which every database management system should include on the very basic level (meaning the one should be able to manage these things using few steps procedure). While for the moment it's always taking me days to solve such an elementaries (and even the advices from an experts are rather vague and rarely pointing out the exact solution (please, do not take this personally, I'm just trying to explain why I think what I think about Access)).

 

[gemma-the-husky]

ok - i spent an hour doing an example for you

open form1.

at the top you get your funds. when you select 1 you see the expanded fund details below, and the classes that make up that fund as well. The "active" setting on the fund is determined by a query based on inspecting the individual classes, which recalculates when you change a class setting.

i would probably implement this a little differently, but it might give you some ideas. needs a handful of lines of code, because you cannot link a continuous form to a subform automatically.

unfortunately, because the top form includes a totals query (to evaluate the fund status), it becomes non-updateable and you cannot add new funds directly

it might give you some ideas anyway.

View attachment fundsandclasses.zip

 

[Maxihero]

OMG, I'm really sorry Dave - I didn't mean to spend your time. I really appreciate this, it's more than a GREAT HELP. It will surely give me much better image of how to do the things concerning the tables and queries. And I'm sure also to anothers who might read this thread.

Many thanks again!