Pre-populated items in sub-subform (1 Viewer)

[skyliner33v]

Hi everyone.

First time poster here building by first database and I've run into an issue I can't seem to wrap my head around. I don't have much Access experience, so I've been pretty much learning as I go thanks to many of the posts here.

I'm building a cost estimating database. I built the tables and the relationships, (which I can link if needed) the way I believe they should be built with the correct relationships.

Each "customer" can have multiple estimates. And each "estimate" will have details about that specific estimate including specific items that are included, their quantity, and unit cost. And I have a junction table that links the many to many relationship between the "customers" and "items"

I have created the "customers" form, and within it i have the "estimates" as a subform. And within the "estimates" subform I have an "esimate details" subform. But in this "estimate details" subform I need to have a pre-populated list of "items" that automatically show up so that the user just has to in put the quanity and cost.

What would be the best way to include a pre-populated list of items within the "estimate details" subform? I have queries built already that pull out the items needed from a table of standard items, but I can't seem to get this list to show up correctly in the "estimate details" subform.

Any help is greatly appreciated!

Thank you

 

[jdraw]

Most developers would suggest you start with a clear description of the requirement. Then identify the business subject areas and how they relate to one another(tables and relationships). Many will advise you to not start with forms.

Here's a link with info on many articles, tutorials and videos related to Database Planning and Design.

Good luck.

 

[skyliner33v]

Most developers would suggest you start with a clear description of the requirement. Then identify the business subject areas and how they relate to one another(tables and relationships). Many will advise you to not start with forms.

Here's a link with info on many articles, tutorials and videos related to Database Planning and Design[/URL].

Good luck.

Thank you for the link jdraw. I have the requirements established, and all the appropriate tables built and linked appropriately (or so I believe). I also have some basic forms/subforms built.

My issues is that I am trying to populate a junction table using a pre-determined list of items that should be included in the estimate and I can't seem to get this list to show up for new estimates.

 

[jdraw]

How did you arrive at the pre-determined list of items? Perhaps, you could adapt that process to be used with new estimates???

 

[skyliner33v]

How did you arrive at the pre-determined list of items? Perhaps, you could adapt that process to be used with new estimates???

Maybe my terminology isn't making sense, i apologize. Basically, for any new estimates that are created, they will fall into one of 15 different repair categories. Once the user has selected which repair category the estimate will be for, I will have a form that they can then input data into. These repairs include specific standard items that need to be included in the estimate. So i need to have the form come with a set of pre-populated standard items so that the user just has to input a quanity and unit cost for each of these items.

I get confused on how I can make the form appear with these specific standard items and how to make sure that the quanity and data input for these items stay with the specific estimate.

I hope that all made a little more sense.

 

[jdraw]

You have 15 different Categories (currently--but this could change).
If I understand correctly, you could have a combobox with these 15 repair categories. Once a repair category is selected, you would open a copy of some form. Then user would fill in certain areas with specific info. The data would be saved to table(s).

Is there 1 form per category?
Are some forms used for multiple Categories?

Perhaps you could show us a few examples of what you are trying--probably best way to get a more focused response.

 

[Mark_]

@OP,

From a business perspective, do you have "Set lists" of items that need to be included in estimates? i.e. if it is "Gardening", you include 'cutting','removal','super-grow','trimming','what have you'? Even if not all apply to a given estimate, you would like to prompt for them because they are normally found on that kind of job?

If so, THIS THREAD may give you some ideas. The poster is looking to automate check lists, a similar concept to automating quotes.

 

[skyliner33v]

You have 15 different Categories (currently--but this could change).
If I understand correctly, you could have a combobox with these 15 repair categories. Once a repair category is selected, you would open a copy of some form. Then user would fill in certain areas with specific info. The data would be saved to table(s).

Is there 1 form per category?
Are some forms used for multiple Categories?

Perhaps you could show us a few examples of what you are trying--probably best way to get a more focused response.

Thank you for the reply. I really do appreciate the help. I thought since I was semi-capable with SQL , that I would be capable at Access, but after working on this for the last 2 weeks, I've realized that I don't know as much as what I thought I did.

My database would do basically the following. And before I begin, I should note that in my original post i listed "customers" as a table, when it is actually "bridges". I figured I would just say "customers" as it would be easier for others to understand. But the same concept applies whether I call them "customers" or "bridges".

Whenever a bridge needs to be repaired, a cost estimate needs to be prepared. There are 15 different Repair Categories to choose from, and this number could potentially be increased/reduced. Each repair category contains different Standard Items that are typically associated with that repair. These standard items come from a long list of standard items in their own separate table.

I need a database that a user can go in and select a bridge, and create a new estimate for that bridge. They would then select a repair category, and the form would automatically populate the subform with the list of standard items for that specific repair category. The user would be able to add or delete standard items in this list, as well as input a quantity and unit cost for each of these items.

Attached is a copy of the relationship diagram for the database, as well as a screenshot of the forms that I have created so far.

My problem is that I don't know how to get the combobox to populate the subform with the list of standard items related to that repair category and how to make sure that once a quantity and unit cost for each of these standard items that they would stay related to that specific bridge and estimate number.

I understand how it should work in the tables, but not how everything should be linked up in the forms.

 

[jdraw]

Can you post a copy of the database you have?You'll have to make it a zip file because of your post count. I also suggest you show us a category or 2 with their corresponding standard items.
Note -we are not trying to intrude on your business or set up, but we do need some basics to test/review in order to comment/advise etc. Remove anything confidential/private--you can anonymize things with Porky Pig, Bugs Bunny....Centerville, Upstate etc.

Look at Marks material Link in #7 - similar concept that could be adapted...

 

[skyliner33v]

Can you post a copy of the database you have?You'll have to make it a zip file because of your post count. I also suggest you show us a category or 2 with their corresponding standard items.
Note -we are not trying to intrude on your business or set up, but we do need some basics to test/review in order to comment/advise etc. Remove anything confidential/private--you can anonymize things with Porky Pig, Bugs Bunny....Centerville, Upstate etc.

Look at Marks material Link in #7 - similar concept that could be adapted...

Absolutely! Attached is a copy of the database. I had to take out a lot of the bridges, and standard items in the tables because it made it over the 2mb file size limit, but there's enough in there to get an idea of how everything is structured.

 

[jdraw]

It seems you have not associated Categories and StandardRepairItems in your database??

I would expect a table showing Category and the StandardItems for that Category.

In general conceptually if you have a Form with 2 combo boxes --say Category on Left and StdItems on Right and a button.
You select a Category eg Anchor Cable Replacement
then in the StdItems you select the first associated Item
then click the button.

You have code behind the button to add a record to a table that has the CategoryID and StdItemID.
You do this for each stdItem in Each Category.
You need code behind the button to append a record to the table. You also add a unique composite index on CategoryID and StdItemID to prevent duplicates. The data in this table will be used to pre-populate the estimate form.

It's possible that you removed some data when creating the file for upload.

Here's a draft model but I don't know your business. Review or discard as you see fit.

 

[skyliner33v]

It seems you have not associated Categories and StandardRepairItems in your database??

I would expect a table showing Category and the StandardItems for that Category.

In general conceptually if you have a Form with 2 combo boxes --say Category on Left and StdItems on Right and a button.
You select a Category eg Anchor Cable Replacement
then in the StdItems you select the first associated Item
then click the button.

You have code behind the button to add a record to a table that has the CategoryID and StdItemID.
You do this for each stdItem in Each Category.
You need code behind the button to append a record to the table. You also add a unique composite index on CategoryID and StdItemID to prevent duplicates. The data in this table will be used to pre-populate the estimate form.

It's possible that you removed some data when creating the file for upload.

I just checked and all the tables and relationships are there as I had them before removing the extra data. So maybe I need another table in there that only contains the Standard Items that a is supposed to be in a Repair Category?

Or can I structure the database like the attached picture? If I think about it correctly, the structure would be as follows:

Each Estimate Detail would contain a Repair Category GID and a Standard Item GID. The Repair Category that is associated with that specific estimate would house a single repair category. And that would house the specific Standard Items related to that Repair Category.

And the purpose of including a Standard Item GID in the Estimate Details table would be so that the user could add any other Standard Item to the estimate that isn't in a specific Repair Category.

Would something like that work?

 

[jdraw]

My point was that each Category has certain number of std items.
Suppose you had 10 Categories and 200 std Items.
If you can identify those 13 stdItems that go with Category 2, the 11 stdItems that go with Category 6 etc., then it seems to me you would have an established list of which items go with which category. Several categories may use the same or mostly the same stditems. I think the easiest way to store that info is in a junction table (but you know your details).

The Category may not have to be in the EstimateDetails, but the stdItem for that Category would.
Your estimate detail is based on all relevant stdItems - not the Category itself. And yes - you would add other stdItems related to the RepairJob regardless of what Category it is normally used with.
(Sort of like the set of stdItems that are not in this Category--to get the extras)


I would not change any forms etc until the model as tested and vetted with some sample data and test scenarios.

 

[skyliner33v]

My point was that each Category has certain number of std items.
Suppose you had 10 Categories and 200 std Items.
If you can identify those 13 stdItems that go with Category 2, the 11 stdItems that go with Category 6 etc., then it seems to me you would have an established list of which items go with which category. Several categories may use the same or mostly the same stditems. I think the easiest way to store that info is in a junction table (but you know your details).

The Category would not have to be in the EstimateDetails, but the stdItem for that Category would.
Your estimate detail is based on all relevant stdItems - not the Category itself. And yes - you would add other stdItems related to the RepairJob regardless of what Category it is normally used with.
(Sort of like the set of stdItems that are not in this Category--to get the extras)


I would not change any forms etc until the model as tested and vetted with some sample data and test scenarios.

Ahh I see. I think your model makes sense with using a junction table for the standard items and repair categories. I will give that a shot.

Thank you so much for your help!

 

[jdraw]

Here's another draft model showing keys (not tested)

 

[Mark_]

Take a look at this and let me know if it is similar to what you need. As you are working on bridges, I know that some standard items will be repeated with different time components.

Pretty ugly, but should demonstrate what you would want to build.

 

[Pat Hartman]

I can't download the samples so I'm sorry if someone has already given you the answer. This is a fairly common request that is handled by a simple append query. I've don't it in applications that manage attendance. Isn't it better to run an append query to add 30 present's to the attended table and delete the two absences than to have to add 28 attendance records one at a time? I've also used it in surveys. You add the survey taker header and then an append query appends the empty answer records for the 60 questions of the survey being taken. In this case deletes are not allowed. Only adds.

So create an append query. The where clause needs to select the source of the list. Then the append part maps field by field and the foreign key field is filled by referencing the form control that holds the FK for the set of records for the new bridge.

 

[Mark_]

@Pat,

Unless he's doing something rather strange he's going to have some standard items that would appear multiple times on the same quote. Depending on depth, the same length of trenching can have very different costs. Likewise putting in itemized quotes often requires the same item to be repeated in differing phased of the project.

 

[skyliner33v]

Here's another draft model showing keys (not tested)

I tried creating the links that you have shown on your v2 diagram, but i get an error stating "No unique index found for the reference field of the primary table". I think this is because the StdItemsPerCategory Table is a junction table and they are only foreign keys to the Repair Category and Standard Item tables. So I can't link them to the foreign keys in the Estimate Details table.

May I see the data types of your tables so that I can see how they are structured?

@Pat,

Unless he's doing something rather strange he's going to have some standard items that would appear multiple times on the same quote. Depending on depth, the same length of trenching can have very different costs. Likewise putting in itemized quotes often requires the same item to be repeated in differing phased of the project.

This is really close to what I am wanting to achieve. But there will only be one Item per estimate, so there is no need to be able to include the same Item within an estimate.

I can't download the samples so I'm sorry if someone has already given you the answer. This is a fairly common request that is handled by a simple append query. I've don't it in applications that manage attendance. Isn't it better to run an append query to add 30 present's to the attended table and delete the two absences than to have to add 28 attendance records one at a time? I've also used it in surveys. You add the survey taker header and then an append query appends the empty answer records for the 60 questions of the survey being taken. In this case deletes are not allowed. Only adds.

So create an append query. The where clause needs to select the source of the list. Then the append part maps field by field and the foreign key field is filled by referencing the form control that holds the FK for the set of records for the new bridge.

I think this is the part that I am missing. I need some kind of append query that will write the Repair Category ID and Standard Item ID to the Estimate Details table. For some reason I couldn't think of how these items get associated to the correct bridge and estimate number.

Here's what I think I need now, based on all of your help.


A blank "New Estimate" form where a bridge, from the Bridges Table, can be selected, as well as a Repair Category from the Repair Categories Table can be selected. Then a button that is an append query that will append the associated Repair Category ID and Standard Item ID to the Estimate Details table. From there the user can add in the quantity and unit cost information for each Standard Item.

I would also need an "Add Item" button that will open up a new form so that the user can add any Standard Item from the Standard Items table. Then a button would append the appropriate Standard Item ID to the Estimate Details table.

Does this sound like it would work? And what would the table relationship look like?

Thank you all for the help!

 

[Mark_]

@Mark This is really close to what I am wanting to achieve. But there will only be one Item per estimate, so there is no need to be able to include the same Item within an estimate.

Are you bundling multiple items as a single line item in your quote? The civil engineers I know always had to list each item separately on any estimate and include both "Expected start" and "Expected Completion" dates.

Trenching alone could appear multiple times, especially when utilities were involved along with drainage. Even if you do not need it now, I would keep the ability to have more than one entry of a given type.

Then again the people I was dealing with had to adhere to government requirements for all proposals. Some of it became almost comic in what they had to list.