expression to look for different category based on first string of one field (1 Viewer)

[elly.khanlar]

Hello everyone,

I'm trying to create a master table that will gather the category of materials and their cost based on the source field. Basically, the category and cost of each material will be different based on the source field. Basically, I am trying to write an expression that will look at the first string of the "source" field and will pull the required information. different tables in the below picture have the same field name, but their information is not the same which I am not sure if it requires normalizations.


I don't want to create this master table using make table query one at a time as my volume of data is so big,Trying to avoid having multiple queries, having one queries at once by building an expression that will look at category from master table, and depends on the first string of category look for different table and layer information in the master table. here is my criteria

 

[plog]

You described what you hoped to achieve in a bunch of specific steps towards an unexplained aim. What's the big picture? What's the data ultimately going to do for you?

Now here's more specific questions based on your spreadsheet:

1. Do Tables A - E exist and contain data right now? Or are they tables to be built in this database as well?

2. Will this data be added to? When 2021 comes will that data need to be added to this database?

3. Might this data be updated? After populaing the data with 2019/2020 data could there be corrections to it in a few months?

4. Can you provide a data example? Show me what you are starting with in your source table(s) and then show me exactly how that data should be put into your master table.

Again, though, big picture-- Give me some context of the overall goal of this system.

 

[elly.khanlar]

Thanks for your quick reply as I really need help in building this model.

I am trying to create a master table that I have identified. to answer your question.


1. Do Tables A - E exist and contain data right now? Or are they tables to be built in this database as well? yes, these tables exist and contain data

2. Will this data be added to? When 2021 comes will that data need to be added to this database? no, this is a data for budgeting purpose, basically, I am using current year data and next year estimate to build the budget for 2020.

3. Might this data be updated? After populating the data with 2019/2020 data could there be corrections to it in a few months? yes, It will be a correction in a few weeks

4. Can you provide a data example? Show me what you are starting within your source table(s) and then show me exactly how that data should be put into your master table.

sure, please see attached. I tried to made it clear by highlighting information.

 

[plog]

You didn't give me a big picture of what you are doing. 'Build Master Table' is but a step in your organization. Presumably you will use this data to some end. What is that end?

The big specific issue I have with helping you is that you are asking to build a table that goes against database rules. And you're not breaking just a couple minor ones, your table breaks a lot of big ones. That process of setting up tables is called normalization (https://en.wikipedia.org/wiki/Database_normalization).

That means Access may not be the correct tool for this. Excel might be. It all depends on what you want to ultimately do with the data you are creating.

 

[elly.khanlar]

I am building this master table to get the cost for each material based on delivery location and origin location as the type of material will be different based on origin location. From this master table, I calculate total cost of each category for the year. I hope I was able to explain the end goal as I really need help

 

[plog]

Again, that explanation is about the table itself, not the big picture. To what end? What will you then do with this data? The goal isn't the compilation of the data itself. The data plays a part in a bigger aim.

Most likely Excel is the tool to use for this, not Access.