Alright, so I got a very unique issue. It combines a little bit of SQL, VB.net Skill, and patience. The program I am trying to make needs to be structured like this.

Table 1: Estimates (Details View)
Customer ID
Employee ID
Form Factor
Problem
Proposed Solution
Estimate Acceptance (Boolean)

Table 2: PartsList (Grid View)
Part Number
Part Name
Part Cost

Now, the trick is, I need to have it where each Estimate can have its own individual parts list. It needs to be this was as each estimate could have just one part, or many parts. How can I do this effectively? I already have the tables and the layout of the app, I just need to get it all connected up. Thanks!!!

Unhnd_Exception commented: Don't post unanswerable questions . -2

Recommended Answers

All 4 Replies

Member Avatar for Unhnd_Exception

I would change the PartsList table name to Parts and add a PartID Column. Add an EstimateID Column to the Estimates table. Then Create a new table named PartsList with a ID, EstimateID, and PartID column.

If an estimate had an Id of 4 and had three parts with ids of 2,4,6 then the PartsList table would look like:

ID EstimateID PartID
1 4 2
2 4 4
3 4 6


To get the parts for the estimate you can join the Parts and PartsList table on the EstimateID.

"Select Parts.PartNumber,Parts.PartName,Parts.PartCost" & vbCrLf & _
"From Parts" & vbCrLf & _
"Inner Join  PartsList" & vbCrLf & _
"On PartsList.PartID = Parts.PartID" & vbCrLf & _
"Where PartsList.EstimateID = @EstimateID"

Just one way of doing it. Maybe it will give you some other ideas.

Okay, Is there any way that I can get it to show the customers information say in fields on the right side of the window and then the products and estimates in the left side of the window? I get the idea of ID'ing the tables and having a master table to display the information, good concept, but could confuse the end-user having to remember the ID's. Thanks for the reply, have a nice day.

Member Avatar for Unhnd_Exception

The Id's shouldn't be part of the end users life. They are for you the programmer to be able to identify items in different places and put them together.

It is possible to get all the parts for the customer just by using the customers name. Get the customerId from the customers table with the customers name. Get the estimateId from the estimates table with the customerID. get the parts as above with the estimateId.

It can all be done with table joins.

Okay, I will be looking more into table joins. Stay tuned here and I will post problematic code and so forth. Thanks for the help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.