Access 2007 Query Question

lowanam1

Registered User.
Local time
Today, 05:25
Joined
Jan 13, 2012
Messages
106
The table that I need to make a query for is a quote table for subcontractors winning and competition quotes.
I need to make a query that pulls the "Winning Bid" and "Winning Bid 2" from each row. The problem that I am having is that since there are 8 fields (Quote status 1-8)for the "Winning Bid" I can't get the query to pull only the winning bid. There can be up to 8 quotes entered into the db for each row. I don't know how to use the criteria to show all the winning bid and winning bid 2's (There can be 2 winning bid's per row). . Any help would be appreciated. Thank you !
 
The problem that I am having is that since there are 8 fields (Quote status 1-8)for the "Winning Bid" I can't get the query to pull only the winning bid. There can be up to 8 quotes entered into the db for each row. I don't know how to use the criteria to show all the winning bid and winning bid 2's (There can be 2 winning bid's per row)

It sounds like you have a table design flaw. You should not have fields like;

Bid1
Bid2
...
...
Bid8

The bids should be in a separate, related table and each bid should be in it's own row with additional fields to indicate which job/contract the bid is related to and the bid status (ie it was the 1st place bid, 2nd place bid, etc.).

Can you provide more details about your table structure?
 
Do you only have 1 table? Are you saying that each quote is contained in a field of 1 record/row so that you have many fields each with a quote?

Can you post your table with the fieldnames, similar to the following:

tablename
-field1name
-field2name
etc.
 
I have one table for the quotes. The structure of the table is as follows. Sub Name 1, Quote Status 1, Hours 1, Material 1, Total 1...thru Sub Name 8, Quote Status 8, Hours 8, Material 8, total 8
 
Having 8 groups of similarly named fields is an example of Repeating Groups which is a clear indication that your database structure is not normalized. Without a normalized structure, you will have problems querying the database just as you describe. So, you have to fix your table structure.

For more on the rules of normalization, it would be best to check out this site. The content of that website might be hard to understand, so this site also might be helpful.

I assume that you are getting multiple quotes for a project (a guess here). That describes a one (project)-to-many (quotes) relationship which by normalization rules requires 2 tables. Each quote would be a RECORD not a field in the 2nd table

First, a table to hold the basics of the project

tblProjects
-pkProjectID primary key, autonumber
-txtProjectNumber
-txtProjectName

Now a table to hold the bids. For example, if you get 8 bids on project 1, you would have 8 RECORDS (i.e. rows) in the following table

tblProjectQuotes
-pkProjQuoteID primary key, autonumber
-fkProjectID foreign key to tblProjects
-txtBidderName

Now if a quote consists of different parts, that describes another one-to-many relationship.

Could you provide some example data for a typical bid so that we can see what you are dealing with? From there we will be better able to help you design the proper table structure.
 
@jwzp22 Thank you that makes alot more sense.
Here is an example of a typical bid.

The projects are broken down by a work item number (123-12-1212), For each work item there could be up to 20 different disciplines that need to go out for a quote i.e. Stage-123-12-1212, Tank-123-12-1212, Crane-123-12-1212, NDT, 123-12-1212....etc. For each of those disciplines there could be up to 8 quotes that are provided by 8 different companies. Of those 8 it is possible that 2 companies will split the work in other words they are both winners of that discipline/work item. I need a query that will show the winning bids for each work item/discipline with the totals of each quote. I hope that I am making sense. Please let me know if you have any questions. Your help is greatly appreciated.
 
For each work item there could be up to 20 different disciplines

The above describes a one-to-many relationship.

What I am not clear on is whether a project can have many work items? Can it? If so, then this would be the structure

tblProjects
-pkProjectID primary key, autonumber
-txtProjectNumber
-txtProjectName

tblProjectWorkItems
-pkProjWIID primary key, autonumber
-fkProjectID foreign key to tblProjects
-txtWorkItemNumber

Since a work item has many disciplines that describes a one-to-many relationship as already mentioned above. So that table would look like this:

tblProjectWorkItemDisciplines
-pkProjWIDiscID primary key, autonumber
-fkProjWIID foreign key to tblProjectWorkItems
-txtDiscipline


But...

If a discipline can also apply to many work items that describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (work items & disciplines in this case), you have a many-to-many relationship which requires a junction table. If this is indeed applicable to your application (a guess on my part), you need a table to hold the disciplines and you would make tblProjectWorkItemDisciplines your junction table. That would look like this:


tblDisciplines
-pkDiscID primary key, autonumber
-txtDiscipline

tblProjectWorkItemDisciplines
-pkProjWIDiscID primary key, autonumber
-fkProjWIID foreign key to tblProjectWorkItems
-fkDiscID foreign key to tblDisciplines

For the bidders, it would be best to hold the basic company info in a table

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress

You said that you can have many bidders on a particular discipline. I must also assume that a bidder can bid on more than one discipline. This again describes a many-to-many relationship, so we need another junction table


tblBidderProjectWorkItemDisciplines
-pkBidderPWIDiscID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-fkProjWIDiscID foreign key to tblProjectWorkItemDisciplines
-currBidAmount
-spAwardPercent


You can use the spAwardPercent (single precision number field) to indicate the percentage (as a fraction) of the particular discipline that goes to a particular bidder. i.e. 0.50 (50% to bidder 1) and 0.50 to bidder 2 for a total of 1.00 (100%).
 
ok I got the above running great. The only problem is...In one project there can be up to 400 work items. For each work item there can be up to 15 disciplines and for every discipline/work item there can be up to 8 quotes. So I'm finding that I have to enter the discipline/work item 8 times for each different quote into the last table....or is there a way to auto populate it? It seems too time consuming. Or maybe I'm doing something wrong? Please help :)
 
Yes, you can automate it. Once you define the first, you can pull those items (i.e. a query) and run an append query to associate them with another quote. I would start with a standard SELECT query to see if you can isolate the records that are common to each quote. From there you should be able to change the query type from SELECT to APPEND query.
 
OK still working on how to figure out how to use append queries. thanks so much
 
i have a select query set up and broken down by discipline "qry_Stage Quotes" however i can not get the append function to work without freezing the query so that new information can be entered..also how do i get the work item to associate with the other quotes? Am i doing something wrong? Please advise. Thanks so much
 
The key is when you want to do this. Do you want to do this when each quote is created or after several related quotes have been created? We will also need to see your current table structure so posting the database might be helpful (sensitive data should be removed or altered)
 
When each quote is created is ideal. I am unable to upload a sample at this time. :(

The key is when you want to do this. Do you want to do this when each quote is created or after several related quotes have been created? We will also need to see your current table structure so posting the database might be helpful (sensitive data should be removed or altered)
 
Understood. Could you just post the relevant tables/fields in the following format:

tblDisciplines
-pkDiscID primary key, autonumber
-txtDiscipline

tblProjectWorkItemDisciplines
-pkProjWIDiscID primary key, autonumber
-fkProjWIID foreign key to tblProjectWorkItems
-fkDiscID foreign key to tblDisciplines


tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
-txtAddress
 
tblWorkItems
-pk=Work Item Number
-txt=Estimator
-txt=Work Item Status
-txt=Specification Status
-txt=Package Type
-dte=Rec'd Date
-txt=Smart Start
-txt=Critical Path
-txt=Work Item Title
-txt=Specification
-txt=Amendment
-txt=Allocation after PMO

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

tblSubcontractorQuotes
-pk=Reference Number
-fk=Company Name
-txt=Quote Status
-txt=Man Hours
-txt=Material
-txt=Total
-txt=Sub Exceptions
-txt=Award Justification
-txt=Award Percent

tblSubcontractors
-pk=Company Name
-txt=Company Address
-txt=Company ST Rates
-txt=Company OT Rates
-txt=Company DT Rates

Here are my tables. Let me know how that looks. Thanks so much
 
A general recommendation first, it is best not to have spaces or special characters in your table or field names. Having them forces you to enclose the names in square brackets when you are creating queries. It is a lot of extra typing. Also, it can impact coding if/when you get into that aspect.

Also, most relational databases are more efficient when key fields are numeric rather than text values.

I do see an issue with tblSubcontractors. If a subcontractor has many rates ST, OT, DT etc. that describes a one-to-many relationship, so the various rates should be in a related table. Also, just from a logic viewpoint, the tblSubcontractors should just contain the basic info about the subcontractor such as name address, phone # etc.

tblSubcontractors
-CompanyName primary key
-CompanyAddress

tblSubcontractorRates
-pkSubRatesID primary key, autonumber
-fkCompanyName foreign key to tblSubcontractors
-fkRateTypeID foreign key to tblRateTypes
-RateValue

tblRateTypes (3 records in this table: ST, OT, DT)
-pkRateTypeID primary key, autonumber
-txtRateType

Going back to the quotes and the append query, I'm a little lost. You said that for each work item there can be many disciplines. Does the tblWorkRequired hold the related disciplines to a work item?

Next, you mentioned that you get multiple quotes for each discipline. I assume that you are trying to capture that in tblSubcontractorQuotes, but I do not see a foreign key to the discipline (i.e. required work):

tblSubcontractorQuotes
-pk=Reference Number
-fk=Company Name
-txt=Quote Status
-txt=Man Hours
-txt=Material
-txt=Total
-txt=Sub Exceptions
-txt=Award Justification
-txt=Award Percent

Shouldn't the table look like this?

tblSubcontractorQuotes
-pk=SubcontractorQuoteID primary key, autonumber
-fk=Reference Number foreign key to tblWorkRequired
-fk=Company Name foreign key to tblSubcontractors
-txt=Quote Status
-txt=Man Hours
-txt=Material
-txt=Total
-txt=Sub Exceptions
-txt=Award Justification
-txt=Award Percent

Also, what type of data is in the Man hours, material, total and award percent fields? If it is numeric or currency, why do you have them identified as text fields? Regarding the total field, is the value inputted calculated from other values provided? If so, then you would not store the total, but calculate it on the fly when you need it.

The one thing I think we may be missing is how do you know to which work item the disciplines and thus the quotes are associated?

I think we may need a table to associate the subcontractor with the work item and then join that table to the quote table. You'll have to explain your process surrounding that since it will impact the append query
 
Ok I rearranged my tables to reflect all of your advice. The way that I related the work items and disciplines were by the reference number in the subcontractor quote table and the work required table. i.e. stage63111001, tank64180001....is that a good way? Please advise. Thanks again
 
tblSubcontractorQuotes
-pk=SubcontractorQuoteID primary key, autonumber
-fk=Reference Number foreign key to tblWorkRequired
-fk=Company Name foreign key to tblSubcontractors
-txt=Quote Status
-nmb=Man Hours
-cur=Material
-cur=Total
-txt=Sub Exceptions
-txt=Award Justification
-txt=Award Percent

tblSubcontractors
-CompanyName primary key
-CompanyAddress

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

blWorkItems
-pk=Work Item Number
-txt=Estimator
-txt=Work Item Status
-txt=Specification Status
-txt=Package Type
-dte=Rec'd Date
-txt=Smart Start
-txt=Critical Path
-txt=Work Item Title
-txt=Specification
-txt=Amendment
-txt=Allocation after PMO

the reference number from the tbl_subcontractorquotes and tbl_workrequired is a number that reads like.....i.e. stage63111001, tank64180001

does this help?
 
When a subcontractor submits a quote, does the quote cover multiple work items which in turn cover multiple disciplines (i.e. required work items)? Do you ever need to review a subcontractor quotes over multiple required work disciplines over many work items? Can a subcontractor only provide a quote for only certain disciplines (but not all) within work item?
 

Users who are viewing this thread

Back
Top Bottom