So I want to repeat the value of ORIGIN across each row and convert the Excel column names for each fiscal year-quarter into the values for a new field, FY_QTR.
Please note that in the actual source table there will be many more columns for a multitude of quarters and many more rows for a variety of Origins. Over time we will add more quarters and more origins.
If there's some way of handling this without using VBA I'd prefer that since I'm VBA-inept... then again, this may be the best time to start learning it. This feels like something I should able to do with a simple crosstab, but I'm stumped.
As you feared, the only way (that I know, anyhow) to do this conversion is with VBA. The reason is that a query will only represent a dataset in the same form as an Access table, not re-shape an Excel table to conform to an Access table.
To explain: in Excel, you now have a dataset that has "some" rows and "lots of" columns, or a "row-major" dataset. In Access, you will have "some" columns and "lots of" rows, or a "column-major" dataset.
I've done this many times through the use of an array. Link to, or import, the Excel table. In Access, determine how many columns there are (as you say you will be adding over time. I have no doubt you'll also be deleting) with the following code
X = CurrentDb.TableDefs("The Table Name Here in Quotes").Fields.Count
Also, determine how many rows there are, and store that number in a variable as well with
Y = CurrentDb.TableDefs("The Table Name Here in Quotes").RecordCount
Now Dim an array, e.g.
Dim ProdArry(Y,X) As Variant
Notice the Y before the X. In VBA, go row by row, reading the value in each column, and storing it in the appropriate element in the array. Since you're changing the dataset format, each time you advance by a column (in the original), you advance a row in the array to store it. MAKE SURE you substitute all null values as 0. After you finish that task, you can figure how to then go back over it row-by-row and enter each product into your re-formatted table.
You'll need to look in the Help files to study arrays in order to get this right, but it's far from undoable. I'm confident that you're only a "not yet accomplished" VBA coder, just like I was back in 1999.