Need to get the highest cost from 1 record (1 Viewer)

[vbbbmom]

I have a material table, and each record consists of the following:

ID
Material Description
Vendor1 Cost
Vendor2 Cost
Vendor3 Cost

What I need to get is the highest number out of the 3 cost fields, so say I have one record (see below):

ID: 7 Material Description: Colonial Casing Vendor1 Cost: .35 Vendor2 Cost: .45 Vendor3 Cost: .39

I need to grab the .45 and use that number as the sellcost field

how would I go about getting this information? I reviewed some information regarding DMax but I have never used DMax and from the examples I reviewed it seemed to be based on the max value of a table of records and I need the max value of one record? Do I create a Module/Function and what is the code? can this be done as a data macro by adding maybe a calculated field within the table ?

Any help would be greatly appreciated.

sj

 

[Galaxiom]

You can do it with nested IIFs but the real problem is your data structure. The costs should be in a related table with one record for each cost rather than separate fields. Then the solution is a simple Max function. (Also you can have as many vendors as you care to include rather than being limited to three.)

If it is practical within the constraints you are working under then you should really look at changing the structure.

 

[vbbbmom]

Galaxiom, Thank you!

I restructured my tables and I created a query called MaxValue, it is a totals query that gives the highest value. Now what I need to happen is the value that I get from this query, I need to somehow transfer that value into a table field and I am not sure what the best way to do this is? Can you help me with this?

 

[Galaxiom]

As you will have noticed, like all aggregate queries the Max prevents any query based on it from being updateable.

There are a few ways to deal with this. The result can be written to a temporary table. However that is often considered a bit of a clumsy technique.

Another alternative is to get the value by using a DMax function instead of the aggregate query. This expression can be used as the new value in an Insert or Update query (depending if you are changing an existing record or writing a new one).

Another variation is using VBA to run the DMax or open a recordset based on your query then read the value into a variable. Then use that value to either supply a parameter to a saved update or insert query or concatenate the value into an SQL string for a dynamic query and Execute it.

 

[vbbbmom]

OK, I am gonna need your help with this. I am pretty novice when it comes to functions.

Basically I need to find the value during the process of entering a new record. So I guess we should use the DMax function and then use the insert query?

Maybe we should do one step at a time, can you explain to me how to go about creating the DMax Function, is it using the function within the query or am I crating a stored procedure or module?

 

[JLCantara]

Hi vbbbmom,

Basically I need to find the value during the process of entering a new record.

So you are using a form for data entry?

Now what I need to happen is the value that I get from this query, I need to somehow transfer that value into a table field

Is this unknown table's record already exists?

JLCantara.

 

[vbbbmom]

JLCantara,

The answer is yes.....I have restructured my project per the request of Galaxiom so I now have 3 tables, the main table contains the material ID and the material description. The 2nd table is the vendor cost table which contans the material ID, the vendor name and the cost of the material for that vendor. Since we use several vendors each item can have more than one cost in this table. What I need to make sure is that when we quote the job we quote it at the highest cost for the item. So my 3rd table is my material details table which is job sepecific, it contains job ID, material id, and needs to contain a cost which will be the highest cost between all the vendors for that specific item.

Hopefully I have explained this so you can understand what I am trying to do.

Thank you for considering to help me.

 

[JLCantara]

Hi VBBBMOM,

I have linked an Access 2003 prototype of your application.

I have added a table linking Materials and Vendors, in case of...

Notice the use of timer: the normal procedure for SubForm/Form data transfer does not work!?!?!

Here is a page explaining this stuff:
http://http://www.mvps.org/access/forms/frm0031.htm

Check the app. then give me your opinion.

JLCantara.

 

[vbbbmom]

JLCantara,

Thank you for helping me, but I am sorry I do not see the prototype attached?

 

[JLCantara]

Oops I forgot to attach the db....

Here it is.

JLCantara.