Hi, I am creating an app in Acces and I would like to add a SUMIFS equation into a table that would use another table or several other tables with 1-2 dimensions. To illustrate what I am trying to do: I have a cost in a foreign currency and I would like to automatically translate it to USD. My first issue was that in Excel I use 2 dimensions: e.g. EUR/USD, PLN/USD and the months (Jan, Feb, Mar), but I have the impression that I cannot use this in Acces, can I? Consequently, I created several other tables for each currency with different FX rates per month. Secondly, I am struggling with the formula I should use to look for the right monthly FX in several different tables. Any ideas?
Question SUMIFS in Acces with 2 dimensions?
- Thread starter Susy
- Start date
Welcome to Access, it is a whole different beast than Excel. As you mentioned you don't store data like in the same manner as you do in Excel. To help you get started in understanding that process start here:
https://en.wikipedia.org/wiki/Database_normalization
Normalization is the process of setting up your tables and fields properly in a database.
With your data, you did the wrong thing for the right reasons. You are correct you shouldn't use a different field for each currency, but you also shouldn't use a new table for each currency. Part of normalization is determining what values are data. EUR, USD, PLN, Jan, Feb, Mar are all data values, which means they should not appear in table or field names.
So instead of a bunch of tables named after currency, you would have a field called [CurrencyType] in a table and the values you would populate that field would be USD, EUR, PLN, etc.
So, my suggestion is for you to read up on normalization, give it a shot with your database, set up the Relationship Tool, take a screenshot fo it and post back here so we can help make sure you have properly set up your tables.
https://en.wikipedia.org/wiki/Database_normalization
Normalization is the process of setting up your tables and fields properly in a database.
With your data, you did the wrong thing for the right reasons. You are correct you shouldn't use a different field for each currency, but you also shouldn't use a new table for each currency. Part of normalization is determining what values are data. EUR, USD, PLN, Jan, Feb, Mar are all data values, which means they should not appear in table or field names.
So instead of a bunch of tables named after currency, you would have a field called [CurrencyType] in a table and the values you would populate that field would be USD, EUR, PLN, etc.
So, my suggestion is for you to read up on normalization, give it a shot with your database, set up the Relationship Tool, take a screenshot fo it and post back here so we can help make sure you have properly set up your tables.
Hi plog,
I read the article, but now I have even more doubts than before!
I have the impression that my main table '_Freight' is too complex (cf. print screen of relationships attached). I think I included too much information on this table. However, as this will be my input source for the reports I would like to create, I thought that I should mention everything there. Any advice?
I read the article, but now I have even more doubts than before!
I have the impression that my main table '_Freight' is too complex (cf. print screen of relationships attached). I think I included too much information on this table. However, as this will be my input source for the reports I would like to create, I thought that I should mention everything there. Any advice?Attachments
isladogs
Access MVP / VIP
- Local time
- Today, 10:01
- Joined
- Jan 14, 2017
- Messages
- 19,303
Susy
Have a look at my Currency Exchange Rate Tracker app
It may do much of what you need though you will need to obtain an API key to use it.
Have a look at my Currency Exchange Rate Tracker app
It may do much of what you need though you will need to obtain an API key to use it.
Good news is you read up on normalization, the bad news is you over did it. Tables with only one real field of data do not need to exist. So Carrier, From, Customer and To shouldn't exist.
Also, I can only see half the fields in _Frieght and I don't know what your organization does and hope to do with this database. Could you open up _Frieght in design view and take a screenshot of all the fields and their types and post it? Also, include a generic description of what it is your organization does--no database jargon, just give me an organizational overview. Last, tell me what this database is supposed to help with--light on the database jargon.
Also, I can only see half the fields in _Frieght and I don't know what your organization does and hope to do with this database. Could you open up _Frieght in design view and take a screenshot of all the fields and their types and post it? Also, include a generic description of what it is your organization does--no database jargon, just give me an organizational overview. Last, tell me what this database is supposed to help with--light on the database jargon.
Hi all,
Thanks a lot for your answers and proposals. I really appreciate your help!
@plog: Attached the requested print screen. Actually, I created the tables Carrier, From, Customer to keep control over them. I don't want people to pick up some carriers that don't exist or use different names for carriers. For what reason do you recommend deleting them?
I am creating this app for several purposes (1) uniformizing the input data (2) to automatically run the reports I want to. E.g. the cost per box per supplier per 100 km or restated reports without including pallet returns, etc. Now, I am using Excel, but I am losing a lot of time...
@isadogs: I used the underscore to see the table at the beginning. Is it so bad to use it?
@Pat Hartman: If I am the one who wants to update the different tables on my own, would I still need this mini app?
Thanks a lot for your answers and proposals. I really appreciate your help!
@plog: Attached the requested print screen. Actually, I created the tables Carrier, From, Customer to keep control over them. I don't want people to pick up some carriers that don't exist or use different names for carriers. For what reason do you recommend deleting them?
I am creating this app for several purposes (1) uniformizing the input data (2) to automatically run the reports I want to. E.g. the cost per box per supplier per 100 km or restated reports without including pallet returns, etc. Now, I am using Excel, but I am losing a lot of time...
@isadogs: I used the underscore to see the table at the beginning. Is it so bad to use it?
@Pat Hartman: If I am the one who wants to update the different tables on my own, would I still need this mini app?
Attachments
Similar threads
