How to quickly duplicate Tables & associated subforms within a main form

[Uncle Gizmo]

Guide: Transitioning from Excel to Microsoft Access for Invoicing Systems​

1. Analyze and Understand the Current Excel System​

• Inventory of Sheets and Data:
  • List all worksheets and their purposes (e.g., Invoices, Job Details).
  • Identify relationships between sheets (e.g., linking invoices to jobs).
• Understand VBA Logic:
  • Document all VBA macros, automated calculations, and transformations applied in Excel.
  • Determine which functions are essential and which are cosmetic.
• Review Data Structure:
  • Identify redundant data, repeated fields, and opportunities for normalization.
  • Check for existing unique identifiers like Job Numbers.

---

2. Plan the Access Database Structure​

• Normalization:
  • Break down the Excel data into relational tables (e.g., Jobs, Invoices, Invoice Line Items).
  • Follow database normalization principles (1NF, 2NF, 3NF).
• Define Table Relationships:
  • Establish primary and foreign key relationships (e.g., JobID linking Jobs to Invoices).
• Design Metadata Fields:
  • Include fields for audit trails, timestamps, or user edits if required.

---

3. Prepare the Excel Data for Import​

• Add Unique Identifiers:
  • Ensure all rows have unique keys (e.g., RowID, JobNumber).
• Clean Data:
  • Remove inconsistencies, empty rows, and duplicate data.
• Consolidate Sheets:
  • Combine similar sheets (e.g., multiple invoice sheets) into a single unified dataset with a differentiating field.

---

4. Import Data into Access​

• Link or Import Excel Files:
  • Use Access's External Data Wizard to link or import raw Excel data.
• Stage the Data:
  • Load data into temporary staging tables for cleanup and normalization.
• Validate Imported Data:
  • Verify that data types and formats are correctly handled (e.g., dates, currency).

---

5. Implement Business Logic in Access​

• Recreate VBA Logic:
  • Convert Excel VBA macros into Access queries or VBA modules.
• Design Queries:
  • Use SQL queries to automate calculations, data transformations, and reports.
• Create Forms and Reports:
  • Design user-friendly forms for data entry (e.g., invoice creation).
  • Build reports that replicate or improve upon Excel’s output.

---

6. Optimize and Test​

• Index Key Fields:
  • Improve query performance by indexing fields like JobID, InvoiceID.
• Test Functionality:
  • Validate the database’s functionality against the original Excel system.
• Get Feedback from Users:
  • Ensure that the new system meets the users’ operational and reporting needs.

---

7. Train Users and Transition​

• Train Stakeholders:
  • Provide training on using Access forms and reports.
• Pilot the System:
  • Run the new system alongside Excel to catch issues.
• Fully Transition:
  • Migrate all operations to Access and decommission the Excel system.

---

8. Document the Process​

• Create User Manuals:
  • Document database structure, workflows, and user instructions.
• Maintain a Change Log:
  • Record updates and improvements to the system.

---

Attribution​

This guide was created by ChatGPT in collaboration with Uncle Gizmo, informed by a detailed discussion on transitioning Excel invoicing systems to Access.

Acknowledgment of Contributors​

This guide is based on a discussion thread involving several contributors who shared their expertise on transitioning from Excel to Microsoft Access. Their insights and advice formed the foundation for the process outlined above. Special thanks to:

1. dibblermail - For initiating the discussion and providing a detailed overview of their current Excel system and challenges.
2. MajP - For emphasizing normalization principles and relational database design.
3. Uncle Gizmo - For sharing additional resources and practical advice for transitioning to Access.
4. Gasman - For highlighting the importance of understanding normalization and sharing resources for debugging and data randomization.
5. GPGeorge (George Hepworth) - For offering detailed insights into database structure and user considerations.
6. CJ_London - For discussing table limitations and strategies for managing large datasets.
7. arnelgp - For offering practical help and advice on sharing and anonymizing data.
8. Pat Hartman - For reinforcing the importance of a sound foundation and illustrating the benefits of relational databases.

 

[MajP]

I have a large slow spreadsheet over multiple sheets with lots of VBA which we use to manage our jobs & I'm remaking it in access.

What you show looks typical and trivial. If properly designed will be small, fast, and require very little code at the same time providing far more user features. (Searches, filters, reporting, multiple views, etc...)

Doing what you did in Excel is like creating a spread sheet application in Word. Doable but like designing with both hands tied behind your back.

 

[MajP]

FYI,
You are now at 20 posts, and this is just drag on. Simply post your tables with sufficient data and scramble any proprietary information. It will probably take an hour for someone to post the correct table design and a working demo database.

 

[dibblermail]

Just taking a wild guess at what you want to track and how your business operates, I sounds like:

1. We have multiple customers
2. Each customer may have multiple customer sites
3. Each customer site may have multiple jobs
4. Each job is completed over multiple dates and requires multiple people, time ranges and equipment
5. Each job results in an invoice to the customer

Does this sound correct or at least close?
ACCESS projects ALWAYS follow the business workflow. You build your tables and table relationships around that business workflow.

Please describe what your business is and what data you wish to keep track of. Without that understanding, we cannot help you design your application.

Yes, thats more or less exactly what this tracks. The rest of it that I haven't mentioned yet is basically MI from that data. Tracking things like all the jobs with an invoice outstanding or all the jobs currently being quoted. Which is all stuff I know it can do, but I haven't looked at yet.

Plus, when you add a new job the current system creates Folders on the NAS & populates it with pre-configured files all correctly named based on the criteria in the spreadsheet. But that's all relatively easy to recreate once the core of the database is nailed down.

 

[dibblermail]

FYI,
You are now at 20 posts, and this is just drag on. Simply post your tables with sufficient data and scramble any proprietary information. It will probably take an hour for someone to post the correct table design and a working demo database.

I really don't want to do that, but only because I'll take the win & run with it without understanding the decisions made & why. I'd rather go a little slower & understand why decisions were made so I don't have to come back and ask again next time. I have at least 3 more to convert after this one.

 

[dibblermail]

What you show looks typical and trivial. If properly designed will be small, fast, and require very little code at the same time providing far more user features. (Searches, filters, reporting, multiple views, etc...)

Doing what you did in Excel is like creating a spread sheet application in Word. Doable but like designing with both hands tied behind your back.

When I made it we didn't have a copy of Access and there was no appetite from the holder of the wallet to get it.

Now I've proved the need for a system, through massive time savings by automating things, that attitude has changed.

I'm doing it in Access now because we upgraded & now have it, I considered doing it in a more modern database, but A we don't have that & B I already know enough VBA to have a go at this right now.

As usual the previous & current decisions have nothing to do with what's best, but what was possible at the time. And anything was better than how it was when I arrived.

 

[Mark_]

In post 25 it looks like each row for an invoice would have 25 columns of data, if just put in as a single row.
In a database, that would be one record in a table.

Your parent record would have one or more child invoice records.
Means you would be able to have 1 invoice for one of your customers, 1165 for another, and there is no difference in ACCESS (or any other database system) in how you set things up.

 

[Uncle Gizmo]

There's a sample database that comes with Microsoft access called the Northwind database... Some developers got together and modernized the original northwind database, I believe a few of those developers frequent this forum.

There are two versions of the modernised northwind database, one for general use and one for developers... Both are free and are basically an example of an invoicing system...

If you haven't come across the latest versions of the north wind database then in view of your underlying invoicing requirements then this might be a place to start....

 

[MajP]

I really don't want to do that, but only because I'll take the win & run with it without understanding the decisions made & why. I'd rather go a little slower & understand why decisions were made so I don't have to come back and ask again next time. I have at least 3 more to convert after this one

If you provide Something. People can recommend a table structure. Once they provide that, they will be able to justify why they organize it that way.
We have no idea of your busness, rules, or what you hold for data. Look at a simple form can only guess. Invoice development is pretty standard.
It would take me 15-20 pages to guess at generic recommendations and may not even be close. If seeing the data I can knock out a generic proposal and describe it in half a page.

As usual the previous & current decisions have nothing to do with what's best, but what was possible at the time. And anything was better than how it was when I arrived

This was not meant to be a criticism. This was to say if you migrate this to Access it will be easy to do. You will not have to invest that same level of work and complexity as doing it in Excel. It will take some initial time but in the long run it will be so much easier.

 

[LarryE]

Here is a very very preliminary suggestion for a job-costing design:

It is based on a little common sense, limited information from you and some knowledge of job-costing.

1. Customers may have multiple sites which you provide job services to.
2. Each Job may have multiple dates where you provide services including employee time and parts.
3. Employees are paid hourly and each employee has a start and end time recorded in the JobDetail table for every JobDetailDate.
4. Parts used for each JobDetailDate also have a cost associated (PartCost) and PartDescription.
5. Notice how JobDetailDates and Parts and Employee Payroll time all come together in what is known as a Junction Table. In this case, it is the JobDetail table. That table has relationship keys (known as Foreign Keys) with the Payroll table, the Part table and JobDetailDate table.
6. Notice also that we don't store calculated values in table fields like you would with EXCEL. For example, the Invoice table has no Total Invoice amount field. That's because any Part costs and employee time in hours are going to be calculated on forms and reports and not stored in a field.
7. Also note an invoice may be partially paid at any given date, so I included an InvoicePaid table to store the payment history and calculate amounts due at any given time.
8. The payroll table has two tables that are connected to it. That is because an employee can be managing the project but may not be actively participating each day, so you need the ability to name a manager independent of the daily work in the JobDetail table.

This is a simple example of a job-cost application where time and materials used come together. This is also an example of data "normalization" where each piece of data is stored in one place and one place only. Also note how the relationships are described between each table.

Obviously, I do not know all there is to know about your business or what specific fields you may need but I attach the file for your study and use and to make changes to fit your specific requirements.

Warning: Do not create any forms or queries or reports until you have the design required. Input test data into each table manually first, then use a query to view the joined tables to see it is what you wanted. You will need to input the Foreign Key references into each table by hand, but it is much better to find problems at the very beginning of the process.

 

[MajP]

Here is a very very preliminary suggestion for a job-costing design:

Notice in a well designed DB that there are lots of tables and few columns per table.
Almost immediately when someone comes here and say they have a table with 40, 50, 100, 200 ... columns I know they are trying to take a spreadsheet design and put it into Access.

Most importantly notice information only gets added once and only once. Only once place for customers, jobs, employees, parts, etc. That info is only entered once but you can refer to it in may places.

 

[cheekybuddha]

Here is a very very preliminary suggestion for a job-costing design:

@LarryE - can you explain why Payroll.PayRollID is linked to Job.JobManagerID? It seems odd.

 

[LarryE]

@LarryE - can you explain why Payroll.PayRollID is linked to Job.JobManagerID? It seems odd.

Yes. Because the Job Manager may NOT actually do any timed work in the JobDetail table. They might be separate. But I am giving the option. They might manage and do timed work as well. But I thought the Job Manager belongs in the Job table as part of the whole Job.

But remember, the whole design is a guess. I don't know if they have Job Managers.

 

[GaP42]

Here is a very very preliminary suggestion for a job-costing design:

Also suggest invoice is really InvoiceLineItem, as currently it shows an Invoice is issued for each JobDetail. Expecting that JobDetails are collected and invoiced. JobDetail - InvoiceLineitem - Invoice (and then InvoicePaid and PaidAmount may then be attributes of Invoice). [it may also be difficult to track JobDetails being invoiced in multiple invoices]

 

[LarryE]

Also suggest invoice is really InvoiceLineItem, as currently it shows an Invoice is issued for each JobDetail. Expecting that JobDetails are collected and invoiced. JobDetail - InvoiceLineitem - Invoice (and then InvoicePaid and PaidAmount may then be attributes of Invoice). [it may also be difficult to track JobDetails being invoiced in multiple invoices]

Yeah, you might be right.

 

[Pat Hartman]

The look & feel is very specific & would be very hard to walk away from. It's set out just how the primary user wants it & so I need to replicate that as closely as possible.

Do NOT allow how you want the data to be presented to cause you to try to replicate the spreadsheet using Access. You will absolutely hate Access and it will be much more work than your spreadsheet ever was. Just because a table sort of looks like a spreadsheet doesn't mean that they have anything other than a superficial resemblance in common. With Excel the data and presentation layers are combined. That isn't how a relational database application works. The two layers are totally separate and so you store the data in the way that is most efficient and useful for processing but you present it in a way that makes the user happy. There is nothing that prevents you from producing reports that look like spreadsheets or even exporting actual spreadsheets that display data. It's just that the spreadsheets are no longer functional and used for data entry.

There are currently about 600 columns that I need to capture & currently there are just under 1300 rows (1 row per project number).

You can have millions of rows but 600 columns is pretty horrifying for a single table. It also tells us that you have at least one repeating group. Each of those columns should probably end up as a row rather than a column. We need to see actual workbooks if you want us to help. Otherwise, you will need to learn the concepts so you can do the normalization yourself.