Solved How to quickly duplicate Tables & associated subforms within a main form (1 Viewer)

[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.

 

[dibblermail]

Thank you again guys.

I'm going to put something together for you to comment on using the real data fields. I can see the direction your going in & I believe I can follow the implied logic in it. We'll see how close I get!

I want to try & do it this way around as I'll remember what I'm learning better. I'll struggle to learn if its done for me & I just see the end result.

Give me a few days as I have to fit in in around paying work.

 

[MajP]

People who often have a lot of data already are often reluctant to normalize it because they think there is a lot of manual labor.
There might be, but we can help automate that. I can usually do stuff for people that would take them hours in a few minutes.
There are many automation techniques to take out the labor. I often use a combination depending what is easiest and fastest.

1. Using Excel especially Power Query in Excel you can do a lot of normalization ahead of time.
There are some good links somewhere on techniques on how to do this. I will have to see if I can find it. Try to do as much ahead of time. With excel there are a lot of copy and past possibilities not in Access.
2. Used different action queries to make new tables or move data to new tables. Often a combination of Union, updates, and appends
3. Use code. This is often my go to when you have lots of columns and making action queries would also be labor intensive. In the below example I demo turning rows into columns. The demo is not that exciting because it is only three Columns, but the point was the OP stated there is really 40 columns. Normalizing this in a query would be very labor intensive.

Query Help on "Lookup" Type Function

I have 2 tables. Table1 Reg#,CED%, WW%, YW% 100,5%,5%,15% 101,7%,4%,10% 102,10%,6%,10% Table 2 EPD,%,Val CED,5%,12 CED,6%,14 CED,7%,16 CED,10%,25 WW,4%,2 WW,5%,4 WW,6%,6 WW,10%,5 YW,10%,1 YW,15%,2 I am looking to get a query that would create the following result set comparable to a lookup...

www.access-programmers.co.uk

So if you can figure out the design you want we can save you hours showing how to automate moving data from non-normal to normal.

 

[LarryE]

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

I removed the relationship link, but kept the JobManagerID in the table in case it is needed. There should be no required JobManagerID.
I also removed the PartID link in the JobDetail table. That should be an option but not required as well.

 

[cheekybuddha]

I removed the relationship link, but kept the JobManagerID in the table in case it is needed.

I was just confused as, in my head, JobManagerId would be linked to an EmployeeID directly, but I guess you use a PayrollID as a person/employee anyway.

(I probably would have payroll details separate and link via the EmployeeID, but your I understand your method now)

 

[dibblermail]

Sorry all,

I got swamped with work & couldn't get anywhere near looking at this until now.

I figured the fastest way for me to have a look at the data was to do it in a package I know well. That way I could quickly create what I think I need visually (for it to be ripped to shreds & remade probably in access).

This is my starter for 10... It could be split more in places & I will do that if it's felt I haven't split it enough (I thought it was probably split enough).

 

[arnelgp]

Struc and Arch for Job can be merge to 1 table, just add another field to signal (flag) if it is for Struc or Arch.