Syntax problem in Query column builder, please help.

[Maggie Barr]

Hello, and thank you in advance if you can help,
I am trying to create a new column in a query based on two fields, PROTOCOL TYPE & DURATION MINUTES. After much online searching trying to find help with syntax and trying multiple types of formulas, I am at a loss. In my most recent attempt, in the "builder" for the new column I am putting:
ADJUSTED_DURATION_MIN: If PROTOCOL TYPE = "Historical" Then 1
ElseIf PROTOCOL TYPE = "Incidental" Then 1
ElseIf DURATION MINUTES >240 Then 240
Else DURATION MINUTES

Below is a description of what I am trying to do:
The column I want to create is ADJUSTED_DURATION_MIN
Then if PROTOCOL TYPE = "Historical" OR "Incidental" make ADJUSTED_DURATION_MIN 1
As well, If DURATION MINUTES is greater than 240 make ADJUSTED_DURATION_MIN 240
Otherwise make ADJUSTED_DURATION_MIN = DURATION MINUTES

I am new to Access and trying to do as much in Access because doing it all in Power Query, which is where I will end of for analyses purposes, was choking the program and taking WAY to long to perform tasks.
Any advice would be appreciated!
Thank you,
Maggie

 

[Joe4]

Just use a nested IIF function, i.e.

[COLOR=#333333]ADJUSTED_DURATION_MIN: IIF([[/COLOR][COLOR=#333333]PROTOCOL TYPE] = "Historical" OR [/COLOR][COLOR=#333333][[/COLOR][COLOR=#333333]PROTOCOL TYPE] = [/COLOR][COLOR=#333333]"Incidental",1,IIF([[/COLOR][COLOR=#333333]DURATION MINUTES] > 240,240,[[/COLOR][COLOR=#333333]DURATION MINUTES]))[/COLOR]

 

[Maggie Barr]

Joe4,
Thank you so much for getting back to me, and I do think your formula will work fine, but I was just looking into an issue I discovered. When I brought the dataset into access from a txt file, I had put the duration minutes (which has blanks) as short text because if I didn't, there were numerous import errors as a result. I am now going into the original data sheet in design view and trying to change it to number, but it won't let me ("the size of one or more fields has been changed to a shorter size, If data is lost, validation rules may be violated as a result", then it says it can't change data type, not enough disk space), so I then tried long number, and it is telling me that that conversion isn't compatible with an earlier version of access. I am running MS Access 2019, and it is pretty quirky. Do you have any advice as to how I can simply change the data type to number? It seems that it shouldn't be that difficult?
Thank you again for your help,
Maggie

 

[Maggie Barr]

Joe4,
Thank you for your help. I re-imported the data, and kept the field as integer, and it worked without error this time, so your formula, as suspected, worked perfectly.
It is odd how I am working in Access "2019" but it says Access 2007 - 2016 file format. I frequently have issues with attempting things and it telling me that it won't work in an earlier version of Access, but bring a novice to the program, I am unable to determine if it is me or something with the program. Regardless, you formula worked perfectly.
Thank you,
Maggie

 

[Maggie Barr]

Dear Joe4,
My word, I hate to be annoying, but after I ran the formula, I tried to create a table of my query. It wouldn't let me, I think, because my database is at 1.99 Gigabytes and Access has a cap of 2. I first did everything in excel when the data set was young, then needed to try to learn Power Query because of the data set growing, then Access because I was asking too much of PQ and it was excruciatingly slow, and now it appears I have to head to SQL. I have tried unsuccessfully to play in SQL a bit, but if that is all I am left with as an option, go there I must. I am not connecting to any gigantic online databases, just my own, along with multiple lookup tables for queries and merges, and column creations and modifications to enable me to run tests with the data. It seems the SQL program is so heavily oriented to huge networks and online databases that it is not very user friendly to someone who has everything on a desktop. I just thought I would ask your opinion on the next step when a database outgrows Access. Any advice would be greatly appreciated.
Sincerely,
Maggie

 

[Joe4]

Note that Access databases "bloat" overtime without routine maintenance, especially if you are deleting lots of data. Deleting the data, in and of itself, does NOT reduce the size of the database; you must run a "Compact and Repair" on the database to purge all deleted data and reduce the size. It is a good idea to do that regularly, to keep it running "as lean as possible". If you run that now, does the size of the database decrease significantly?

As you outgrow Access, you can still use Access as the front-end, just store the data somewhere else (and "link" to it). In my work, typically we use SQL to store large databases, and then use Access as the front-end to access and work with that data. However, as you know, SQL is not free. However, you may be able to use Microsoft's free MySQL. See: https://dev.mysql.com/doc/connector...examples-tools-with-access-linked-tables.html

 

[welshgasman]

Might have to move to SQL Server?
https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

 

[Maggie Barr]

Joe4 & welshgasman,
Thank you both for your input. Unfortunately, compact and repair only gave me .14 gigs of space back, not enough to do what I need. I have a BIG learning curve ahead of me for the SQL program. Time for some serious tutorials.
I am running Microsoft SQL Server Management Studio 18 which is, I guess, and add on update to Microsoft SQL Server 2017, so both are in my system running together, permitting that I even configured the install and setup right.
The problem I am having, other than that I know NOTHING about SQL, is that when I try to find either Access or Excel files through the SQL Server Import and Export Wizard, there are no MS Office programs visible. I had read online that there was some sort of download for MS Office 2016 that enabled access from SQL to MS Office programs, but I am running MS Office 2019 Pro Plus, so I have no idea if that will work for me. My best option at this point, I think, is to use the original file of my data which is a txt file, bringing it in as a flat file, and convert all my excel lookup tables to txt files so I can bring them in. Not a huge deal, but a slight hassle as potential updates occur to the lookup tables. I had previously loaded my data (not lookups) into SQL just because someone had recommended that linking to it there would help Power Query function better than linking to the txt file, but once that was done, I was unable to access the SQL DB from Power Query. Probably the same issue with the MS Office programs not being available to SQL on my system for imports, the fix for this eludes me though.
Anyway, thank you for your time and input. It is just a lot of technological jumping/learning from Excel, to Power Query, to Access, to SQL as a matter of necessity over a couple of months, my head is spinning a bit, but I will get there, and I always greatly appreciate the online assistance and support.
Best Wishes,
Maggie

 

[xenou]

Do you have full blown sql server or just sql server express? Note that the latter has a 10Gb limit (I think - I could be wrong). So you have to think carefully about whether sql server will solve your storage problems or not if you expect the data size to keep growing.

 

[Maggie Barr]

xenou,
Ha, I am actually laughing because, obviously I am WAY out of my league here, I just had to run a query to find out what I have for SQL. "Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )". I do have the express, so I checked, and you are correct with the 10Gb limit. I am not near that now, but it could be possible at some point. Funny, I just got all my data in, converted all my excel lookups to txt to bring them in, and was refining the column numbers, order etc. before attempting to create relationships and start trying to create some custom columns. Thanks for the heads up on this. I may continue for a bit and pray that I don't reach the limit. It would likely be a couple of years in the project before the data grows that much, if it ever reaches it. Do you have any recommendations for any other programs to handle and manipulate large data sets (hopefully somewhat user friendly)?
Again, thank you for the warning,
Maggie