Solved Need help reformatting database structure to get rid of calculated field (1 Viewer)

[gojets1721]

I've attached an example file, which hopefully will explain this better. Essentially, I'm using a calculated field to capture the data from over a multitude of columns. For each entry, there will always only be data in one of these columns and the rest will be blank. By using a calculated field, it pulls all the data across all the columns into one concise column. But this method is inherently flawed and the calculated field can't be edited in a form. I inherited this DB and it seems like this was a band-aid for poor DB design. In my example, you'll see that the subcategory field can't be edited.

The reason its likely designed poorly is because our IT team imports the data in, not me. They pull it from an online form and just paste it right into the table. Unfortunately, they won't change how they import it in or change their workflows.

Would anyone be willing to take a stab at how to construct the DB around this issue to basically allow the subcategories to be edited in the form? I've run outta ideas. Thanks

 

[plog]

Normalize it properly then move the data.

tblComplaints should not be a table you use--not for forms, not for queries, not for reports. It should only be used by IT to paste data into since that part of the process is immutable.

So, after they paste their data you trigger an event that moves new records from tblComplaints into your properly structured database. Here's how I see that so far:

tblCustomers - CustomerID, CustomerFirstName, CustomerLastName.
tblComplaintsProduction - ComplaintID, CustomerID, ComplaintDate, Category, SubCategory

Also, make sure you use the right datatypes (CustomerID=number, ComplaintDate=Date)

 

[mike60smart]

I've attached an example file, which hopefully will explain this better. Essentially, I'm using a calculated field to capture the data from over a multitude of columns. For each entry, there will always only be data in one of these columns and the rest will be blank. By using a calculated field, it pulls all the data across all the columns into one concise column. But this method is inherently flawed and the calculated field can't be edited in a form. I inherited this DB and it seems like this was a band-aid for poor DB design. In my example, you'll see that the subcategory field can't be edited.

The reason its likely designed poorly is because our IT team imports the data in, not me. They pull it from an online form and just paste it right into the table. Unfortunately, they won't change how they import it in or change their workflows.

Would anyone be willing to take a stab at how to construct the DB around this issue to basically allow the subcategories to be edited in the form? I've run outta ideas. Thanks

Hi
What are you trying to calculate when all of the Categories are Text fields?

 

[Pat Hartman]

I've seen this database before. What happened the last time we offered suggestions on how to resolve the problem?
If this data is down loaded from the web, why do you want to update it? Shouldn't you just be doing the reporting?
Essentially all you need are the Category and the calculated SubCategory for your reports. Although the calculated column is required due to the poor table design, it seems to solve the problem and since this is not a database that anyone should update. I don't think I object to it.

So, I guess it all comes down to - WHY are you modifying the data?

 

[gojets1721]

I've seen this database before. What happened the last time we offered suggestions on how to resolve the problem?
If this data is down loaded from the web, why do you want to update it? Shouldn't you just be doing the reporting?
Essentially all you need are the Category and the calculated SubCategory for your reports. Although the calculated column is required due to the poor table design, it seems to solve the problem and since this is not a database that anyone should update. I don't think I object to it.

So, I guess it all comes down to - WHY are you modifying the data?

Hi Pat. So these complaints are submitted by staff and they sometimes select the wrong/incorrect sub category when using the online form. My actual DB has dozens of categories and subcategories. It's not as simplistic as the example but the example still shows the core of the problem. Being able to change the subcategory in the form would be very useful. The current band aid of using a calculated format (obviously) does not allow for it to be edited in the form. I'm looking for guidance for how to better construct the database to allow for editing of the subcategory in a form while still accounting for the odd uploading of the data

 

[gojets1721]

Hi
What are you trying to calculate when all of the Categories are Text fields?

I'm not calculating anything. It's a poor solution to put all the data in the various subcategory columns into one column. Hence why I'm here asking if anyone can construct it better.

 

[gojets1721]

Normalize it properly then move the data.

tblComplaints should not be a table you use--not for forms, not for queries, not for reports. It should only be used by IT to paste data into since that part of the process is immutable.

So, after they paste their data you trigger an event that moves new records from tblComplaints into your properly structured database. Here's how I see that so far:

tblCustomers - CustomerID, CustomerFirstName, CustomerLastName.
tblComplaintsProduction - ComplaintID, CustomerID, ComplaintDate, Category, SubCategory

Also, make sure you use the right datatypes (CustomerID=number, ComplaintDate=Date)

Could you possibly implement that in the example I provided? I'm really not sure how to implement the production DB nor the trigger

 

[plog]

No, perhaps someone else will, but this issue is not easy enough to code in 15 minutes and not difficult enough to make it interesting for me to spend more than that much time on.

I gave you the outline and I from what I've seen in this and other posts of yours, you have the skills to fill in the specifics. Build a properly normalized structure for your data; paste in the table and sample data from the data you get from IT; build a system to move the data from that table to the properly structured ones. Most likely this will be a series of UPDATE and APPEND queries, with perhaps a bit of VBA to do some light lifting in between. Once you have that process that moves pasted data to your tables you can then find a way to trigger it--Scheduled Task in MS Windows, a button users click, a form with a timer, etc.

 

[Pat Hartman]

I went back to March to find your earlier post on this topic where you were given the advice to NORMALIZE the data when you import it.

https://www.access-programmers.co.uk/forums/threads/need-a-solution-to-un-editable-calculated-field-in-form.316967/page-2#post-1756783

There is NO need to modify the form. You just need to normalize the data when you import it. Your user should have no say in how the data is stored. Then you can do the same thing the web page does and have a cascading combo. You pick a category and that filters the subcategory combo to only contain relevant items.

And now plog has given you the same advice yet again.

Rather than appending the raw data, link to the input file. Create a query that "calculates" the single field and then import that. Once the data is properly normalized, your problem goes away. You can use cascading combos to control the cat and sub cat fields and they will be updateable.

 

[gojets1721]

I went back to March to find your earlier post on this topic where you were given the advice to NORMALIZE the data when you import it.



And now plog has given you the same advice yet again.

Rather than appending the raw data, link to the input file. Create a query that "calculates" the single field and then import that. Once the data is properly normalized, your problem goes away. You can use cascading combos to control the cat and sub cat fields and they will be updateable.

Hi Pat, thank you for the advice. Similar to my previous comment and along with my post back in March, I don't know how to do that. Would you be willing to show it in the example or link some guides that walk it through?

 

[Gasman]

With a picture being worth a thousand words.
https://www.youtube.com/results?search_query=cascading+combos+access

or just the words
https://www.google.com/search?q=cas...i30l4j69i60.8153j0j4&sourceid=chrome&ie=UTF-8

 

[Pat Hartman]

I built the append query because the conversion is two-step since you are going from text to a number. I also modified the form to use combos.

 

[gojets1721]

This worked!! Thanks Pat!!!

 

[Pat Hartman]

You're welcome The important thing though is that you understand how it works so you can do it again on some other import if you need to.