How to handle “inactive” records?

[Sarameier]

Dear all,

I am just a beginner with access. Therefore, please apologize if I am not yet familiar with most of the Access functions.

My situation:

I am currently using Access 2016.

My main purpose is to have a simple Form-Subform structure. This means for example, having

• a form with a user (user A)
• which includes a subform showing number of related accounts (account 1,2,3..)

For this, I have just been using the subform function in Access. That means:

• I had related the tables USER and ACCOUNTS through a lookup field
• And I used the form wizard and then included both tables USER and ACCOUNT
• And the form wizard created a form including a subform automatically
• The completed form now allows me to just choose a user and to see all related accounts listed in the subform

So far so good!

My question is now: how to deal with “inactive” records?
For example, there might be

• users which should not appear anymore in the choice list, because they have left
• accounts which are not active anymore, and the should not appear anymore in the subform, even if they related to a chosen user

Should I therefore create two queries

• which would show only the “active“ records (query_active_users, query_active_accounts)
• and create a new form with these two queries subsequently?

Thanks for all answers in advance

 

[arnelgp]

you add a Yes/No field (Active) to your table.
Create a Query that will filter your table to only include when this field is set to Yes.
use this Query as recordsource of your form/combobox, etc.

 

[jdraw]

It seems you have to be clear in what you want your query(ies) to return.
If Active accounts means " all currently active users and all of their currently active accounts", then you have to constrain your query to Active Users and Active accounts.
If you want to return all accounts for a specific user, then you're dealing with that specific user and all of his/her accounts active or Not.

Seems you can have active/inactive users, and active/inactive Accounts.

??Can an Inactive user have an Active Account????

 

[The_Doc_Man]

You always start from (and often go back to) the drawing board. Literally.

Since you are new to this kind of work, you haven't gained the experience required to understand the best approach. There is an old carpenter's axiom that says "Measure twice, cut once." Fast implementation is not your friend until you have a roadmap. You do best by planning carefully first, laying out IN WRITING - and heck, maybe even drawing a picture-of-concept - so you know what you are trying to do. Why do this, you ask? Because paper has a better memory than people. You have this idea in your head. But memory is fleeting if you don't capture it right away. You have a million thoughts flowing through your head as you try to decide what to do next. But if you have a list, you can prioritize "what to do next" or gather like-structured items to get done in a bunch. Or however you want to work.

Just remember, if you don't have a roadmap, how will you ever know with certainty that you have arrived at your destination?

Now, a couple of simple rules to ponder:

1. If you can't do it in paper, you can't do it in Access. (This is the short way of saying what I said earlier.) Without at least semi-detailed plans, you will eventually drive yourself crazy trying to remember what you intended to do in a given situation because you will have SO many ideas competing for your attention. Projects do that to you. (Trust me... been there, done that, bought that T-shirt, and like an idiot didn't copyright the saying, so it has now fallen into public domain.)

2. Access can only tell you what you told it. OR Access doesn't know what you didn't tell it.

For an example of this rule in action, look at jdraw's and arnelgp's answer. If you want to know that an account is active or inactive, you need to include a field in a table to tell you that - which in turn means you have to include a way for YOU to tell Access to set the field one way or another. The point is, if you have plans (see #1 above) then you know WHAT you need. All that matters then is to follow through to see how to get that information into Access so it can use it later to answer your questions about active/inactive or balance-paid/owes-money or person is a dolt/genius or whatever questions you have.

 

[Pat Hartman]

To summarize, now that you have an ActiveFlg, EVERY single query requires that you be specific and select Active, Inactive, or All.

 

[mike60smart]

Dear all,

I am just a beginner with access. Therefore, please apologize if I am not yet familiar with most of the Access functions.

My situation:

I am currently using Access 2016.

My main purpose is to have a simple Form-Subform structure. This means for example, having

• a form with a user (user A)
• which includes a subform showing number of related accounts (account 1,2,3..)

For this, I have just been using the subform function in Access. That means:

• I had related the tables USER and ACCOUNTS through a lookup field
• And I used the form wizard and then included both tables USER and ACCOUNT
• And the form wizard created a form including a subform automatically
• The completed form now allows me to just choose a user and to see all related accounts listed in the subform

So far so good!

My question is now: how to deal with “inactive” records?
For example, there might be

• users which should not appear anymore in the choice list, because they have left
• accounts which are not active anymore, and the should not appear anymore in the subform, even if they related to a chosen user

Should I therefore create two queries

• which would show only the “active“ records (query_active_users, query_active_accounts)
• and create a new form with these two queries subsequently?

Thanks for all answers in advance

Hi

You should NOT do this:-

"I had related the tables USER and ACCOUNTS through a lookup field"

You should not be using Lookup Fields in your Access tables.

 

[Pat Hartman]

I didn't notice the Lookup Fields mention. As Mike suggests, they are a trap for the unwary.

To be more specific about your queries, you don't necessarily need different queries, you just need an argument that specifies what you want.

Where (ActiveYN = Forms!yourform!ActiveYN OR Forms!yourform!ActiveYN Is Null) AND whatever other criteria

Don't forget to set the table level default for ActiveYN to No.

 

[Sarameier]

Hey all,

Thanks for all these friendly answers, and particularly for Doc Man’s detailed advises.
I am happy to follow all your point.

Please let me get back to square one, looking at only 2 simple tables:

• table Users and table Accounts
• each account has 1 user (i.e. 1 user can appear in n accounts). Therefore, both table are connected as an 1:n relationship

My goal is now to get an application in Access so that I can just

1. click on a choice list, so that I will be seeing all active users
2. click on a user within the choice list, so that I will be seeing all account related to this particular user

State:

I understood that I would need now two corresponding queries.

1. One query: displaying active users
2. One query: displaying active accounts

To create these queries seems very simply.



But what to do next?

Following Pat’s last message: Should I use only one (SQL) argument instead of the 2 queries? In this case, how to implement this in a form so that it is comfortable to use.

Regards,

Sara

 

[CJ_London]

I recommend using a date field rather than a yes:no field. This tells you more such as when an account became inactive. Also means you can project into the future for example to make someone inactive on a specified date in the future and look into the past for example to compare the number of active accounts a year ago with today

Principle would be if the ‘inactive’ field is unpopulated then the record is active and if populated the record is active if the value is later than today (or whatever date you specify) otherwise it is inactive

Criteria would be something like

for active records
inactiveDT is null or inactiveDT>date()

for inactive records
InactiveDT<=date()

The query becomes a little more complex but pays dividends on information


and sometimes you need active and inactive dates e.g. date someone becomes an employee and the date they leave

 

[Sarameier]

... I mean: how to do this practically. Assuming, I had my two queries ready. What do do next?

Arnel wrote:

you add a Yes/No field (Active) to your table.
Create a Query that will filter your table to only include when this field is set to Yes.
use this Query as recordsource of your form/combobox, etc.

But where is the relationship between these two queries implemented? I mean: that each account would have one user related.

If someone could just tell me the next practical steps to be taken on Access, would be really great

 

[CJ_London]

you are not providing enough detail to provide a detailed answer

but assuming you have an inactive field in both user and account tables, you might have a single main form and a continuous subform, the main form to display user information and the subform the account information

The recordsource to the main form would be something like

SELECT *
FROM tblUsers
WHERE inActive=false (or per my example WHERE inactiveDT is null or inactiveDT>date()

this also might be the rowsource to an unbound combo (called say cboUserSelect) in the main form for users to select a user except you would replace the SELECT * with something like

SELECT userPK, userName

and you would need some code in the combo afterupdate event along the lines of

private sub cboUserSelect_afterUpdate()

    me.filter="userPK=" & me.cboUserSelect
    me.filteron=true

End Sub

in the subform, the recordsource would be

SELECT *
FROM tblAccounts
WHERE inActive=false (or per my example WHERE inactiveDT is null or inactiveDT>date()

assuming your relationships have been set up before you create your form (i.e. in tblAccounts you have a field called userFK which maps to userPK in tblUsers) then the subform will automatically populate with the currently selected users accounts

To my previous post I would also add another question - what happens if an account is assigned to a different user? (perhaps the first user has left or is on long term sick and someone has to cover).

 

[Pat Hartman]

1. click on a choice list, so that I will be seeing all active users

Looks like you shouldn't be using a flag at all although I agree in principle that many flags should be dates. The definition of an "active" customer is dependent on whether or not he has an active account which is not what we were originally talking about. Accounts are what should be marked "inactive" and that should definitely be with something called CloseDate. Now the criteria becomes different and you need nested queries.

Create a query that selects only "active" accounts. Join it with the user table using a left join. The criteria becomes

Where (IIf(Forms!yourForm!ActiveYN = True AND tblAccounts.CloseDate Is Not Null, IIf(Forms!yourform!ActiveYN =False AND tblAccounts.CloseDate Is Not Null, True))

The reason for using a separate select query for "active" accounts (you could create a sub query but those are harder to build with the QBE) is because you need a left join to bring back all the users but the criteria is on the right-side table. Without the separate query to isolate the criteria or the sub select, your Left join will turn into an equi-join

 

[arnelgp]

you can of course see when the "account" becomes inactive on it's
last "transaction"
see this little demo for your form build-up.

 

[CJ_London]

Depends on requirements but relying on a last transaction date for the ‘when’ may not be relevant. OP has not mentioned a transaction table, a call table or any other table that would provide that information.

 

[arnelgp]

Depends on requirements but relying on a last transaction date for the ‘when’ may not be relevant. OP has not mentioned a transaction table, a call table or any other table that would provide that information.

how can you have an account without a transaction?
even opening an account is already a transaction.
do you want the OP to disclose everything to you.
exercise some thinking.

 

[The_Doc_Man]

Now, arnelgp, you KNOW, as we have often seen, that people sometimes use the "just update the master record's total" method as opposed to a transaction method. Have we decided which way our OP is going? And the discussion has not, in fact, delved beyond the idea of "customer with multiple accounts" without going much deeper in what it means to have an account. I believe you might have made an assumption.

 

[arnelgp]

Now, arnelgp, you KNOW, as we have often seen, that people sometimes use the "just update the master record's total" method as opposed to a transaction method

you can't deny, if there is some "TOTAL" you are talking, there must be some "DETAILS" somewhere.
maybe, on another dept? but the fact still remains, there will always be a detail part.

even in real life, you can't give a statement of account that is already a Total?
you were from military, you can't summary execute an individual without detail cause?

 

[CJ_London]

I’m not going to progress this discussion any further as it is disrespectful to the OP. We don’t know enough to provide a proper solution

 

[Sarameier]

Hello

Thank you very much to ALL of you. This has been already important help for me.

On my part, I will try to progress. But I will need to take small rather than big steps

 

[Sarameier]

Hi again,

You might have suggested this above: it seems that the simplest solution for me as a beginner is:

1. Creating two queries displaying the active records only
2. Using these two queries to create a form, including a sub-form

That is: Just swapping the two intitial tables for two queries