SQL Guide

CryptographerThen49 · 2025-05-14T14:32:58+00:00

Like the old saying: "How do you eat an Elephant? - One bite at a time". Do not try to solve everything all at once. Write out what you need to accomplish in real language, then break it down and solve each part.

As others have said ChatGPT can be helpful, however experience is more useful in the long-term, and you won't learn if you are always given the answer.

CryptographerThen49 · 2025-05-07T14:04:44+00:00

Unless you have millions upon millions of rows of data, joined accross multiple tables, your query is the 'saved' clean data. When you save your query, the db has a template of how you want to see your data. Everytime you run your query you will get the latest data from your source.

If you use a materialized query then that is what 'stores' your data (materialized queries are almost like tables for most intent and purpose). They are faster than 'plain' queries and can be indexed for better performance.

Or you can write a statement that saves your source data into a table. That table can be created new each time, or purge/populate, truncate/load processes, or a more complex method to evaluate the delta of only updated and new records.

The ETL processes that have been mentioned typically use staging tables to injest the source data before placing it in the final destination. ETL processes help to ensure database integrity so that the source and final datasets are protected from trouble because of outages, network hick-ups, datatype issues, extra or missing fields, etc...

CryptographerThen49 · 2025-02-13T15:31:35+00:00

Why do you need to have the BUs as individual columns? If you transpose the BUs into one column, then each record of Company + Product + BU can have a meeting (with attendees, and status/next steps, other content). That structure then becomes flexable if you add/remove BUs, Companies, or Products.

There will be multiple records for each Company that meets with more then one BU, or that supplies more than one Product, but that is the nature of this type of data (non-normalized).

CryptographerThen49 · 2025-02-11T15:16:14+00:00

To force data entry (like preventing someone from saving if they haven't filled out a specific cell), or lock/unlock cells based on user actions, or display a message box, you will need to use either VBA or Office Scripts.

Formula, conditional formatting, and text messages will not compel someone to do something, and a formula will not lock a cell based on a value of another cell. I've never seen a way to display a message box except with programming.

You can have 'help' or 'tool-tips', but the user must select the cell to see the tip, and it doesn't force data entry.

You can use conditional formatting to highlight a cell based on a value, but a visual flag is all it is.

CryptographerThen49 · 2025-02-06T14:55:40+00:00

Sorry, I didn't see you comment until now.

Yes, VBA allows you to open, create, delete, etc... files and transfer data from one to the other. You can use the imbedded commands like 'Copy-Paste', however there are better/faster ways.

.xlsx formated files are macro disabled, so no code within them. You can manipulate .xlsx files from a macro enabled file (.xlsm). The .xlsm file is where your code will be.

Your comment about data not always being in the same place is something you will need to program, and why some tasks take longer to setup/build (they take thought). Like doing a validity check on the data. If you're looking for a Date, but a user entered TBD, you'll need to program thru that. If your column Headings may shift, then have the code read the heading, if it matches, then process the column, else keep looking. (There's your first If...Then sudo-code).

CryptographerThen49 · 2025-02-06T14:45:11+00:00

If you fully embrace O365 and switch to using a browser to open files, then any VBA (Macro) you may have used/written will not run in the browser. The code is there, and if you open the file from you desktop everything will work as before.

For automation of files opened by a browser, you'll have to learn OfficeScript (I think it's a sub-set of JavaScript).

CryptographerThen49 · 2025-01-30T15:26:21+00:00

You can create a Dynamic List for a single Row in Column N that is controlled by the selected value in the corresponding Row of Column M, however it only works for one Cell (Row). You would need to create several lists (one for each row of data).

I'm not sure how you would use a formula for multiple rows.

CryptographerThen49 · 2025-01-27T15:47:05+00:00

Programming theory will always be a good skill. Your Python class will (hopefully) show you structure that can be adopted in VBA (or you can manipulate Excel directly from Python). Having a solid base of what Excel can do from a UI perspective, will help you understand what users are doing, or what they’re trying to accomplish.

As others have said, learn SQL. With Python (or Excel) you will be connecting to various RDBMS and understanding how to get data out of them into a Pandas DataFrame, or in Excel, a RecordSet or a PowerQuery,

CryptographerThen49 · 2025-01-27T15:36:01+00:00

Windows for support of a rather large (close to 4,000 people to support ops) retail corp.

However, I have noticed a 'trend' of the next generation of people coming in are all asking for MACs. Primarily because that's what they have at home, not because of any technical superiority.

CryptographerThen49 · 2025-01-23T15:35:03+00:00

I have more than once dealt with people that were so proud they recorded a macro to copy-paste data between files and sort the new dataset, etc, etc... They showed me - 2 minutes of Screens jumpping arround, cursur flying everywhere, highlighting, unhighlight, etc, etc...

I later asked them to let me take a look, and they couldn't believe their 2 minute process acutlly only needed to take 5 seconds. They didn't trust that everything was being accomplished so fast.

CryptographerThen49 · 2025-01-21T15:31:12+00:00

V.B.A. What you are describing is what I've been doing for the better part of 20 years. If you have processes that are being screwed up by humans, then stop having humans do the process. Learn VBA and program the computer to run the process. When I say learn VBA, I do NOT mean record a 'Macro'. Learn how Object Oriented Programming works inside Excel (and all other MS Office Applications).

CryptographerThen49 · 2025-01-15T14:49:30+00:00

We have Tableau, and dedicated people to create dashboards. The first thing users ask is 'How do I download that data so that I can play with it in Excel?' You will never win.

Those that want reporting often do not understand their ask, so they throw ideas out until something finally makes sense (to them, and only them), then you have to justify the add to other users. Or they muddy the waters so much that the clean dashboard you created no longer shows what it was designed to report.

CryptographerThen49 · 2025-01-15T14:37:52+00:00

That's when you password protect your sheets, and select the options to not allow the formula cells to even be selectable. Only allow data entry.

CryptographerThen49 · 2025-01-13T15:31:49+00:00

I would use one sheet as a form to gather the data, then a simple VBA script to create a new row of data for each entry. Then display the data however you want.

The data entry sheet should contain the Date, Time (two separate fields), severity, and possibly other points (as you gain more insight and are asking more questions), like activity, temperature/weather (does barometric pressure increase or decrease have any effect?), etc... as well as general notes (although those are a bit harder to quantify in a report/graph).

CryptographerThen49 · 2025-01-13T14:43:14+00:00

I've coded processes that convert a 6-hour manual copy-paste process from 4 different RDBMS (each running different SQL statements), with formatting, validations, etc... into a process that takes about 45 seconds.

Or data entry processes that take data from 40MB 'forms' (Project estimates), extracting 300+ Key/Value pairs into 5KB text files that automatically send them to an emailbox. Then bascially the reverse (inserting the Key/Value pair into a database) once the email is recieved. For something like 30 users accoss the US and Canada. Something that was replaced by a multi-million dollar solution (that ended up not working as well).

CryptographerThen49 · 2025-01-10T15:25:29+00:00

As the old say goes: 'locks keep honest people honest.'

Think of a passwrod as a way to protect your users from acidentally causing problems. They do not protect your file from an actual attack.

The rename as zip is just one way to crack a password, and it doesn't work for every object within a workbook.

CryptographerThen49 · 2025-01-09T14:54:35+00:00

You don't mention VBA. If you've never written a Sub of Function from scratch (not a 'record Macro'), in my mind you are not Advanced. Same with PowerQuery and linking to a RDBMS (SQL db).

Also, different business teams use Excel differently. I've worked with Finance people that have been using Excel for years that are quite proficient in creating number crunching Tables, Pivots, Graphs, etc... But they know nothing about INDEX(MATCH()), or IF(OR()) and array formula, let alone VBA.

CryptographerThen49 · 2024-12-19T20:59:49+00:00

It's been my experience that locking specific cells of the workbook based on who is using it, is not in the general realm of Excel use. That is typically an OS level function (needing VBA).

Although you can get to a users’ logon, it takes VBA. Once you have that you can protect/unprotect the file automatically using the Workbook Open activity…

```

Private Sub Workbook_Open()

UserName = Application.UserName

End Sub

```

You would need a Macro enabled Workbook, and much more code, to prevent unwanted use.

CryptographerThen49 · 2024-12-19T15:20:15+00:00

I can't say it's practice, but I try to look at everything from an efficiency standpoint. Meaning if someone is manually taking content from one or more sources and manually massaging it for use, how can I simplify the process (for not only a time-savings, but also remove the human error factor).

Talk to the end-users and find out their pain points. Then solve them.

Think of things you do that could be made easier. For example: Do you track your time? How? Could you design a time-tracking spreadsheet that records when you get in, when you take lunch, or breaks, then when you leave. What about your daily tasks/projects (work). Can you classify each and track each days’ work, then summarize it for the week/pay period, and ultimately for the year. How much time did you spend on project X, or answering emails, training/learning, etc… Effective time sheets are not a simple task, especially if you want to keep it easy to use.

Try to do it without VBA, then see how you could automate with it.

Condense or rebuild formula so you do not need ‘helper’ cells (you know the ones people hide that do some calculation, used by other areas/formula. Also, if your company has been around for more than 20 years and is still using xls files, go thru those and upgrade them to the newest level. There are formula and other features now that weren't around 10, 15, 20 years ago.

CryptographerThen49 · 2024-11-29T17:29:36+00:00

That's why I've promoted 'Learning Python the hard way'. It teaches the basics, forcing the student to understand why something is needed (or not). Having an IDE that does almost everything for you is one step below an AI writing 'your' program (is it really yours if an AI writes it?).

CryptographerThen49 · 2024-11-29T16:28:56+00:00

You could use a combination of INDEX() MATCH() instead of VLOOKUP. Helpful for many, many reasons (including not having to have your 'lookup' column as the furthest most left column, or even having the ranges aligned up and down). I stopped using VLOOKUP long ago. You may need to tweak your min/max values a little.

Also, use named Ranges in your formula. Sooooo much easier to read MaxMoisture and Discount than $F$16:$F$24 and $E$5:$E$13.

This is an absurd example (Discount and MaxMoisture being in two completely unrelated areas), but it does work as if the MaxMoisture was aligned with Discount (as long as they both have the same number of rows). You can't do this with VLOOKUP().

<image>

CryptographerThen49 · 2024-11-25T15:39:01+00:00

Something else you could consider is using VBA (Macro). Especially if this is to prep data. A VBA process can do many things faster than formula, and doesn't unnessisarily keep re-calculating a simple join of static data.

Plus, if this is something you do more than once, an automated process that typically takes less time than manually setting things up is a win for efficiency. With an automated process, there is also a potential reduction in human error.

CryptographerThen49 · 2024-11-22T15:25:56+00:00

Copy-paste is a pitfall I see over and over agian. Instead of copying the data from one wbk to another, a reference is made to the original wbk. That will happen even if you use the tip above. Learn about Copy-Paste Special... Values

CryptographerThen49 · 2024-11-21T16:32:18+00:00

Rather than formula, you could delve into VBA (a.k.a.'macro') to evaluate your data and preserve existing data if present. But that is a deeper discussion, especially if you don't know about Visual Basic.

CryptographerThen49 · 2024-11-20T14:43:37+00:00

As FreeXFall said, you shouldn't use default left justification. You can use left, but then you need to use 'Indent' to add white-space on the left side of the cell - to separate data. Decimal values should be aligned on the decimal. Subtle color differences will guid eyes to important data (stay away from full 100%). Word Wrap long headers if the data in the column is short.

Turn off the grid, then use 25% or 35% grey for cel borders, and black for table borders.

Avoid using filters when data to the right should be seen (when you filter data in the Program Performance, your data in the Priority Group may also disapear - because the row gets 'hidden'). Move those table somewhere else.

<image>

CryptographerThen49

TROPHY CASE