database selection

[riptides]

Wow... getting into the details. You asked for it!

I tried to avoid any duplicate data entry, when creating the spreadsheets. But because your "form" is your "table" is your "report" in Excel, I do have may cells that auto-populate by pulling data in from other sheets.

For example, when calculating a BOM cost, I will have the BOM worksheet load the BOM for a given assembly number, then automatically find the latest revision of each item in the assembly from another workbook, and then go find the latest pricing from yet a third workbook. The pricing thing is a handful, very big formula that takes into account challenges like thee latest pricing maybe not covering the quantity in question, or older pricing that is at the right quantity but on an outdated pricing trend curve. Messy stuff, that Excel can handle quite well, but only when Excel's latest rendition of formula auto-correct doesn't break all links into the corresponding workbook.

Anyway, I haven't given it much thought yet, but I anticipate the database will have multiple tables, such as:

1. Item master
2. BOM master
3. Sales orders
4. Accounts
5. Approved vendor data
6. Customer data
7. Employee / contractor data
8. Inventory (could be rolled up into item master?)
9. Timesheets (could be rolled up into employee / contractor data?)
10. Leads tracking
11. Rates (for calculating project costs, and based partly on employee data, but partly independent due to other rates factors)

One thing I would want to ensure is that any Forms I create can easily auto-populate various fields from various tables. For example, when creating a BOM, I want the user to be able to enter an item descriptor and automatically see a list of available part numbers, or to enter a part number and then automatically see the corresponding descriptor.

Here we go....LOL.

The form you are viewing can be query based to pull information (pre-populating) from what ever the query returns. You can have one query pull everything plonk it in a "working" table and then have forms with different data being displayed out of the "working" table.

You can even read/write from Access to Excel. So say if the pricing Excel Sheet is better handled in Excel, Access will just go look there for a price. Even call up a named range off a particular sheet or just look at a hard coded cell location. Using this method, you will know someone messed up something in the workbook since you are not getting pricing data.

Then as Ken stated, LOL, you can upsize your Access database, data, to SQL. LOL.

 

[WinterDeere]

Thanks! I am firming up my opinion that Access is the best next step. Maybe it won't stay there, maybe I'll be hiring someone later to port it up to a more robust database, but Access is within my grasp and something I can do on my own as a good next step from Excel. Right now, I'm very busy growing the business and doing new design work, so I don't really want to carve any large part out of our budget to jump from Excel to an enterprise solution.

I have enough work scheduled right now to take me into early September, but I'll be looking to start playing with this mid-September, unless some big new project comes in to furlough this further.

 

[riptides]

Thanks! I am firming up my opinion that Access is the best next step. Maybe it won't stay there, maybe I'll be hiring someone later to port it up to a more robust database, but Access is within my grasp and something I can do on my own as a good next step from Excel. Right now, I'm very busy growing the business and doing new design work, so I don't really want to carve any large part out of our budget to jump from Excel to an enterprise solution.

I have enough work scheduled right now to take me into early September, but I'll be looking to start playing with this mid-September, unless some big new project comes in to furlough this further.

We have used Access to quickly assemble prototype solutions.

Some customers took the tools and just kept expanding them.

For "departmental" use, 5 to 25 users, a great solution can be had. With a growth path both for data and a wider audience at hand. There is a lot of knowledge out there on the software, still.

Have you tried loading your cleansed data into an AI model and see what it builds? Or just ask one of the many modelers for a sample Access load form for an Excel spreadsheet. Prepare to be surprised.

 

[WinterDeere]

Have you tried loading your cleansed data into an AI model and see what it builds? Or just ask one of the many modelers for a sample Access load form for an Excel spreadsheet. Prepare to be surprised.

I used to laugh at how old people lost touch with the pace of technology, and now here I am. No, short of having Siri almost always fail while attempting to use ChatGPT to answer mundane questions, I have not played at all with using AI.

I should be getting on that bandwagon, tho. It would probably seriously shorten my workload in many areas, as I sit here manually importing 9 new files each, into 10 copies of an Excel spreadsheet.

If you have a suggested starting point, I'm all ears!

 

[kenmbz]

I used to laugh at how old people lost touch with the pace of technology, and now here I am. No, short of having Siri almost always fail while attempting to use ChatGPT to answer mundane questions, I have not played at all with using AI.

I should be getting on that bandwagon, tho. It would probably seriously shorten my workload in many areas, as I sit here manually importing 9 new files each, into 10 copies of an Excel spreadsheet.

If you have a suggested starting point, I'm all ears!

Take a course on "prompting" AI for Excel and Access, should be some free ones around.

 

[riptides]

I used to laugh at how old people lost touch with the pace of technology, and now here I am. No, short of having Siri almost always fail while attempting to use ChatGPT to answer mundane questions, I have not played at all with using AI.

I should be getting on that bandwagon, tho. It would probably seriously shorten my workload in many areas, as I sit here manually importing 9 new files each, into 10 copies of an Excel spreadsheet.

If you have a suggested starting point, I'm all ears!

Type "Create a Microsoft Access form to load an Excel Spreadsheet" in any of the available AI modelers. I would start with something you KNOW as far as what you EXPECT the results to be. Just to gain confidence in the modeler. Tailor and add more inputs and you are off and running. Cut and Paste can be your friend.

I just tried Claude.AI. Yes it generated useable code.

Here is part of its response:

 

[anomad]

Can you ask the models to generate code in SQL or regular VB? Not trying to start a discussion about why I don't like VBA. That might give the OP a head start on learning enough SQL to make the next transition easier or learn some new tricks. SQL and my brain get along better than VB or VBA.



Story time:
All this reminds me of being a GIS guy back when it was new and trying to figure out the best way to centralize key data elements from a hodge podge of sources into something I had a good handle on for one source of truth. I was running GPS when it was first open to civilian agencies. I remember testing when selective availability was turned off. Flash forward to when remote sensing tools came around a lot of the young guys would tinker with all that until they were blue in the face, rather than go collect field data, and not have a very good product. I would make a pot of coffee and teach people how to use those new GPS units and draw my data by hand. I would also go to the field whenever allowed. For the AI/large language model analogy, I think it will give you a foundation to build on. But it will never replace a smart human getting in there and completing the finishing touches. The remote sensing gimmicks give you a place to start. But it doesn't replace a smart person putting eyeballs on it and tapping it with a rubber mallet to make sure it fits.

 

[riptides]

Re:-.1
MS Office uses VBA. You can ask the models to generate all sorts of code snippets or functions based on what your trying to do. They can even generate a user interface from what I experienced.

 

[WinterDeere]

Here's the thing: I'm an engineer trying to run a design/build business, not a programmer! Every hour, heck every minute, I spend dicking around with a database is another hour for which I'm not doing billable work.

So, I have absolutely zero interest in learning SQL or VB. My goal is to use the canned tools provided in a way that costs me the least time over the course of the next year. If I can spend an hour on setup to save an hour a month, it might be worth it. But if I spend 15 hours on setup to save an hour a month, then it was just wasted time.

So far, I've gotten by using Excel and Access without having to learn VB.

 

[square1]

MS Access front end / User Interface, SQL backend. MS Office products are bloated, slow, and get quirky when asked to do too much.
There are always users that can't be trusted so the Access user interface protects the backend data while SQL does the heavy lifting.