Recent content by MadPiet

I sorted it out.. the answer was a tiny bit of dynamic SQL ECLARE @TableName NVARCHAR(20) = 'Toxicity'; DECLARE @CrossApplyColumnList NVARCHAR(2000); DECLARE @sql AS NVARCHAR(3000); /* need to construct the CROSS APPLY stuff for each row. */ /* finally, aggregate the chunks into a single string...

"Use the force, Luke!" Do it in T-SQL, where the data is... just use a little dynamic SQL, like this... use itchy; go DECLARE @TableName NVARCHAR(20) = 'Toxicity'; DECLARE @CrossApplyColumnList NVARCHAR(2000); DECLARE @sql AS NVARCHAR(3000); /* need to construct the CROSS APPLY stuff for...

Some daze... I swear. Maybe because it was Monday? Public Function ListColumns(ByVal strTableName As String) Dim tdf As TableDef Dim i As Integer Set tdf = CurrentDb.TableDefs(strTableName) For i = 3 To CurrentDb.TableDefs(strTableName).Fields.Count - 1 '--...

Think Pass through Query. It doesn't. Access SQL is stuck in the 1600s or so.

Duane, that's pretty much what I did when I did it in Access in a previous lifetime. If I could do it with CROSS APPLY (and build the SQL for it), I could just return a sequence of "sets" tdf.Fields(i).Name AS Symptom, tdf.fields(i) AS Grade, tdf.fields(i+1) AS Causality, tdf.fields(i+2) AS...

The fun part is that the column names are not consistent. The pattern is {PatientID, Cycle, {ToxicityName (where the grade is stored, as if it were a spreadsheet), Causality[n], [Relatedness[n]} ) Sorry, the curly braces are the repeating groups. In a nutshell, if you find the column_id (or...

This is an academic question at this point, but I was just wondering.... (I had dozens of these tables to deal with... I was just wondering if there was a less painful way of creating the unpivoted view of the table) I have a table with an absurd number of repeating groups USE [Itchy] GO...

How do you turn off the FileSnatcher feature? The stuff that gets stored in the cloud is super irritating. If I wanted it there, I would have put it there myself.

Do you have some mock data you can share? (Doesn't have to be real... just representative.) Maybe you can do some of the grunt work in Excel and PowerQuery and the rest use Queries in Access. hard to tell at the moment though.

Before you make yourself crazy trying it in code, you might want to try creating a linked sheet in your database and running the update query against that linked table. Super quick, and you'll know immediately if it works. (I don't think it does. I want to say MSFT lost a lawsuit about it, and...

I'd just do the whole thing in Excel. If you have a hierarchy of accounts in a table, then it works out. I've seen YouTube videos showing how to do it. Kinda depends on your goals though.

Thanks! Some days I'm just blindingly stupid or just plain blind. (wow, this website is umm... not my favorite) Found a site that has grain etc prices for the Ignited States, which should work fine ... the biggest thing I wanted to find was wheat. Just gotta figure out a good threshold for % of...

Just wondering... Does anybody know of a source for price indices for various categories of foods? I have a sort of snapshot of unit prices, but I need an easy (and reasonably realistic) set of prices for the same ingredients across time. I need prices for 2020-2022 or so. (I have recipes with...

On #2 - Denny Cherry (among others) talks about crazy nesting of views. From a debugging perspective, it's a total nightmare. Whatever gains in performance you may get could be offset by the complexity of maintaining something like that.

They're covered in both Kathi Kellenberger's book (from a long time ago now) and Itzik Ben Gan's book on TSQL. Itzik's books are always great.