conditional query

[H.S.SHASTRI]

Hi,
I am new to access. Presently I am working on a small data base. My Table contains following fields.
Part Number , Estimated Cost ,Supplier , Supplier Quote,Settled Quote.

Part No	Estimated Cost	Supplier	Supplier Quote	Settled Quote
1000	10	A	12	9
1000	10	A	13	9
1001	50	D
1002	20	B	24	15
1002	20	C	22	15
1002	20	A	22	15

What I am looking for is to calculate B.O.M cost with logic , consider cost if settled then settled else min of supplier quote else estimated in case no supplier has yet quoted.

Regards
H S Shastri

[isladogs]

What I am looking for is to calculate B.O.M cost with logic , consider cost if settled then settled else min of supplier quote else estimated in case no supplier has yet quoted.

I have absolutely no idea what that means or how it relates to the data you provided.
Others may understand but B.O.M. doesn't mean anything to me either

Wikipedia gave me the following:

• Bureau of Meteorology, Australia
• BOM (psychedelic) (3,4,5,beta-tetramethoxyphenethylamine), a psychedelic drug
• BOM (file format), a file format used in OS X installer packages
• Browser Object Model, the objects exposed by a Web browser
• Byte order mark (U+FEFF), a Unicode character

None of those look relevant to me
If you want someone to give an informed answer, I suggest you explain in a different way

It might help if you state the expected results for that data

[CJ_London]

Bill Of Materials, but not clear what is meant by settled cost, don't see why a supplier would quote a price then settle for a lower price.

However I agree, not clear what is being asked in terms of the calculation. How do you determine if settled? a value in the settled field?

what would your calculation show for the example data you provided?

Also concerned the table design does not look normalised, even with such few fields.

[orange]

Welcome to the forum.

I agree with the other responders --your issue isn't clear.
It is often a good strategy to describe your "requirement" in simple, plain English. Don't show readers you table or fields (or worse yet a "concept" sample with made up data).

A "wild" guess at your question and preamble(based on BOM as Bill of Materials) is:

I have a small business where we assemble/build products. A Product may consist of many parts.
I purchase Parts from Suppliers. Multiple Suppliers may offer the same Parts. I request Estimated Costs for such Parts from different Suppliers, and often purchase based on lowest cost.
Also, for each Product I maintain a Parts hierarchy. I would like to automate how to determine a Product cost based on the Parts hierarchy and settled Quote costs. My question relates to the design of the hierarchy and method to traverse the Parts to calculate costs. I would appreciate any info.


Here is info on a hierarchy that may be helpful.

Good luck with your project.

You might also try Google search for relational database hierarchical data structure

[June7]

So not only do you need to compare data within a record, need to compare data with other records for same part.

SELECT PartNo, MIN(Nz(SettledQuote, Nz(SupplierQuote, Nz(Estimated, 0))) AS Cost FROM tablename GROUP BY PartNo;

However, that will not get you the Supplier and if you need that it gets a little complicated.


Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.