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

[dibblermail]

The development of database using the relational paradigm requires the application of normalisation techniques to minimize data duplication - redundancy, ensure the data is logically consistent, and data integrity is maintained through the established relationship / rules between the tables in which the data is stored. That does require a detailed understanding of the data needed to perform the business functions (the scope) your project will fulfill. That detail lies with you, However it is apparent that the proposed layout in the above post #41 was some problems from a design perspective. Limiting to what appears to be the intention of the proposed layout, the relationship rules could/should be:

Your Main Table (probably an appropriate name is the JOB table). A JOB is carried out at ONE site.
A (client) COMPANY may request any number of quotes for different JOBs that may be accepted and become orders.
A (client) COMPANY will have one or more CONTACTS (like Company Directors)
For A JOB one or more INVOICES may be raised to a (client) COMPANY
For a JOB there are one or more SITE_ROLES.
A SITE_ROLE TYPE describes the SITE ROLE undertaken by a CONTACT.
A SITE_ROLE TYPE includes Architect, structural engineers, consultant engineer, project manager, site contact, etc as needed
A CONTACT will hold the contact details of a person: that person may be a company director, an architect, or a .... as specified by SiteRole when engaged in a JOB

You will notice that there is no reference to Arch for Job, Struc For Job ... they have been merged and generalised into the SITE ROLE table.
COMPANY INVOICE INFO is merged into the COMPANY table unless you can rationalise why you might need to retain more than one record for the same Company where an invoice may need to be sent at any one time.

Perhaps then you might adopt/ adapt your layout to a model more like:
View attachment 118830

Thanks for going through that in so much detail. I think you’ve more or less nailed it.

As a work flow in the Excel version...

Client asks us to quote on a job, so initially we have basic info:

• Job Number
  • It gets the next available number & it’s unique
  • It’s maintained throughout the jobs life, including if it moves beyond a quote
  • Some sites get multiple Job Numbers for different jobs, but they’re run as individual jobs
• Status
  • Quote, Current, Closed, etc
• Client
• Site Name
• Site address
• Job Type
  • We are metal fabricators, so Structural Steel, Bespoke Cladding, Staircase, etc
• Date Required
• Client’s contact
  • Some Clients have several contacts & sometimes more than 1 on a single job
• Current Action
  • Tracks where the job is in our system, Being Drawn, Awaiting approval, etc
• Architect info
• Structural Eng info

That info is also used to run code to make a set of folders with default files added to it & named to match the info above. Some of those files also have relevant info inserted into them based on the info above. We then use the files & folders to run that job & this system (currently in excel) tracks the progress & financials for those jobs as they progress. We currently have 6 different pages of MI that are generated from the data set in excel.

We invoice most jobs several times. This could be to break out elements of the job or it could be the job is phased. That’s why for a single job number there are invoice 1-25 in the old system. I realise that with access I don’t need to pre-allocate 1-25 in 1 table & I can just add a new line with the next number as required.

When starting a new job it could be for a known client, at a known site, with known contacts; Or it could be a known client at a new site but with existing contacts. So the system has to find that info & offer relevant suggestions to the user when creating the job to avoid typing info into the system multiple times (my excel code currently does this as a cascade starting with Client).


Would Site Role cause issues finding the right person on a different job. I may need an architect I’ve used before, but with a different client on a different site. That was why I had them split off in the old system. If its easy to overcome then that would make a lot of sense to go your suggested way.

 

[Isaac]

Sorry for going silent! It was a very busy weekend & Yesterday was just as crazy.

Thank you to everyone for all the suggestions & help. I have links with some reading to do here & a database to download & explore.

The spreadsheet has been evolved & honed over 5 years to something that works very well, but is far too slow now.

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.

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

I can see that by adding 1 more field I can make the invoice table & form work for all project numbers; but I was concerned it would fill up too quickly if I went that route. From your comments it looks like I may have been wrong?

Give me chance to run through your links & the example database and I'll come back.

Heads up, if you continue down this path the Access version will actually be worse than the Excel version.
Definitely study up on normalization and understand how to structure tables, it's the foundation of everything and thus, the sequential beginning of everything design-wise. With the slight exception that sometimes it's helpful to think of what reports will be needed to ensure you are capturing the appropriate columns (sometimes referred to as Fields) in your Tables.

 

[GaP42]

Would Site Role cause issues finding the right person on a different job. I may need an architect I’ve used before, but with a different client on a different site. That was why I had them split off in the old system. If its easy to overcome then that would make a lot of sense to go your suggested way.

I'll just address this question at this time (gotta get other things done)
Site role is the site specific role a contact has for a job: eg the designated architect.
However your contacts have a position/ expertise within the company in which they are employed - such a company director / consulting architect.
It is quite a simple matter to then envisage using the Contact table to identify Consulting architects (making sure you are careful with your categorisations) to list those that you might engage for a specific job and when you have contacted them to ensure they are willing/able to be engaged, to then use a function to create a record in Site-Role for that contact (consulting architect) - as the Site Architect.
Does that clarify/help?

 

[GaP42]

When starting a new job it could be for a known client, at a known site, with known contacts; Or it could be a known client at a new site but with existing contacts. So the system has to find that info & offer relevant suggestions to the user when creating the job to avoid typing info into the system multiple times (my excel code currently does this as a cascade starting with Client).

This is a form design problem needing to support your work flow, however I would suggest again that this is likely something like:
A New Job (Quote) is to be raised: Prompt -->Existing or new client? if Existing then Prompt --> Present Client Sites to select or New Site btn
Queries supporting "Existing Client" and the " Client Site /currently active or all" are straightforward to present to the user. You could also then prompt to use the, say the Architect on the job-site selected, or other roles associated with the selected site - copying the assignments - and editing/ adjusting as required.

To help further with your modelling efforts the following is offered based on the above. Note however to review and assign the data types shown as it is focussed on getting the concepts across. This is still at an unrefined staged, there are some/many generic placeholders such as InvoiceDetails or AddressDetails where you need to breakdown the atomic elements needed that you require - or potentially require the introduction of additional tables. The annotations may help to envisage how it may work for you

An area that can be further generalised in the "Helper" or lookup "Type" tables - if they share the same structure - they may be combined, with addition of a type column. One form can then be used to manage all the values for those types. However make sure you really look closely at the current workflow : there may be implicit assumptions being made about whether some of these types for eg change over time for a job and you need to keep the date of the change.
Edit: Updated diagram to include Divisions and Payments and some extension / amendments to Jobs

 

[dibblermail]

I'll just address this question at this time (gotta get other things done)
Site role is the site specific role a contact has for a job: eg the designated architect.
However your contacts have a position/ expertise within the company in which they are employed - such a company director / consulting architect.
It is quite a simple matter to then envisage using the Contact table to identify Consulting architects (making sure you are careful with your categorisations) to list those that you might engage for a specific job and when you have contacted them to ensure they are willing/able to be engaged, to then use a function to create a record in Site-Role for that contact (consulting architect) - as the Site Architect.
Does that clarify/help?

Yes, I can see how that would work using an extra field to identify where that person fits in the structure. Client Contact, Arch, etc

 

[dibblermail]

View attachment 118847

I looked at it again & thought maybe I should have split out the Site specific info into it's own table. It's unusual, but we have worked at the same site for different clients before. Does that make more sense?

 

[GaP42]

maybe I should have split out the Site specific info into it's own table. It's unusual, but we have worked at the same site for different clients before. Does that make more sense?

That will be a judgement call: it is quite legitimate to have a separate Site table to hold the location details, however really this is only useful to you if you find that the Site information is re-used quite often for different jobs - you did mention that you (sometimes) quote for a number of jobs on the same site (even if for the same client or for perhaps later for a different client if ownership has changed). Another key element to this is if the site information stays constant: address / location will stay the same irrespective of job, however, for eg, site notes may not - they might be about site access. Does it matter if these change over time - and is it important to keep them as they were at the time of the job. If they do change and it is important, then these notes do not belong in the Site table.
Hope that helps

 

[dibblermail]

That will be a judgement call: it is quite legitimate to have a separate Site table to hold the location details, however really this is only useful to you if you find that the Site information is re-used quite often for different jobs - you did mention that you (sometimes) quote for a number of jobs on the same site (even if for the same client or for perhaps later for a different client if ownership has changed). Another key element to this is if the site information stays constant: address / location will stay the same irrespective of job, however, for eg, site notes may not - they might be about site access. Does it matter if these change over time - and is it important to keep them as they were at the time of the job. If they do change and it is important, then these notes do not belong in the Site table.
Hope that helps

Yes, I totally understand the logic, thanks for clarifying. I would keep site notes with the Job No for that exact scenario.

 

[dibblermail]

A quick question.

I have several Fields that are on the face of it an integer. But I also need a "TBC" solution. I do this in excel by setting the values as text so I can trap the TBC values & kill all Cells/Text Boxes that show calculated values based on those TBC values.

How would you approach that in access where data types aren't so flexible?

I'm assuming something like Tax band to be saved as an integer like 10%, 20%, 30%, etc and a second field beside it to act as an override which is a boolean like TBC or Use Tax Value.

 

[arnelgp]

do you only monitor the job's progress as a whole or do you
still monitor what materials has been used/installed?

does the quotation include the quotation price (job estimate) for the job?

 

[GaP42]

A quick question.

I have several Fields that are on the face of it an integer. But I also need a "TBC" solution. I do this in excel by setting the values as text so I can trap the TBC values & kill all Cells/Text Boxes that show calculated values based on those TBC values.

How would you approach that in access where data types aren't so flexible?

I'm assuming something like Tax band to be saved as an integer like 10%, 20%, 30%, etc and a second field beside it to act as an override which is a boolean like TBC or Use Tax Value.

Access will not allow mixed datatypes, and to a database modeller/designer, those are two different data items (and so normalisation rules apply about atomic data items) - one that accepts/records a specific value such as applicable tax band and another perhaps boolean, which says, say, TaxSettingCompleted (to indicate TBC).

 

[dibblermail]

do you only monitor the job's progress as a whole or do you
still monitor what materials has been used/installed?

does the quotation include the quotation price (job estimate) for the job?

It's primary role is to track Job Contact info, Site info, Job progress & money. It's secondary role is as a repository of jobs we didn't win & jobs we have completed.

The main MI out the back shows...

• Current Quoted jobs & their progress
• Current live jobs & their progress
• Jobs to be invoiced
• Jobs in invoiced & awaiting payment
• Jobs closed
• Jobs Lost

Separate from this (but linked to auto update values as required) I also created other spreadsheets:

• Each job has its own quote sheet/s which contain all materials, labour/time & other associated costs (down to very bolt & saw cut)
• Stock Materials are tracked in a dedicated spreadsheet & are removed as they are used.

All of it works based on workflow, so updates to the sheets happen without having to remember to do it.

 

[dibblermail]

Access will not allow mixed datatypes, and to a database modeller/designer, those are two different data items (and so normalisation rules apply about atomic data items) - one that accepts/records a specific value such as applicable tax band and another perhaps boolean, which says, say, TaxSettingCompleted (to indicate TBC).

That's exactly what I was trying to convey. Thanks for confirming I did have the right approach. I have quite a few situations where that will apply.

 

[dibblermail]

Another quick question.

Data validation best practice. How would you control a Combo box to show a known list of values?

Do I create a table for each combo box? Most need to be lists which are occasionally edited by an end user

In excel I had them all in 1 sheet as Named Tables

 

[GaP42]

Another quick question.

Data validation best practice. How would you control a Combo box to show a known list of values?

Do I create a table for each combo box? Most need to be lists which are occasionally edited by an end user

In excel I had them all in 1 sheet as Named Tables

Most/all of your combos will be driven from the Type tables - using a query of the table to show the displayed value, hiding the stored ID of the particular type. Type table lists should not be arbitrarily edited - you need to avoid multiple terms being used for the same type. However they can be set up so that if a value is entered that does not exist the form controlling such additions is displayed. There are other methods too.. If you discover data items not covered by the Type list that likely leads to needing to create another Type table. As mentioned earlier these simple Type tables can be combined into one master table holding all the types each type being distinguished by the TypeCategory (ActionType, ProjectStatus, etc)
The lists for these Types should not (generally) be long - the user should be able to see the item they wish to select in the list displayed. If they are longer consider a find-as-you-type option for the combo (not available by default).
Anther trick, if needed, is to use a mapping between equivalent types and the master of those types so that selections/entries made in the "real data" can then pick up the masterID for that type. Not often used. However if you need to migrate your spreadsheet data and users have created some false duplicate Types a mapping table is used to clean the data on import.

 

[dibblermail]

Most/all of your combos will be driven from the Type tables - using a query of the table to show the displayed value, hiding the stored ID of the particular type. Type table lists should not be arbitrarily edited - you need to avoid multiple terms being used for the same type. However they can be set up so that if a value is entered that does not exist the form controlling such additions is displayed. There are other methods too.. If you discover data items not covered by the Type list that likely leads to needing to create another Type table. As mentioned earlier these simple Type tables can be combined into one master table holding all the types each type being distinguished by the TypeCategory (ActionType, ProjectStatus, etc)
The lists for these Types should not (generally) be long - the user should be able to see the item they wish to select in the list displayed. If they are longer consider a find-as-you-type option for the combo (not available by default).
Anther trick, if needed, is to use a mapping between equivalent types and the master of those types so that selections/entries made in the "real data" can then pick up the masterID for that type. Not often used. However if you need to migrate your spreadsheet data and users have created some false duplicate Types a mapping table is used to clean the data on import.

Using an additional field for CategoryType to put them all in 1 table is the fix I was looking for. It was mentioned above but got forgotten in the volume of information I'm trying to absorb.

Thanks for the nudge in the right direction.

 

[MajP]

Here is a demo of a single lookup table where you are simply saving a value.
Everyone one of the combo pulls from the same lookup and every lookup is editable. I use this when I simply need a lot of simple "value" lookups without other related fields. Does not make sense for certain lists.
1. Very long lists
2. Lists that need a lot of validation
3. Lists with other related data fields
4. Lists where the value changes need to cascade often.

 

[dibblermail]

Here is a demo of a single lookup table where you are simply saving a value.
Everyone one of the combo pulls from the same lookup and every lookup is editable. I use this when I simply need a lot of simple "value" lookups without other related fields. Does not make sense for certain lists.
1. Very long lists
2. Lists that need a lot of validation
3. Lists with other related data fields
4. Lists where the value changes need to cascade often.

Thanks,

That looks to do everything I would need to control the basic combo boxes I have.

I'll have a play.

 

[dibblermail]

Me again, I've hit a stupid issue.

I wanted a dummy run at building a few linked tables & forms but its going wrong. Its got to be a simple fix, but I'm not sure what it is. When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. Forms for the individual tables show 2 records.

I've tried several times but keep running into the same issues.

Please can someone explain what this muppet did wrong? did I link them incorrectly?

I know I have Client & Company linking (not all entries in tbl_Contacts is a client & I knew I'd trip over that at some time in the future if I used "Client") , that was intentional, hopefully that's not a bad idea?

 

[GPGeorge]

"When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. "

That is pretty much how it usually turns out when you try to use multiple table queries.

The solution is to bind each form to a table. Or bind each form to a query based on one table.

The chances of creating read-only queries increases dramatically when you join in more tables.

If you need to add records to related tables (i.e. a "one-side" table and one or more "many-side" tables) the way to design forms for that is to create a main form/sub form design.

The main form is bound to the "one-side" table and each subform in that main form is bound to one of the "many-side" tables.