Design Question - Access 2010

[mburch2000]

I have a Master Table with MasterID & Status among other fields. If the Status=FollowUp, then I need to follow up on that record. I created a FollowUp Table and inserted all records with a Status=FollowUp from the Master Table into the FollowUp Table. I did this by creating an Append Query which did work.

Once all the FollowUp records are in the FollowUp Table, I can write a query to identify the FollowUp records if there is only one follow up. However, if a MasterID (Client) has multiple follow ups in the FollowUp Table, I don't know how to identify them.

At the end of the day, I need to identify all Follow Up records, and there may be multiple follow ups. But once a MasterID (Client) has a status not equal to FollowUp, then I do not want to pull those records. All I want in my FollowUp Form are Clients that still has a Status=FollowUp. So the FollowUp Table may have record #1with a Status=FollowUp, record #2 with a Status=FollowUp, but record #3 with a Status=Declined. Once the Status is not FollowUp, I don't want that Client to show up in my FollowUp Form. I hope this makes sense. I appreciate any insight.

Thanks,
Mike

[Rabbit]

There's no need for a follow up table. You're just duplicating and denormalizing data that way.

You can just use a query that returns just the records where the status is follow up. There's no need for an append query and there's no need to run a process at the end of the day to sync the data. Using a query instead will make the data real time.

[mburch2000]

There are 5 statuses a client can have, only one being follow up. When the record is entered into the Master table, I used the query to insert the follow up records into the follow up table so I can deal with just them. I need to track multiple follow ups on a client which is why I created the follow up table.

[Seth Schrock]

Rabbit is correct. Doing it his way will make your life so much easier. You said you have a follow up form. Just change the record source to be the query that just picks out the records with a status of Follow Up, and as soon as you change a status to or from Follow Up, it will appear/disappear from the form (a requery would be needed if the form was already opened when the change was made).

[mburch2000]

When the record is entered into the Master table, I used the query to insert the records with a Status = FollowUp into the FollowUp table so I can deal with just them. I need to track multiple follow ups on a client which is why I created the follow up table. I created a FollowUp Form which I want to pull all "active" follow up records, so they can be followed up on. Follow data will be logged on each follow up. Once a status doesn't equal follow up, I don't want to pull the client into the FollowUp Form. I can then report on the clients with follows ups and what eventually happened with that client. See tables below:

tblIntakeMaster - data is originally entered into this table
IntakeMasterID = PK
ContactDate
ClientName
ClientAddress
ClientCity
ClientState
ClientZip
ClientEmail
ClientPhone
AccidentDate
InjuryType
ClaimType
ClaimDescription
Priority =Yes/No
IntakeStatus = FollowUp, Accept, Decline, ReferOut, NoResponse
FollowUpDate
FollowUpPlan

tblIntakeFollowUp - this is used to track follow ups
FollowUpID = PK
IntakeMasterID
StatusDate
IntakeStatus
FollowUpDate
FollowUpPlan

Let me know if this helps

Thanks,
Mike

[mburch2000]

If I eliminate the FollowUp Table, how will I track multiple follow ups? I need to retain the follow up information (date, plan, follow up date, new status) on each client that originally has a status = follow up. I need to report on all client activity.

[Seth Schrock]

I believe that you have a different design problem which is cascading down to your current problem. What I would do (I think, but I don't know your exact situation) is to have a table that lists your clients information (first 9 fields) and then have a table of accidents that would be related to your client table. I would do this because it would be possible for one client to have multiple accidents. In your accidents table, you would have the 10-17th fields plus a ClientID field would would be used for the relationship between the tables. Now, if you need multiple follow ups per accident, then you would drop the last three fields from your accident table and put them in a follow up table that was those three plus an AccidentID field for the relationship to the accident table. You would then run the query that Rabbit suggested on this table. However, you wouldn't be moving information between tables based on which status they were. That is one of the purposes of the query: sorting out data based on criteria.

Look at this link and see the hows and whys: Database Normalization and Table Structures

[Seth Schrock]

Here is a sample database that I did real quick to show you how it works. If you run the query right now, you will return one record. If you open tblFollowUp and change the second record to Follow Up (using the drop down box) and then run the query again, you will return two records. If you change either one of the records in tblFollowUp to another Intake Status, then you will again only return one record in the query.

[mburch2000]

This database is for tracking marketing information on potential new clients. As new cases are being reviewed, they are either declined, accepted, followed-up on, referred out to another firm, or the client stopped responding. About 40% of new cases are followed up on to see if they want to accept the case. That means 60% of the cases entered are done as soon as they are entered as far as the database is concerned. Sometimes, more research has to be done, so there may be multiple follow-ups after speaking with a client. Within 1 or 2 weeks latest, the case is either declined, accepted, referred out to another firm or the client stopped responding.

The Master Table tracks all clients entered and through what marketing source they came through. The FollowUp Table is suppose to track clients that has multiple follow-up records. If a client comes back because of another accident, they are treated as a new case

[Seth Schrock]

That seems to fit my database exactly. The tblClient information only needs to be gathered once. Each record in tblAccident would be a separate case. Each case has (or can have) multiple follow ups through tblFollowUp. If you open tblFollowUp, you will notice that each record has the same AccidentID: 1. Both of those follow ups are tied to the same accident/case.

[Seth Schrock]

Here is the same sample database with a form that shows a little better how you can view/enter the information. Just open the frmMaster. You have the client information on the Client Info tab and the Case/Accident & follow up information on the Case Info tab.

[twinnyfo]

Mburch,

Seth is on the right track.... The only information in your follow-up table from your Clients table should be the MasterID. This is a standard configuration for normalized DBs.

[mburch2000]

Seth,

I appreciate your assistance. You are right I do have some design problems, but my main issue still exist. Working from your sample database, if I add a 2nd record to the follow up table for accident 1, the query pulls both records as expected. When I add a 3rd record to the follow up table for accident 1 with a status of declined, the first two records still show up in my query. If all three records in the follow up table are tied to the same accident, in this case accident 1, once the newest record is added with a different status other than follow up, I don't want the query to pull any of the records for accident 1. This way I get a "true" picture of the accidents I need to follow up on. Is there a way to modify the query based on date or some other criteria?

Thanks,
Mike

[Seth Schrock]

Ahh... The question becomes more clear. So the question that I have for you is, if the status is follow up, will you always want every follow up to be listed? What I can do, is move the status field to tblAccident which would make the status true for every follow up record. If the status of the Case is follow up, then it will list the one or more records in tblFollowUp that are related to that case. If the status of the case is anything else, none of the records in tblFollowUp that are related to that will be listed.

[Rabbit]

Outer join to a subquery that returns records with statuses of not followup. Then exclude them by filtering for the nulls.

[mburch2000]

Yes exactly Seth. As long as the status is follow up I want to see all follow up records. Once the status changes to something other than follow up I don't want to see any of the records.

[Seth Schrock]

Try this. I moved the status to the Accident table and the query then looks through all the records in tblFollowUp that are tied to a Accident/Case whose status is FollowUp. If this is what you are looking for, I would create a report based on the query that groups by all the client information. That way, the client information will only be listed once, but each follow up record will be listed under it.

[mburch2000]

Seth, this is great. You did a few things with the forms that I didn't know about. I just have one question on the frmMaster, on Page21 (Case tab), on the botton section with sfrmFollowUp. The IntakeStatus field is listed which I need, but it is not in the tblFollowUp which is where the data comes from that populates the sfrmFollowUp and it does not work. Do you have a way to get the IntakeStatus field working? If so, I think we have a winner!

Thanks,
Mike

[Seth Schrock]

Oops. My bad. I forgot to change the form design when I changed the tables. Try this.

[mburch2000]

Oh I see. I didn't think about using the Status field as you did, but it works!!! Now I'm going to figure out how you created the Master Form with the SubForms inbedded into the 2nd tab. Once I got this I should be able to modify my database to work. You are the man!!! Thank you very much!

Mike :-)

[Seth Schrock]

Not a problem. If you need help with how to work with subforms, just post your question in a different thread and somebody will be able to help you out.