I am hoping that someone could help me convert a spreadsheet of formulas that rely on a rigidly limited data set, to a MS Access database with a data set that can be expanded.
I play game that requires the use of regenerating resources to craft items. I have a spreadsheet with formulas that can calculate the minimum amount of time needed to replenish the resources used to craft an individual item; and can also calculate the amount of time needed to replenish after crafting multiple items when two or more items are drawing from the same resource.
As it is currently written, the spreadsheet works to calculate for three items. If I want to see the effects of crafting more items, I would have to re-write all of the formulas. The formulas for each item (labeled 'New' in the spreadsheet) not only deal with the numbers pertaining to that one item and it's use of resources, but the formulas take in to account how much resource is being used by other items and how frequently. To add a fourth item would mean re-writing all of the 'New' formulas to include calculations for all four items. They would all have to be re-written again to add a fifth item. And re-written again to add a sixth item.
Looking at the formula for the 'New' field is slightly less confusing than would first seem. All the if statements in the formula are there simply to prevent REF errors caused by dividing by 0, due to the presence of a zero somewhere in the data.
I have tried adapting my spreadsheet in to a MS Access database, but I can not get past the first steps of creating the pertinent tables. In my spreadsheet, each resource is on an individual row. In a database... I tried creating a ResourceTable but could not figure out if I should list the resources in one column and the ResPerHr in a second column,or if I should list each resource as it's own column with the ResPerHr being the only value held for that field. Then I think there would be an ItemTable that would have a field for the ItemName and then fields for each resource, and each row would have the crafting information for an individual item. And then I do not have idea #1 about how to create ... not a table, right? because to perform all the calculations, I would need a query, right???....how to create a query that would allow for the selection of certain items from the ItemTable (1 item, or 2 items, or 5, or 10), and then be able to retrieve the pertinent resource information from the ResourceTable and then use it to calculate the 'Time', 'Cur' and 'New' fields that are in my spreadsheet; not just for one individual item, but for all the items in the ItemQuery (however many that may be).
P.S. I am using Microsoft Office 2003
P.P.S. I am sorry if this request is completely unintelligible. There are moments when I have trouble understanding what my spreadsheet does, and I made it.
I play game that requires the use of regenerating resources to craft items. I have a spreadsheet with formulas that can calculate the minimum amount of time needed to replenish the resources used to craft an individual item; and can also calculate the amount of time needed to replenish after crafting multiple items when two or more items are drawing from the same resource.
As it is currently written, the spreadsheet works to calculate for three items. If I want to see the effects of crafting more items, I would have to re-write all of the formulas. The formulas for each item (labeled 'New' in the spreadsheet) not only deal with the numbers pertaining to that one item and it's use of resources, but the formulas take in to account how much resource is being used by other items and how frequently. To add a fourth item would mean re-writing all of the 'New' formulas to include calculations for all four items. They would all have to be re-written again to add a fifth item. And re-written again to add a sixth item.
Looking at the formula for the 'New' field is slightly less confusing than would first seem. All the if statements in the formula are there simply to prevent REF errors caused by dividing by 0, due to the presence of a zero somewhere in the data.
I have tried adapting my spreadsheet in to a MS Access database, but I can not get past the first steps of creating the pertinent tables. In my spreadsheet, each resource is on an individual row. In a database... I tried creating a ResourceTable but could not figure out if I should list the resources in one column and the ResPerHr in a second column,or if I should list each resource as it's own column with the ResPerHr being the only value held for that field. Then I think there would be an ItemTable that would have a field for the ItemName and then fields for each resource, and each row would have the crafting information for an individual item. And then I do not have idea #1 about how to create ... not a table, right? because to perform all the calculations, I would need a query, right???....how to create a query that would allow for the selection of certain items from the ItemTable (1 item, or 2 items, or 5, or 10), and then be able to retrieve the pertinent resource information from the ResourceTable and then use it to calculate the 'Time', 'Cur' and 'New' fields that are in my spreadsheet; not just for one individual item, but for all the items in the ItemQuery (however many that may be).
P.S. I am using Microsoft Office 2003
P.P.S. I am sorry if this request is completely unintelligible. There are moments when I have trouble understanding what my spreadsheet does, and I made it.
