Query to seperate active/inactive clients

[csnyder1582]

I'm an insurance agent setting up a database to keep track of my clients. It has all the basic information; name, address, phone, email, policy #, type of policy, etc...). I have a table set up that will contain every client. What I want to do is set up a form for my active clients and a form for my inactive clients with a drop down menu in both forms to move my clients to their respective place.



Any help would be greatly appreciated.

[pbaldy]

Normalized database design would leave all the clients in one table, with a field for the status. You would use one form and either open it filtered to one group or the other, or include a control at the top to change views. Typically I'd expect the policy info to be in a related one-to-many table, based on the assumption that a client could have more than one policy (auto, home, life, etc).

[islo]

It would be helpful to know how many different statuses can your client have over time. For instance, Active, Inactive, Closed, Denied etc.

Having said that, a "status" field in the clients table would be the quickest way to achieve this.
However, if your clients will be changing status every now and then, maybe you may want to create a new table, say "Client Status" and this table will save all the client statuses over time.
This can help you see a history of a clients status eg, Active on 5/5/2011, Inactive on 4/15/2010 etc.

[csnyder1582]

My clients will have 1 of 10 statuses and I would like 1 form to contain all my active clients and 1 form to contain the rest.

From the research I've done it seems I'd want to have a form with its source being a query of active and a form with a query for those with the other 9 possibilities; I just don't know how to set this up

[pbaldy]

I would only use one form, but to use two, each would be based on a query, along these lines.

SELECT * FROM TableName WHERE Status = "A"

SELECT * FROM TableName WHERE Status <> "A"

[islo]

SELECT LNAME, FNAME, STATUS
FROM tbl_Clients
WHERE STATUS="Active";

This is a querry that will give you a list of client last name &first name and their statuses if they are Active...