Fixing Mistakes (1 Viewer)

[KristenD]

After completing all aspects of my database, I moved on to creating queries/reports/forms for reporting purposes for my executive team. In creating queries/reports I discovered I made an error in using a combo box on a form for data entry where I should have used a table and based the form on a query for the entry.

My question is can I go ahead and add the table and redo the form without messing up the data that is currently in the tables?

What I am doing is adding a table with a list of state agencies and on the form I used a combo box and stored the data in the table and I just want to make sure that the info that is already there won't get messed up.

Thank you!
Kristen

 

[Simon_MT]

Kristen, We have all made mistakes. To me, it would be better to make the handling of State Agencies consistent and all you need to do is transfer the information to a Foreign Table and re-state values on the table using the Foreign key. This is how I would approach your problem. Extract the information from the combibox using Grouping / Distinct. I don't now if you use IDs or Shortkeys (OOC = Oil on Canvas) Append to a new table: ID Desc New Shortkey Create the Shortkey if required otherwise Link the new Table Desc with your existing table and Set the field to either the ID or Shortkey. Create a query or lookup for the Form. Simon

 

[KristenD]

Thank you!!

I use ID's. If I do that, it won't mess up the information that is already in the table?

Because all I am doing is just creating that relationship between the new table and current table right?

The form shouldn't have anything to do with anything correct since it is just used for easy data entry right?

 

[Simon_MT]

You need to supplant the IDs into the existing table by first Linking the info to the State Agency, that way the information is consistent.

Simon

 

[KristenD]

That's what I thought, but for some reason was having a hard time trying to figure it out.

Thank you!

 

[KristenD]

I think I'm doing it wrong because it's not linking like it should.

I have the one-to-many-relationship, but it's not recognizing the information in the original table.

:banghead:

 

[Simon_MT]

That is probably because the information in the field is in its original input state that does not relate to the State Agency table.

Simon

 

[jdraw]

I agree with Simon, but an example or two may help readers to understand your issue.

 

[Pat Hartman]

AgencyID  AgencyName
1          DOD
2          SEC
3          FBI

Are you saying that you stored "DOD" rather than "1" in your table? Did you use a Lookup on the table itself? If you did, chances are good that the numeric ID was stored rather than the text string. Once you remove the table level lookup, you will be able to see what was ACTUALLY stored (this is one of the big problems with table level lookups. They hide important information from you). If the text is stored and you want to fix it to store the ID, add a new colum to the table. Create an update query that joins the text field to the text field in your new lookup table. Change the query to update and select the new AgencyID field. Update it to lktAgency.AgencyID. Once you validate that the update was done correctly, remove the text field since it is no longer needed.

 

[KristenD]

In the original set up was just a combo box in the form that was not tied to any table. When I came back to write queries and set up search forms, it would not pull the information like I had wanted it to. I realized I set it up incorrectly and should have had a table to link and store information in. So I set up the additional table:

1 Maryland
2 Virginia
3 Delaware

And tried to link that to the information that is currently stored in the LicensingAgency from the combo box that I created on the form. I'm not sure if I've done that correctly or not or if the information is still pulling from the combo box that I originally set up. I did go in and delete the original combo box and set it to look up the table and it seems to have the same information in there.

 

[Pat Hartman]

What data value is currently stored in the main table (not the lookup table). Is it 1 or is it Maryland or is it a combination?

 

[KristenD]

Right now "Maryland" is currently stored in there from the combo box I put on the form. I am unsure how to link the data that is currently stored in there to the look up table I created.

 

[Pat Hartman]

Please go back and reread my post #9. I told you how to solve this problem.

If the text is stored and you want to fix it to store the ID, add a new colum to the table. Create an update query that joins the text field to the text field in your new lookup table. Change the query to update and select the new AgencyID field. Update it to lktAgency.AgencyID. Once you validate that the update was done correctly, remove the text field since it is no longer needed.

 

[KristenD]

I've been working on the update query, however, it is keeps saying it is updating 0 rows.

here is update query in SQL:

UPDATE tblLicenseAgency INNER JOIN tblLicensing ON tblLicenseAgency.AgencyID = tblLicensing.LicensingAgencyFK SET tblLicensing.LicensingAgency = [tblLicenseAgency].[LicensingAgency];

I had done an update previously doing the same thing and it worked before but now with these tables it is not working.

Please help!

Thanks!

 

[Pat Hartman]

You can't join on the ID field, you need to join on the text field. Then you update the ID field. I don't know if I mentioned this earlier but ALWAYS copy the table so you have a backup in case a bulk update like this goes wrong.

 

[KristenD]

Sorry, I was away for the past week and a half unexpectedly. I will do the update on the text field. I always do it on a back up database before actually doing it on the live version that way if I mess up, nothing is affected.

Thank you!