Moving data from one table to another but complicated!

[ibbledibble]

Hi

I have a database for our customers that contains a number of tables and respective forms. I want to redesign the database as it has a design flaw but this will involve the transfer of a large amount of data from one table to another.

The first table contains all our customer's details and the second table contains details of incidents that happened to that customer. Therefore, there could be many incidents for one customer.

The customer table contains two fields that I want to transfer, 'case status' and 'case level'. I want to put these into the second table 'incidents'. However, as there are more incidents than customers I need to know how to do this so that the data in 'incidents' is correctly linked to the customer. They are already linked by auto number.

Also, if there is an incident that has the case status set to 'open', I want to display a field in the customer form that says 'This customer has an unresolved incident'.

Can anyone please help?

Thanks

 

[neileg]

The customer table contains two fields that I want to transfer, 'case status' and 'case level'. I want to put these into the second table 'incidents'. However, as there are more incidents than customers I need to know how to do this so that the data in 'incidents' is correctly linked to the customer. They are already linked by auto number.

Aren't you going to have to do this for each incident separately? Or am I misunderstanding? Is a case made up of several incidents?

Also, if there is an incident that has the case status set to 'open', I want to display a field in the customer form that says 'This customer has an unresolved incident'.

You could use DCount:

=if(DCount("[CaseStatus]","MyTable","[CaseStatus]='Open')>0, "This customer has an unresoved incident","")

 

[ibbledibble]

Aren't you going to have to do this for each incident separately? Or am I misunderstanding? Is a case made up of several incidents?

Yes, each case is made up of several incidents

 

[neileg]

So don't you need three tables? Customers, Cases and Incidents?

 

[ibbledibble]

No, I already have a table for customers and a table for incidents. Each customer may come back to us at some point with a new incident, so the two are linked to each other. Whenever a new incident is opened for a returning customer, it will be linked to that customer's record by a customer reference number that was automatically generated when the customer's details were originally entered.

In the customer table, I have the fields 'case status' and 'case level'. I want to move these to the incidents table. However, because there could be more than one incident with the same customer ref number, I cannot just copy and paste the data. It would be out of sync in the table and hence in the form. I already have over a 1000 incidents in the database.

 

[neileg]

Confused!

What is a case? You seem to be suggesting that there's only one case per customer. Is that right?

You only have one value for case status and case level. I don't see how this ties in with multiple incidents.

 

[ibbledibble]

Sorry, I'll try and explain again.

Our database is structured with the following tables:

Customers
Incidents
Actions
Outcomes

The main record is the personal details of the customer. These records contain a reference number which is the foreign key in the incidents tables. Therefore the incident is linked to a specific customer. So if a customer breaks his leg, we put his details into the database and then put down that he broke his leg in a linked incident table. We then do whatever we need to do on the case.

Now...say the customer comes back three months later and then says he has broken his arm. We don't enter new customer details but he look for him on the database, go to the incidents form and open a new incident that is linked to him. Therefore, one customer will have many incidents, but with identical foreign keys.

The two fields I want to transfer are called 'case status' and 'case level'. They are currently in the customer table which is the wrong place. They need to be in the incidents table, because if someone comes back with a second incident, it will have a different status to the first.

Do you think I should just start again with a new field in the Incidents table, or will it be possible to transfer the data across?

 

[neileg]

OK the term case is misleading because you are recording incidents. Never mind.

Let's say we have a customer who has two incidents, one complete and closed and another still in progress. What does the case status say?

 

[ibbledibble]

It will say 'Open' but obviously there is nothing to link it to a specific incident.

 

[neileg]

Not sure if you can automate this then. If the case status is 'Closed' then you know that all incidents are closed. You can create a query that joins the customer table and the incident table and select all customers where the case status is closed and then update the case status field in the incident table to Closed. I assume that there can be more than one open incident for a customer so you can't do that for Open customers. If you can only have one, then you can select the customers that are Open and update the most recent case. Otherwise, you'll need to do this manually.

I don't know what case level signifies, so can't help on that.

 

[Len Boorman]

Just offering a pen'th

Moving data from one table to another is a disaster waiting to happen. I am currently dealing with exactly that situation, It is a nightmare.

Customers and Incidents says to me

Customer can have many incidents

One of the attributes of Incident is Status. (Open/Closed/ whatever)

That means that every incident is linked to acustomer and the status of every incident for every customer if therefore known

Start from that point I suggest

Outcomes/lessons learned are probably additional attributes of Incident. Whenether or not you have a set of Outcomes/lessons that you want to use as a control list is another matter.

Do avoid moving data though. It really should not be necessary.

Apologies if I have not interpreted up your problem correctly

L

 

[neileg]

I agree with you Len. I think the OP knows the current structure is wrong and intends to correct this. I believe the issue is about migrating from the old structure to the new one. At least that's where I'm going!

 

[ibbledibble]

Thanks guys for the advice.

After due consideration I think I'll start again. I'll put the new fields into the incidents table and start using them from the start of the month to tie in with our reporting needs. I can get our users to go back through their open cases and migrate these manually. When they are done, I'll remove the old fields and learn a lesson for next time.

Neileg, to go back to what you said about the message:

You could use DCount:

=if(DCount("[CaseStatus]","MyTable","[CaseStatus]='Open')>0, "This customer has an unresoved incident","")

I presume that "MyTable" = "Incidents"? How do I put DCount into the programming? I'm a beginner btw!

 

[neileg]

After due consideration I think I'll start again. I'll put the new fields into the incidents table and start using them from the start of the month to tie in with our reporting needs. I can get our users to go back through their open cases and migrate these manually. When they are done, I'll remove the old fields and learn a lesson for next time.

Don't beat yourself up. We all learn, everyday!

I presume that "MyTable" = "Incidents"? How do I put DCount into the programming? I'm a beginner btw!

You would use that as the datasource for the control on the form. You could also use it as the basis for a calculated field in the query on which the form is based, but I can't see any advantage in doing that.

 

[gemma-the-husky]

if you have one client linked to several cases, with the case status held on the client record, then effectively the case status may/will not be correct for clients with multiple cases

by all means add the status field to the cases table, but then you need to decide how to set it - you could set it to the active status in the client table, but this will mean that for some cases the wrong status will be shown, which you will have to try and resolve manually.

you may be able to, say, update statuses to closed for customers with multiple cases, other than the latest case, which may suffice

you also need to modify your code/queries/forms/reports etc to derive the status information from the cases table, rather than from the customer table

 

[Rabbie]

The Case status must be held in the Case record along with other details of the Case. The customer record should only contain direct information about the customer.