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

 

[xenou]

well, I'm not an expert in large sql express projects - I believe this is a 10 GB limit per database. So you can if necessary have tables in multiple databases that you link up together. It's probably do-able (you'd use the same strategy to have two 2Gb databases in Access that you link up in a third database where you run the queries).

depending on your situation you might find a standard sql installation somewhere you can use by the time you need more room. Also archiving old data can keep the database size down.

 

[Maggie Barr]

Hello All,
Well, once again, I am unable to have SQL connect to Excel or Access or vise versa. I am at a loss, and though I have searched for it extensively, and found there is potentially some magical file to download from MS (which told me to uninstall my MS Office 2019??? Obviously aborted that mission). I am unable to determine how to connect the programs. If any of you have dealt with this and could point me in the right direction, I would greatly appreciate it. In the meantime, I am going to try to build my Access DB again with only links to the txt file as previously I had imported it. Hopefully it won't slow the process too much.
Thanks again, and best wishes,
Maggie

 

[Joe4]

Other options include splitting it among multiple Access databases. You could have that just holds all the base data.
Then, you could have a second which links to all the tables in the first one, and try doing all your work in that one.
Note that linked tables do not add to the memory size of the database.

 

[xenou]

I'm not very familiar with SQL connections to Excel or Access. You can do imports to SQL server using an import wizard. This isn't so easy to explain and might require some setup to get it to work. I doubt sql server imports from Access are extremely common - I'm sure there's a way to do it. Generally speaking, you don't connect SQL server to Access or Excel. You might instead connect Excel or Access to SQL server. SQL server is supposed to be where the data IS, and Excel or Access where the data gets USED. It's a little hard to tell if you are trying to do a one time import or actually have some kind of ongoing connection with these applications.

The most flexible toolkit would be to use SSIS (SQL server integration services) but once again there's a bit of setup and I'm not sure if you have that with SQL express. I can't speak to magic files - you would have to provide a link to what you saw.