How to use the same exact code in my VBA on Access? (1 Viewer)

[luzz]

I used the codes below in my Excel to calculate my gross weight and would like to use the same code in Access to calculate the gross weight.
The variable check refers to LBS
and use refers to nettweight

Thank you!

Function myfunc(check As Range, use As Range, ref As Range, ctn As Range) As Variant

Dim grosswt As Double

'lbs<130

If check.Value <= 130 And ref.Value = "Bio" Then
grosswt = (use.Value + 20) * (102 / 100)

ElseIf check.Value <= 130 And ref.Value = "Presetting" Then
grosswt = (use.Value + 20) * (101 / 100)

ElseIf check.Value <= 130 And ref.Value = "Sueded" Then
grosswt = (use.Value + 20) * (101 / 100)

ElseIf check.Value <= 130 And ref.Value = "Brushing" Then
grosswt = (use.Value + 20) * (115 / 100)

ElseIf check.Value <= 130 And ref.Value = "Printing" Then
grosswt = (use.Value + 20) * (101 / 100)

'lbs between 131 and 200

ElseIf check.Value >= 131 And check.Value <= 200 And ref.Value = "Bio" Then
grosswt = ((use.Value * 1.15) + 15) * (102 / 100)

ElseIf check.Value >= 131 And check.Value <= 200 And ref.Value = "Presetting" Then
grosswt = ((use.Value * 1.15) + 15) * (101 / 100)

ElseIf check.Value >= 131 And check.Value <= 200 And ref.Value = "Sueded" Then
grosswt = ((use.Value * 1.15) + 15) * (101 / 100)

ElseIf check.Value >= 131 And check.Value <= 200 And ref.Value = "Brushing" Then
grosswt = ((use.Value * 1.15) + 15) * (115 / 100)

ElseIf check.Value >= 131 And check.Value <= 200 And ref.Value = "Printing" Then
grosswt = ((use.Value * 1.15) + 15) * (101 / 100)

'lbs between 201 and 500

ElseIf check.Value >= 201 And check.Value <= 500 And ref.Value = "Bio" Then
grosswt = ((use.Value * 1.1) + 10) * (102 / 100)

ElseIf check.Value >= 201 And check.Value <= 500 And ref.Value = "Presetting" Then
grosswt = ((use.Value * 1.1) + 10) * (101 / 100)

ElseIf check.Value >= 201 And check.Value <= 500 And ref.Value = "Sueded" Then
grosswt = ((use.Value * 1.1) + 10) * (101 / 100)

ElseIf check.Value >= 201 And check.Value <= 500 And ref.Value = "Brushing" Then
grosswt = ((use.Value * 1.1) + 10) * (115 / 100)

ElseIf check.Value >= 201 And check.Value <= 500 And ref.Value = "Printing" Then
grosswt = ((use.Value * 1.1) + 10) * (101 / 100)


'60%c40%p lbs between 501 and 999

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "60%Cotton40%Poly" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.11) * (102 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "60%Cotton40%Poly" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.11) * (101 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "60%Cotton40%Poly" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.11) * (101 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "60%Cotton40%Poly" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.11) * (115 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "60%Cotton40%Poly" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.11) * (101 / 100)

'100%c lbs between 501 and 999

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "100%Cotton" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.12) * (102 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "100%Cotton" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.12) * (101 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "100%Cotton" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.12) * (101 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "100%Cotton" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.12) * (115 / 100)

ElseIf check.Value >= 501 And check.Value <= 999 And ctn = "100%Cotton" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.12) * (101 / 100)


'60%c40%p lbs between 1000 and 2000

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "60%Cotton40%Poly" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.07) * (102 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "60%Cotton40%Poly" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "60%Cotton40%Poly" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "60%Cotton40%Poly" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.07) * (115 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "60%Cotton40%Poly" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.07) * (101 / 100)


'100% lbs between 1000 and 2000

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "100%Cotton" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.08) * (102 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "100%Cotton" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.08) * (101 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "100%Cotton" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.08) * (101 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "100%Cotton" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.08) * (115 / 100)

ElseIf check.Value >= 1000 And check.Value <= 2000 And ctn = "100%Cotton" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.08) * (101 / 100)


'60%c40%p lbs between 2001 and 4999

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "60%Cotton40%Poly" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.05) * (102 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "60%Cotton40%Poly" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "60%Cotton40%Poly" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "60%Cotton40%Poly" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.05) * (115 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "60%Cotton40%Poly" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.05) * (101 / 100)

'100% lbs between 2001 and 4999

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "100%Cotton" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.08) * (102 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "100%Cotton" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.08) * (101 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "100%Cotton" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.08) * (101 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "100%Cotton" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.08) * (115 / 100)

ElseIf check.Value >= 2001 And check.Value <= 4999 And ctn = "100%Cotton" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.08) * (101 / 100)

'60%c40%p lbs between 5000 and 8000

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "60%Cotton40%Poly" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.05) * (102 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "60%Cotton40%Poly" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "60%Cotton40%Poly" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "60%Cotton40%Poly" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.05) * (115 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "60%Cotton40%Poly" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.05) * (101 / 100)

'100% lbs between 5000 and 8000

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "100%Cotton" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.07) * (102 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "100%Cotton" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "100%Cotton" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "100%Cotton" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.07) * (115 / 100)

ElseIf check.Value >= 5000 And check.Value <= 8000 And ctn = "100%Cotton" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.07) * (101 / 100)

'60%c40%p lbs between 8001 and 10000

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "60%Cotton40%Poly" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.05) * (102 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "60%Cotton40%Poly" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "60%Cotton40%Poly" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.05) * (101 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "60%Cotton40%Poly" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.05) * (115 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "60%Cotton40%Poly" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.05) * (101 / 100)

'100% lbs between 8001 and 10000

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "100%Cotton" And ref.Value = "Bio" Then
grosswt = (use.Value * 1.07) * (102 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "100%Cotton" And ref.Value = "Presetting" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "100%Cotton" And ref.Value = "Sueded" Then
grosswt = (use.Value * 1.07) * (101 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "100%Cotton" And ref.Value = "Brushing" Then
grosswt = (use.Value * 1.07) * (115 / 100)

ElseIf check.Value >= 8001 And check.Value <= 10000 And ctn = "100%Cotton" And ref.Value = "Printing" Then
grosswt = (use.Value * 1.07) * (101 / 100)


End If

myfunc = grosswt

End Function

 

[Galaxiom]

Firstly I wouldn't do the function like that in Excel or Access. You are preforming an inordinate number of redundant tests by repeating same ones over and over again.

Secondly, hard coding data into a function is not a good idea. When something changes then the function would need to be rewritten.

The data should be in a table and the grosswt calculation factors (eg 1.05, 1.07, 115, 101 etc) retrieved using a query. Then plug these factors into a formula to get the result.

The table would have a field for the value check is tested against (lets call it test), one for ctn and one for ref. Note that a normalised design would not typically have a testmin and testmax fields because the testmin of the subsequent range could be effectively determined from the testmax of the previous range.

The query would retrieve the record where ctn and ref matched and check was the minimum test value greater than check.

Ideally the factors query would be joined directly into a main query but whether that can be done depends on how you are using the recordset.

 

[luzz]

Thank you for replying!

I have tried putting ref, ctn in a field in the same table and tried to use expression builder to calculate it, but expression builder doesnt seem to read the ctn and ref.

 

[Galaxiom]

Can you show us more about how you are using the results?

Ideally you could post a cut down version of your database with the essential tables having a few example records and your query. (You will need to zip it to allow it to be attached to your post because you don't have ten post yet.

The easy way is to use a DLookup() with criteria to get the result from the table. However it is more efficient to join to your main query.

 

[luzz]

First of all, Thankyou for replying!

below are my code i use previously in the access report under control source (Expression Builder)

=IIf([AccessTotalsOur Qty]<130 And [Content]="100% cotton",[Nett Weight]+20,IIf([AccessTotalsOur Qty] Between 131 And 200 And [Content]="100% cotton",[Nett Weight]*(115/100)+15,IIf([AccessTotalsOur Qty] Between 201 And 500 And [Content]="100% cotton",[Nett Weight]*(110/100)+10,IIf([AccessTotalsOur Qty] Between 501 And 999 And [Content]="100% cotton",[Nett Weight]*(112/100),IIf([AccessTotalsOur Qty] Between 1000 And 2000 And [Content]="100% cotton",[Nett Weight]*(107/100),IIf([AccessTotalsOur Qty] Between 2001 And 4999 And [Content]="100% cotton",[Nett Weight]*(105/100),IIf([AccessTotalsOur Qty] Between 5000 And 8000 And [Content]="100% cotton",[Nett Weight]*(105/100),IIf([AccessTotalsOur Qty] Between 8001 And 10000 And [Content]="100% cotton",[Nett Weight]*(105/100),IIf([AccessTotalsOur Qty] Between 131 And 200 And [Content]="60% cotton 40% polyester",[Nett Weight]*(112/100))))))))))

this code actually work, but it is unable to read in my content and reference. So basically when i key in the condition to read my content or reference, the result will be show blank on the report.

For eg;
i key in the condition content = 100%
then as long as the content = 100% they will not generate any number out.

 

[luzz]

Firstly I wouldn't do the function like that in Excel or Access. You are preforming an inordinate number of redundant tests by repeating same ones over and over again.

Secondly, hard coding data into a function is not a good idea. When something changes then the function would need to be rewritten.

The data should be in a table and the grosswt calculation factors (eg 1.05, 1.07, 115, 101 etc) retrieved using a query. Then plug these factors into a formula to get the result.

The table would have a field for the value check is tested against (lets call it test), one for ctn and one for ref. Note that a normalised design would not typically have a testmin and testmax fields because the testmin of the subsequent range could be effectively determined from the testmax of the previous range.

The query would retrieve the record where ctn and ref matched and check was the minimum test value greater than check.

Ideally the factors query would be joined directly into a main query but whether that can be done depends on how you are using the recordset.

In order to calculate the gross weight, i will have to look at the LBS and a chart.
So do i have to put the chart into the table as well?

 

[Galaxiom]

In order to calculate the gross weight, i will have to look at the LBS and a chart.
So do i have to put the chart into the table as well?

Yes. That chart would be a separate table.

Basically your calculation can be expressed as:

grosswt = ((use.Value * x) + y) * (z / 100)

Your table fields would be something like:
check, ref, x, y ,z

Each record will have x, y and z for the pairs of the maximum check and ref that apply. The query finds the lowest check and ref that are higher than the check and ref that are being tested and returns the formula result using the x, y and z values from the record.

(Give x, y and z meaningful names. They were just what I used because I don't know what they are called in your business rules.)

Sorry if it sounds complex. It isn't really and it is a fundamentally better way to do this job than hardcoding those values into multiple formulae.

 

[luzz]

I have uploaded the table i created, can you check if i am on the right channel as you? thanks

 

[Galaxiom]

Run the Final query to see the results. I didn't use your proper formula but something rather simpler, but you should get the idea.

Then work back through the query design.
Candidate finds the potential matches in the WeightChart where CheckMax exceeds LBS and the Reference matches.
Then BestCandiate finds the right record in the WeightChart by getting the lowest one.

Then this perfect match gets the Add and Multiply values to plug into the formula.

I have pieced together the query as separate subqueries so you can follow the logic in design view. This could be condensed into an integrated query with subqueries inside and structured in other ways that are slightly more efficient. However this would be hard to follow because they don't work in design view.

Using multiple queries like this might seem oddly complex. Another less technically correct but easier constructed alternative is to simply have CheckMin and CheckMax fields in the WeightChart and retreive them with a condition of:

LBS BETWEEN WeightChart.CheckMin AND WeightChart.CheckMax

I have not shown this alternative but you could probably work it out yourself if you really want to go that way once you understand the table structure concepts.

However, doing it like that means there could be a gap accidentally left in the ranges. It is also technically a breach of normalization because the CheckMin can be determined from the next lowest CheckMax record.

You really need to change away from Excel-think where you often find things as intersections. Databases are different. See how WeightChart is structured so it can be looked up by giving it only values in fields. Each combination of lookup values has two fields with a record for each combination rather than a column and a row like your original chart.

Also note how I have abstracted the References to a number. This is much more efficient for the database to store and process. It also means a description can be changed in one record and will display the new name everywhere.

Also note that the Final recordset is not updatebale so is only useful in a Report. If you need to use it in a Form there are other related ways to manage this. However we can go there later if necessary. First step is to understand this concept.

I hope I have not confused you with my reuse of some of your terminology in ways that might not be what you had. (eg Reference).

 

[arnelgp]

this is much simpler.
see changes on the tables.
additional Content table has been added.
Query1 is the final result.
open Query1 in design view and study how it is done.

 

[luzz]

Run the Final query to see the results. I didn't use your proper formula but something rather simpler, but you should get the idea.

Then work back through the query design.
Candidate finds the potential matches in the WeightChart where CheckMax exceeds LBS and the Reference matches.
Then BestCandiate finds the right record in the WeightChart by getting the lowest one.

Then this perfect match gets the Add and Multiply values to plug into the formula.

I have pieced together the query as separate subqueries so you can follow the logic in design view. This could be condensed into an integrated query with subqueries inside and structured in other ways that are slightly more efficient. However this would be hard to follow because they don't work in design view.

Using multiple queries like this might seem oddly complex. Another less technically correct but easier constructed alternative is to simply have CheckMin and CheckMax fields in the WeightChart and retreive them with a condition of:

LBS BETWEEN WeightChart.CheckMin AND WeightChart.CheckMax

I have not shown this alternative but you could probably work it out yourself if you really want to go that way once you understand the table structure concepts.

However, doing it like that means there could be a gap accidentally left in the ranges. It is also technically a breach of normalization because the CheckMin can be determined from the next lowest CheckMax record.

You really need to change away from Excel-think where you often find things as intersections. Databases are different. See how WeightChart is structured so it can be looked up by giving it only values in fields. Each combination of lookup values has two fields with a record for each combination rather than a column and a row like your original chart.

Also note how I have abstracted the References to a number. This is much more efficient for the database to store and process. It also means a description can be changed in one record and will display the new name everywhere.

Also note that the Final recordset is not updatebale so is only useful in a Report. If you need to use it in a Form there are other related ways to manage this. However we can go there later if necessary. First step is to understand this concept.

I hope I have not confused you with my reuse of some of your terminology in ways that might not be what you had. (eg Reference).

Thank you so much! I will need some time to digest all this information! haha

 

[luzz]

this is much simpler.
see changes on the tables.
additional Content table has been added.
Query1 is the final result.
open Query1 in design view and study how it is done.

Thank you

 

[Galaxiom]

this is much simpler.

Yes. This is the precisely the other solution I mentioned in my post. With it, great care must be taken not to leave gaps or create overlapping ranges especially when adding new ranges among the existing.

Also note that arnelgp has used table level lookups (see the lookup tab of the field) to display the Content descriptions in the grossweight chart table. Unless you realise this you will likely find some details of his solution a bit confusing.

Behind this it is the same system I used to abstract them to numbers for storage and processing.

 

[luzz]

Run the Final query to see the results. I didn't use your proper formula but something rather simpler, but you should get the idea.

Then work back through the query design.
Candidate finds the potential matches in the WeightChart where CheckMax exceeds LBS and the Reference matches.
Then BestCandiate finds the right record in the WeightChart by getting the lowest one.

Then this perfect match gets the Add and Multiply values to plug into the formula.

I have pieced together the query as separate subqueries so you can follow the logic in design view. This could be condensed into an integrated query with subqueries inside and structured in other ways that are slightly more efficient. However this would be hard to follow because they don't work in design view.

Using multiple queries like this might seem oddly complex. Another less technically correct but easier constructed alternative is to simply have CheckMin and CheckMax fields in the WeightChart and retreive them with a condition of:

LBS BETWEEN WeightChart.CheckMin AND WeightChart.CheckMax

I have not shown this alternative but you could probably work it out yourself if you really want to go that way once you understand the table structure concepts.

However, doing it like that means there could be a gap accidentally left in the ranges. It is also technically a breach of normalization because the CheckMin can be determined from the next lowest CheckMax record.

You really need to change away from Excel-think where you often find things as intersections. Databases are different. See how WeightChart is structured so it can be looked up by giving it only values in fields. Each combination of lookup values has two fields with a record for each combination rather than a column and a row like your original chart.

Also note how I have abstracted the References to a number. This is much more efficient for the database to store and process. It also means a description can be changed in one record and will display the new name everywhere.

Also note that the Final recordset is not updatebale so is only useful in a Report. If you need to use it in a Form there are other related ways to manage this. However we can go there later if necessary. First step is to understand this concept.

I hope I have not confused you with my reuse of some of your terminology in ways that might not be what you had. (eg Reference).

I look through the access file that you attached and saw that you create a relationship among the table. Do i have to create a relationship as well? I dont really understand how you do the weight chart? Can you explain a little bit more? What does the term "CheckMax" means?
Once again thankyou

 

[luzz]

Run the Final query to see the results. I didn't use your proper formula but something rather simpler, but you should get the idea.

Then work back through the query design.
Candidate finds the potential matches in the WeightChart where CheckMax exceeds LBS and the Reference matches.
Then BestCandiate finds the right record in the WeightChart by getting the lowest one.

Then this perfect match gets the Add and Multiply values to plug into the formula.

I have pieced together the query as separate subqueries so you can follow the logic in design view. This could be condensed into an integrated query with subqueries inside and structured in other ways that are slightly more efficient. However this would be hard to follow because they don't work in design view.

Using multiple queries like this might seem oddly complex. Another less technically correct but easier constructed alternative is to simply have CheckMin and CheckMax fields in the WeightChart and retreive them with a condition of:

LBS BETWEEN WeightChart.CheckMin AND WeightChart.CheckMax

I have not shown this alternative but you could probably work it out yourself if you really want to go that way once you understand the table structure concepts.

However, doing it like that means there could be a gap accidentally left in the ranges. It is also technically a breach of normalization because the CheckMin can be determined from the next lowest CheckMax record.

You really need to change away from Excel-think where you often find things as intersections. Databases are different. See how WeightChart is structured so it can be looked up by giving it only values in fields. Each combination of lookup values has two fields with a record for each combination rather than a column and a row like your original chart.

Also note how I have abstracted the References to a number. This is much more efficient for the database to store and process. It also means a description can be changed in one record and will display the new name everywhere.

Also note that the Final recordset is not updatebale so is only useful in a Report. If you need to use it in a Form there are other related ways to manage this. However we can go there later if necessary. First step is to understand this concept.

I hope I have not confused you with my reuse of some of your terminology in ways that might not be what you had. (eg Reference).

I having some issue now. I have added in content which is 100%Cotton and the reference = Bio Can you advise me on how can i create my table?

for example;
if my lbs = 200, i will have to multiply by 15% then + 10Kg then if the reference = Bio i will have to multiply by 2%.

Can you please guide me on how to structure the table so that it will work?
Thankyou so much

 

[luzz]

I have made amendment to the Access file above and i faced some issue.

- I added in content and reference and an additional column called "multiplication" in the weightchart.
- I change the formula to calculate the result and my results are wrong, besides that, it does not show all the result and it does not show result when my "multiplication" column is blank.
- Is it possible to link this to my form ?

May i know how to fix this? Thank you!

this is the formula i used to calculate:

Result: (([LBS]*((100+[Multiplication])/100))+[Add])*((100+[Multiply])/100)

 

[luzz]

I have solve all my issue. Now i want to use it in my form, can i do it? if yes, how can i do that?

 

[Galaxiom]

I have solve all my issue. Now i want to use it in my form, can i do it? if yes, how can i do that?

Usually a subform is the way to deal with the need to enter values when some data is returned in an nonupdateable recordset.

 

[luzz]

Usually a subform is the way to deal with the need to enter values when some data is returned in an nonupdateable recordset.

I do have a subform on the form (NewPO form) that i have created and have key in formula by using code builder in my nett weight text box to automate my nettweight. How can i automate my grossweight based on the table/queries that i have built? Thankyou for your help.
I attached my access file above.