Question SUMIFS in Acces with 2 dimensions? (1 Viewer)

Susy

New member
Local time
Today, 15:39
Joined
Dec 19, 2019
Messages
25
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?
 

plog

Banishment Pending
Local time
Today, 09:39
Joined
May 11, 2011
Messages
11,613
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.
 

Susy

New member
Local time
Today, 15:39
Joined
Dec 19, 2019
Messages
25
Great, thanks a lot for your help! I will start with that and come back to you! :)
 

Susy

New member
Local time
Today, 15:39
Joined
Dec 19, 2019
Messages
25
Hi plog,
I read the article, but now I have even more doubts than before! :D 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

  • Capture.JPG
    Capture.JPG
    75.8 KB · Views: 199

Susy

New member
Local time
Today, 15:39
Joined
Dec 19, 2019
Messages
25
Hi isladogs, thanks for your proposal. Unfortunately, I have my own rates that I have to use. But thanks for your help!
 

isladogs

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
No problem. The table structure may still be useful however.
 

plog

Banishment Pending
Local time
Today, 09:39
Joined
May 11, 2011
Messages
11,613
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.
 

isladogs

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
Having just looked at your diagram I agree with plog.
Also table names should not start with an underscore. Use Freight not _Freight.
Make sure each table has a primary key field
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:39
Joined
Feb 19, 2002
Messages
42,984
And I disagree. It is more likely that the four single field tables need additional attributes. If you want to limit the values of Freight to a specific set of data, you of course could create a value list on the table but in my OPINION, I believe it is better to create a table. Why? I don't want to be in the business of modify a table definition if a user wants to add/remove a Freight option. What happens if FedEx used to be valid and is used in thousands of reccords but you don't want to use FecEx any more? How are you going to prevent people from picking FedEx if you don't remove it from the value list? You can't!!! That is why these lists need to be tables. Once FedEx has been assigned to thousands of rows, you can't remove it from the value list because the data base engine will fight you. So the best option is a table with at least one other field which is named AvailableYN or whatever. Your code then uses the flag to determine whether to allow an item to be picked based on the value of AvaiableYN.

Last time we had this discussion, I uploaded a mini-app that I incorporate in all my applications since once you solve this problem, you certainly don't have to solve it again. I solved the problem back in the 80's with COBOL and IMS. After several other iterations, it ended up as Access. In some apps the tables are ACE. In others they are whatever RDBMS the main set of tables are. The mini-app includes tables/forms/reports/queries that allow users to manage these little lookup tables themselves so you don't have to do it. Is job security really that important to you that you actually want people to call you to modify a table to add a new freight option to a table?

The mini-app has actually changed very little over the years. The current version includes the option to specify what level of user can update what lookup tables. Some tables can realistically only be modified by a programmer since code is required somewhere in the app to do something. But most tables can be updated either by anyone or by a supervisor. The app doesn't implement the security because you will all have different security measures in place (or not). just put your initial validation in the Form's OnDirty event. Check there if the person attempting to modify the data is authorized.

PM me if you want it and can't find it.
 

Susy

New member
Local time
Today, 15:39
Joined
Dec 19, 2019
Messages
25
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?
 

Attachments

  • Freight.JPG
    Freight.JPG
    54.8 KB · Views: 402

Users who are viewing this thread

Top Bottom