Fixing Mistakes

[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.

 

[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.

 

[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.

 

[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!

 

[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!