Thread: Design/Logic Help

Hi All,

I need some advice on how to set this up but also another question about how I would like it to work on web application.

I will have a list of tools (each tool will have a # and a cost). So my table will look something like - ToolID, ToolName, Tool#, Cost.

But then allow on web application to allow a user to create a tool build which is something that contains multiple tools. So tool build#1 my contain tools (#1, #5, #7, #8)...and in tool build#2 my contain tools (#1, #5, #11, #24). Each build will also contain a qty so in toolbuild#1, it may take 2-#1 tools. So would the table look something like - ToolBuildID, ToolID, BuildName, Qty? And if yes, do you know of any example of how this type of logic would work on web application? (user would create a toolbuild record from tools)

Thanks for any advice/help!

Hey Rebelle,

You need 2 extra tables:

1. A table to define a group
TOOL_GROUP
-group_id
-group_name
-group_price
-etc

2. a relational table to link tools to tool groups, each tool in a group represents 1 row in this table
TOOL_GROUP_ITEMS
-group_id
-tool_id

Then to display the groups, query the tool_group table INNER JOIN tool_group_items.group_id to tool_group.group_id, INNER JOIN tools.tool_id to tool_group_items.tool_id, order by group_id, tool_id.

Hi dr_rock,

I think I may have something like you suggested on tblbuildmaster example but tell me if i'm wrong.

I attached everything I have but with questions of what I would like to do/have.

It's a headache...time for some caffeine now.

Thanks!

After reviewing more what I on the attachment, if I make a screen like below:

screen to create new buildname:
dropdownlist ProductLine
dropdownlist Category
Textbox BuildName

with an insert into tblBuilds catID and generate newbuildID

so now that I have a new build (buildID) in tblBuilds (BuildID, BuildNm, CatID).....after inserting into tblBuilds how can i do an insert to into tblDetails for every district that exists and also each type 1 and 2? so let's say there are 10 districts, everytime a newbuild is created it would then insert 20 records into tblDetails, once for each district and one for each type (1 / 2)...

records:
district 1 - build1 - q1 - q2 - q3- q4 - type1
district 1 - build1 - q1 - q2 - q3- q4 - type2
district 2 - build1 - q1 - q2 - q3- q4 - type1
district 2 - build1 - q1 - q2 - q3- q4 - type2
district 3 - build1 - q1 - q2 - q3- q4 - type1
district 3 - build1 - q1 - q2 - q3- q4 - type2
district 4 - build1 - q1 - q2 - q3- q4 - type1
district 4 - build1 - q1 - q2 - q3- q4 - type2
district 5 - build1 - q1 - q2 - q3- q4 - type1
district 5 - build1 - q1 - q2 - q3- q4 - type2
district 6 - build1 - q1 - q2 - q3- q4 - type1
district 6 - build1 - q1 - q2 - q3- q4 - type2
district 7 - build1 - q1 - q2 - q3- q4 - type1
district 7 - build1 - q1 - q2 - q3- q4 - type2
district 8 - build1 - q1 - q2 - q3- q4 - type1
district 8 - build1 - q1 - q2 - q3- q4 - type2
district 9 - build1 - q1 - q2 - q3- q4 - type1
district 9 - build1 - q1 - q2 - q3- q4 - type2
district 10 - build1 - q1 - q2 - q3- q4 - type1
district 10 - build1 - q1 - q2 - q3- q4 - type2

Hmm.. I am a little confused but then I did just wake up..... I think I know what you are asking, it may be best done in a stored proc (assuming you have the facilities)

Assuming we have a table of districts with x rows, we also have tblBuilds and tblDetails as you described. We also have your form with a title field and two property fields.

When you submit the form you want to enter into tblDetails the title one of the selected properties and a district name, which results in 2 rows per district (one for each property).

...could be way off here, hopefully not...

Once the form is submitted and you catch it assign the title and the two properties to variables you need to iterate through all districts and build two insert statements for each, these can be appended together separated with a ";" so that you don't send unnecessary calls to the DB.

e.g.

Code:

Dim strTitle : strTitle = Request("a") 'BuildName
Dim intProperty1 : intProperty1 = Request("b") 'ProductLine
Dim intProperty2 : intProperty2 = Request("c") 'Category
Dim strSQL, strConcat 
strConcat =""
strSQL = "Select * from Districts"
'open DB etc.
rstDistricts = adoConn.Execute("strSQL")

Do Until rstDistricts.EOF
    
    strConcat = strConcat & "Insert Into tblDetails (...) Values (strTitle, intProperty1, "&rstDistricts("district")&");" &_
    "Insert Into tblDetails (...) Values (strTitle, intProperty2, "&rstDistricts("district")&");"
    rstDistricts.MoveNext
Loop
adoConn.Execute(strConcat)
'close rstDistricts 
'close DB

Am I close? not sure what the q1 - q2 - q3- q4 represents so ill wait for a response

Hi dr_rock,

Ok...I think pretty close for the most part....I've never done a stored procedure before but I'll see if I can do it with this task. The q1 - q2 etc are the detail data for each district. It's an int field where they will enter a number (1st quarter, 2nd quarter...so on).

Each district will enter a quantity and then the detail on the build will be another type of detail which should be calculated off the # they enter on the main detail.

Ex: if I take item#1 and enter 3 in 1st quarter for district 1. then when I click on item#1 to expand the Build data the build data will house many items with a qty and i will want it to calculate the build qty x the 3.

I'll work on it and try to see how far I can get and I'll be back. I just wasn't sure about the screen and populating the tables via the GUI instead of manually entering it.

Thanks Dr_Rock!

Rebelle