Each subcontractor is broken down by discipline. There are tank subs, stage subs, paint subs, sandblast subs, etc. So when subs turn in their quotes it is for a particular discipline broken down per work item. for example: a stage sub will send one quote that has all the stage work items and the quote will be broken down work item by work item showing what they would charge for man hours, material, and total for each work item within that discipline. Quotes can and will change depending on how often the scope of work changes for a particlular work item. There are 250 subcontactors that we solicit for work and out of those 250 each sub only does work on disciplines that they are experts at. Does that answer your question?
Access 2007 Query Question
- Thread starter lowanam1
- Start date
You asked this earlier:
The reason why I am asking all of the questions, is because I am trying to figure out how to automate the quote & work item addition.
What I am thinking is to have an unbound form with two multi-select list boxes. One list box would be for the quotes and the other for the disciplines. A button on the form would pull the selections from both append the appropriate records to the tblSubcontractorQuotes table via Visual Basic for application code. I'm just not sure how to filter those list boxes. We should be able to filter down to the applicable disciplines by using a combo box for the work items since there is a relationship there; I just do not know how to filter on the quote side since no current relationship exists between the subcontractor and the work items or the discipline.
You say this:
The reason why I am asking all of the questions, is because I am trying to figure out how to automate the quote & work item addition.
What I am thinking is to have an unbound form with two multi-select list boxes. One list box would be for the quotes and the other for the disciplines. A button on the form would pull the selections from both append the appropriate records to the tblSubcontractorQuotes table via Visual Basic for application code. I'm just not sure how to filter those list boxes. We should be able to filter down to the applicable disciplines by using a combo box for the work items since there is a relationship there; I just do not know how to filter on the quote side since no current relationship exists between the subcontractor and the work items or the discipline.
You say this:
. Where do you show this relationship in your current tables?
I am thinking that you need to define the discipline for each subcontractor so that you can filter their quotes in order to match to the disciplines of the work items
tblSubcontractors
-CompanyName primary key
-CompanyAddress
-fkDisciplineID foreign key to tblDisciplines (long integer number datatype field)
tblDisciplines
-pkDisciplineID primary key, autonumber
-txtDiscipline
The above table would include the various types of subs, each as a record:
tank, stage, paint, sandblast etc.
I think you might have to modify the following table as well. I assume that the Sub discipline field tells you whether it is tank, stage, paint, sandblast etc. Am I correct in my guess?
tblWorkRequired
-pk=Reference Number
-txt=Work Item Number
-txt=Sub Discipline
-txt=Turnkey
-txt=Make Buy Sub
-txt=Have to Sub
-dte=Date of Revision
-txt=Type of Contract
Replace the discipline field with a foreign key reference to tblDisciplines
tblWorkRequired
-pk=Reference Number
-txt=Work Item Number
-fkDisciplineID foreign key to tblDisciplines (long integer number datatype field)
-txt=Turnkey
-txt=Make Buy Sub
-txt=Have to Sub
-dte=Date of Revision
-txt=Type of Contract
tblSubcontractors
-CompanyName primary key
-CompanyAddress
-fkDisciplineID foreign key to tblDisciplines (long integer number datatype field)
tblDisciplines
-pkDisciplineID primary key, autonumber
-txtDiscipline
The above table would include the various types of subs, each as a record:
tank, stage, paint, sandblast etc.
I think you might have to modify the following table as well. I assume that the Sub discipline field tells you whether it is tank, stage, paint, sandblast etc. Am I correct in my guess?
tblWorkRequired
-pk=Reference Number
-txt=Work Item Number
-txt=Sub Discipline
-txt=Turnkey
-txt=Make Buy Sub
-txt=Have to Sub
-dte=Date of Revision
-txt=Type of Contract
Replace the discipline field with a foreign key reference to tblDisciplines
tblWorkRequired
-pk=Reference Number
-txt=Work Item Number
-fkDisciplineID foreign key to tblDisciplines (long integer number datatype field)
-txt=Turnkey
-txt=Make Buy Sub
-txt=Have to Sub
-dte=Date of Revision
-txt=Type of Contract
You should be joining the tables by the ID field not the text field. Also, where is the discipline in the company/subcontractor table?
BTW, instead of taking a photo, maximize the relationship window then use Shift+PrtScn. Then paste into a Word document and post the Word document
BTW, instead of taking a photo, maximize the relationship window then use Shift+PrtScn. Then paste into a Word document and post the Word document
Datasheet view would be fine for viewing existing records and/or when you are adding records manually, but I was referring to the form that would be used for automating the process of adding larger groups of records at a time. In that form as mentioned previously, I see a couple of list boxes, one for the quotes and one for the required work disciplines.
Since doing this will require Visual Basic for Application (VBA) code, it would be best to work with your actual tables & fields since the naming has to be exact in VBA for anything to work correctly. So, is there some way that you can post your current database with any sensitive data altered or removed?
Your submission could not be processed because a security token was missing.
If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.
This is the error that I get when I try to upload. I could email it to you?
If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.
This is the error that I get when I try to upload. I could email it to you?
I tried that doesn't work either....so I can give you the naming conventions...and you can walk me through how to put the code? Will that work ? Thanks
This is exactly how I have the tables named:
tbl_Disciplines
tbl_RateTypes
tbl_SubcontractorQuotes
tbl_SubcontractorRates
tbl_Subcontractors
tbl_WorkItems
tbl_WorkRequired
This is exactly how I have the tables named:
tbl_Disciplines
tbl_RateTypes
tbl_SubcontractorQuotes
tbl_SubcontractorRates
tbl_Subcontractors
tbl_WorkItems
tbl_WorkRequired
I would start with an unbound form. Add 2 multiselect list boxes to the form. One list box should be based on the required work while the other would be based on the quotes. You will need to include the key fields of both tables in the listbox, but you can hide the value to the user. When you set the multiselect property of the list boxes, you want to choose extended (not simple).
You may want to include a combo box based on the category to filter both the quotes and the required work listboxes so that only relevant quotes/required work are shown.
Then add a button to the form. The code will go in the on click event of that button.
I'll let you get that part set up first.
You may want to include a combo box based on the category to filter both the quotes and the required work listboxes so that only relevant quotes/required work are shown.
Then add a button to the form. The code will go in the on click event of that button.
I'll let you get that part set up first.
Ok i came up with an idea to upload my sample db to the internet because I have no idea what you are talking about lol :banghead:
Here is the site:
www.drivehq.com
my login is: lowanam1
password : shiprepair2009
Here is the site:
www.drivehq.com
my login is: lowanam1
password : shiprepair2009
