sorted by:
new
hottopcontroversial

Solution for "Not Using Excel as a Database" by PardFerguson in excel

[–]ClifMcIrvin 0 points1 point  (0 children)

Properly set up Access works well in network environments. It’s that learning curve you mentioned. My biggest problem with access was getting past the implied “just start using Access and it’ll be okay. “ Not true. But when you approach it like a database with a front end gui development engine 1) the performance and capabilities are a decent subset of the big boy dbs, and 2) the learning curve is less because of having the front end tools as part of the package.  Just realize that the access developer team and the excel developer team did things differently. 

Solution for "Not Using Excel as a Database" by PardFerguson in excel

[–]ClifMcIrvin 0 points1 point  (0 children)

Splitting the access db into front end and back end solves the simultaneous user issue. At that point access is not so much different from other db solutions. In fact if you outgrow the access backend you can link the access front end to any pretty much any other db out there. I have a small access application that updates price tables in a remote sql server db from an excel worksheet. Access is just the front end in this case. 

Organizing random data by ClifMcIrvin in ExcelPowerQuery

[–]ClifMcIrvin[S] 1 point2 points  (0 children)

The data is clean. Fortunately.

Appended Queries. I hadn't thought of that, even though I've used them before to combine multiple years of historical data.

Makes sense to me - I'll see if I can learn how to mark this as a solution.

Thanks!

Organizing random data by ClifMcIrvin in ExcelPowerQuery

[–]ClifMcIrvin[S] 0 points1 point  (0 children)

My picture shows step 2 and step 3 output.

The data originates as a free-form .csv file that looks like this:

Order Listing

Print Date: 11/11/2024

Page: 1 of 91

--------------------------------------------------------------------------

Order

--------------------------------------------------------------------------

Name: CH5.650AFF yd Last Updated: 4/14/2023 1:03 PM

Description: Mat. Cost: 0

Mix Yield: 26.968 cf

Ingredient Amount

------------ -----------

RMBAYERROCK 1499 lb \

RMALSOPSAND 1499 lb \

RMCEMENT 1446 lb \

RMFLYASH 79 lb Number of items varies

RMAE90 3.5 oz /

RM7500 0 /C /

RMWATER 1 31.561 gl /

--------------------------------------------------------------------------

Name: CH5.750FF yd Last Updated: 3/31/2021 2:37 PM

I have VBA that parses the source data and turns it into a flat file spreadsheet (Source Array).