Help?

[DcoyElement]

Id Love some help on an issue im having, I dont want to be given the answer just some tips. This is my first time using Access. I am trying to make an automated database which when you put in some basic parametres it takes that information and gives you the information you are after. I am trying to make a calculation that takes the total cost of products in a field then checks to see if the total cost is within a certain range and if it is then Add X if not then keep going until it is within the right range then add x.

Is this just a IF-THEN-ELSE Function?

So like IF Total Cost falls within 0-50 THEN +10 ELSE keep looking? I am unsure.

Any help would be appreciated.

 

[The_Doc_Man]

This is almost certainly going to require VBA coding to achieve. Doing this by query would be incredibly tricky at best and totally impossible at worst because queries don't stop running in the middle of what they are doing (unless they throw an error of some type.) I'll spare you the rather boring technical reason of why queries behave that way until a later time.

However, your statement is not very clear in terms of design intent. From your description we don't know your data source and your intended data use or destination once you've done this computation. We can answer questions in the abstract sometimes, but we aren't psychic. We do need at least a little bit of detail to go on. Like, what is the OVERALL problem? What are you building here?

When starting with a database, my advice is usually that you would simulate the operation on paper to lay out what it is you want to do. The short form of my rule is, "If you can't do it on paper, you can't do it in Access." I.e. if you don't have a clear enough understanding of the goals so that you could simulate the process, you are not ready to use Access anyway. Remember, Access is dumber than a box of rocks. It is a tool, just like a power drill is a tool. The drill doesn't know where to make holes... you do. The drill just spins very fast and turns whatever is loaded to the chuck.

Don't take this as an insult, because it is not intended that way, but... You say "you are unsure" in your question. This is a sure sign that you don't (yet) know what you are doing. Time for some "skull sweat" to think through your intent. Stop thinking in code. Ask yourself what you would do by hand to achieve this desired result. Focus on the steps you need to perform to get from starting point A to ending point B. Then you have a shot at figuring this out well enough to implement something. This is also where you will need a particularly scarce commodity to help you... patience.

My late father-in-law was a carpenter. His rule was "Measure twice, cut once." For programming, the rule is "Design once, review twice, implement once."

Finally, since that is your first post, hello and welcome to the forum.

 

[theDBguy]

Hi. Welcome to AWF!

I agree. It might help give you a proper guidance if you could share with us the structure of your tables.

 

[DcoyElement]

This is almost certainly going to require VBA coding to achieve. Doing this by query would be incredibly tricky at best and totally impossible at worst because queries don't stop running in the middle of what they are doing (unless they throw an error of some type.) I'll spare you the rather boring technical reason of why queries behave that way until a later time.

However, your statement is not very clear in terms of design intent. From your description we don't know your data source and your intended data use or destination once you've done this computation. We can answer questions in the abstract sometimes, but we aren't psychic. We do need at least a little bit of detail to go on. Like, what is the OVERALL problem? What are you building here?

When starting with a database, my advice is usually that you would simulate the operation on paper to lay out what it is you want to do. The short form of my rule is, "If you can't do it on paper, you can't do it in Access." I.e. if you don't have a clear enough understanding of the goals so that you could simulate the process, you are not ready to use Access anyway. Remember, Access is dumber than a box of rocks. It is a tool, just like a power drill is a tool. The drill doesn't know where to make holes... you do. The drill just spins very fast and turns whatever is loaded to the chuck.

Don't take this as an insult, because it is not intended that way, but... You say "you are unsure" in your question. This is a sure sign that you don't (yet) know what you are doing. Time for some "skull sweat" to think through your intent. Stop thinking in code. Ask yourself what you would do by hand to achieve this desired result. Focus on the steps you need to perform to get from starting point A to ending point B. Then you have a shot at figuring this out well enough to implement something. This is also where you will need a particularly scarce commodity to help you... patience.

My late father-in-law was a carpenter. His rule was "Measure twice, cut once." For programming, the rule is "Design once, review twice, implement once."

Finally, since that is your first post, hello and welcome to the forum.

Hello, my main aim, is to be able to take given suppliers delivery cost, which is different depending on which supplier is selected, then a user can input a product choose the supplier and the quantity of products, this will then workout the transport cost and add it to the total, using this the user can then compare the products including transport cost to see which is the most cost effective. I have never used VBA code before, but would love to learn especially for this project.

So, user fills out form, Product, Quantity, Supplier. This is then worked out and compared to the same product from different suppliers, and shows the most cost effective etc. At this moment in time having never used access before, I have a table for suppliers transport costs and a table for the products, i have it set up so when a user fills out the quantity it provides a total cost in the next field, however, I am unsure on how I then take that cost total then (If it falls within given ranges from suppliers do this) For example, If a product totals at $500 it would be in lets say band 4 resulting in an added $50 in transport cost, but how do I automate that part so when filling out a form it does it in the backend so users dont have to do all the inputs themselves. Hope this made more sense, Thank you for the warm welcome.

 

[GPGeorge]

PMFJI:
This sounds like it can best be solved with a table - drive solution. By that I mean you have a table that stores transportation costs by band.

The problem with trying to specify that is the same as the initial problem, more detail is needed.

If all suppliers used the same METHOD of calculating shipping costs, it would be easier than if they use different methods, as well as different rates.
For example, one supplier might have set amounts $5, $10, $15, etc for different pricing bands. Another supplier might use a straight percentage, i.e. 10%, 8%, 6% etc.

Let's pin that down. Again, details make possible educated suggestions.

 

[arnelgp]

create a table for your range of Cost, example

tblRangeCost (table)
Lo (Number, double)
Hi (Number, double)
AddCost (Number, double)

sample data:

Lo--------------------Hi------------------------AddCost
0---------------------$50----------------------0
$51------------------$100---------------------$1.25
$101-----------------$999999-----------------$5.00

you then create a Query to add the AddCost (additional cost):

select product, product_cost, product_cost + (select AddCost from tblRangeCost where [product_cost] between Lo And Hi) As marginCost
from table1;

 

[DcoyElement]

PMFJI:
This sounds like it can best be solved with a table - drive solution. By that I mean you have a table that stores transportation costs by band.

The problem with trying to specify that is the same as the initial problem, more detail is needed.

If all suppliers used the same METHOD of calculating shipping costs, it would be easier than if they use different methods, as well as different rates.
For example, one supplier might have set amounts $5, $10, $15, etc for different pricing bands. Another supplier might use a straight percentage, i.e. 10%, 8%, 6% etc.

Let's pin that down. Again, details make possible educated suggestions.

Hello Hello, thank you for the response, So details! One supplier, has 4 Ranges, If between 0-50 add 10 if between 50-150 add 20 if between 150-1000 add 40 and if between 1000-2000 add 70.

Another supplier is 0-250 add 35, 250-1999 add 58 2000-4999 add 120 5000-9999 175 10000-19999 360 20000-29999 525 30000-100000 625.

These are the suppliers with consistentency with their transport costs, they go with if within range then add this, while others work off pallets. These are the two I would like to tackle first. As I assume they are the easiest.

 

[jdraw]

@DcoyElement,

Still some confusion in the requirements- seems suppliers can/do have different ranges and cost for range. And I suspect these costs and/or ranges can be redefined as time goes on??
Can you provide a few examples that show
- product(s) delivered by Supplier with a few different ranges and the calculated/expected cost.
- product(s) delivered by Supplier that use/work off "pallets"

Simple examples to clarify your requirements and expectations.

 

[DcoyElement]

@DcoyElement,

Still some confusion in the requirements- seems suppliers can/do have different ranges and cost for range. And I suspect these costs and/or ranges can be redefined as time goes on??
Can you provide a few examples that show
- product(s) delivered by Supplier with a few different ranges and the calculated/expected cost.
- product(s) delivered by Supplier that use/work off "pallets"

Simple examples to clarify your requirements and expectations.

Product X x 10= 500 so transport cost from one supplier would be 58 then comparing that to the other supplier which would be
add 40. So then seeing if lets say product X=35 and Y=55 Would product Y be cheaper because of its cheaper transport cost or would X be cheaper. Just trying to find out which supplier to go with based on a total cost which includes transport cost comparison.

Suppliers JEFF ALAN and STEVE all sell the same Monitor.
Jeffs monitor is priced at 50
Alans monitor is priced at 55
Steves monitor is priced at 57

You want to order 15 monitors, So Jeffs 50x15=750 Alans Monitor 55x15=825 Steves monitor 57x15=855. Thats the base cost, so as it stands you want to buy Jeffs monitor to save the money.

However you come to the transport cost and Jeffs price ranges of 500-1000= 250 transport cost, while Alans is only 100 and Steves is 55, you would then add 750+250=1000 Then Alans 825+100=925 Steves 855+55=910. So then you would want to choose Steves monitors because while the base cost is more expensive the cost including transport cost is cheaper. My goal/aim is have it so a user can fill out a form with the quantity and the chosen product then it calculates the transport cost for each supplier and spits out the best value for money supplier.

 

[jdraw]

So, as I understand your Jeff/Alan/Steve scenario, you want overall cost in order to make your decision.

Cost for Decision = Base Cost + Transportation Cost

It seems you need a TransportCostLookup table, with something along this line:

TransInfoID
Supplier
RangeStart
RangeEnd
TransCost

This similar to arnelgp's , except the ranges are Supplier specific - not uniform across all suppliers.
You may want to include a field for EffectiveDate if these values change from time to time.

 

[DcoyElement]

So, as I understand your Jeff/Alan/Steve scenario, you want overall cost in order to make your decision.

Cost for Decision = Base Cost + Transportation Cost

It seems you need a TransportCostLookup table, with something along this line:

TransInfoID
Supplier
RangeStart
RangeEnd
TransCost

This similar to arnelgp's , except the ranges are Supplier specific - not uniform across all suppliers.
You may want to include a field for EffectiveDate if these values change from time to time.

Yeah that is correct, Cost for decision = base cost +transportation cost

So are those 5 listed characteristics are they 5 seperate tables, Ive currently got 3 seperate tables, One for the product (Monitor) with 3 suppliers all in their own seperate tables with their specific ranges. Earlier The Doc Man said I would have to use VBA, is this needed? Is your suggestion all in one table? My knowledge of access is quite embarrassing tbf.

 

[jdraw]

These are attributes/fields in TransportCostLookup table. I agree with Doc, some vba could/would be needed.
No, your set up can not be done with 1 table.

My knowledge of access is quite embarrassing tbf.

No. Don't be embarrassed. We all started not knowing key parts of database and/or Access. You are asking questions and participating --all good
I made this draft model that may be helpful. Ignore the Integer (10) -that's a shortcoming on my part with the modelling tool.

 

[GPGeorge]

So, as I understand your Jeff/Alan/Steve scenario, you want overall cost in order to make your decision.

Cost for Decision = Base Cost + Transportation Cost

It seems you need a TransportCostLookup table, with something along this line:

TransInfoID
Supplier
RangeStart
RangeEnd
TransCost

This similar to arnelgp's , except the ranges are Supplier specific - not uniform across all suppliers.
You may want to include a field for EffectiveDate if these values change from time to time.

This is where I was headed, yes. It has to be supplier specific because, well, the original intent was to compare supplier specific differences.

I am still concerned, however, that you also need to account for different methodologies. This depends on everyone using a flat rate per volume approach. I know it's probably less common, but you do have to think about a percentage rate per volume approach, where you simply add a percentage to the total cost.

 

[DcoyElement]

These are attributes/fields in TransportCostLookup table. I agree with Doc, some vba could/would be needed.
No, your set up can not be done with 1 table.


No. Don't be embarrassed. We all started not knowing key parts of database and/or Access. You are asking questions and participating --all good

I made this draft model that may be helpful. Ignore the Integer (10) -that's a shortcoming on my part with the modelling tool.

View attachment 97465

So is that separate tables? So Product table into product supplier then supplier transport cost etc. Or is this using a specific tool. Secondly, how would one then setup a form to make it user friendly. Thank you.

 

[jdraw]

So is that separate tables? So Product table into product supplier then supplier transport cost etc. Or is this using a specific tool. Secondly, how would one then setup a form to make it user friendly.

The graphic (ERD-Entity Relationship Diagram) identifies the tables to support your set up and how the tables relate to one another based on the info gleaned in this thread.
(in the TransportCostlkp table I misspelled a field name-- RaneHi should be RangeHi )

Some business rules in the ERD:
You deal with 1 or many Products
There are 1 or many Suppliers
You make decisions regarding a combination of Product*Supplier info
Suppliers provide their Transportation cost based on cost of Products ordered
We know nothing yet of the pallet based processes

User friendliness is developed/designed in the user interface (that is the form(s) with which the user interacts)

If this is all new to you, then I suggest you watch some youtube or other videos to get an appreciation of the processes involved in database design, designer and analyst roles, user expectations etc. See the Database Planning and Design link in my signature for many related articles and links.

 

[Cronk]

Do we know if a Supplier has multiple products with different transport cost structures for each product?

 

[jdraw]

@Cronk

No we don't have all details from the OP. George has asked some questions and I'm waiting for some details on pallets and the overall requirement.

 

[DcoyElement]

@Cronk

No we don't have all details from the OP. George has asked some questions and I'm waiting for some details on pallets and the overall requirement.

It is just one product, between 5 suppliers, The prices are all extremely similar, but their transport costs vary resulting in more cost effective products. We are trying to do make it so all the suppliers fit within a range, So like a supplier which would be based on pallets, converting them into the other suppliers method of range cost. So if it falls within a given range from the supplier than it costs this amount.

 

[jdraw]

If I understand correctly, you want all Suppliers to base their prices on some common range. And, although you haven't provided details on pallet based suppliers, you want them to adopt the common range approach.

If I look at that from a supplier's view, seems you want current custom range suppliers and current pallet based suppliers to change their current business practices. It may be possible, but that isn't your current mode of operation.
This seems a different requirement (or perhaps a misinterpretation of your initial post.

What you are seeking is a change in the way the suppliers do their business. Seems to me that would involve some sort of negotiation. We don't know your business sufficiently well to know whether you could reduce your suppliers and only accept those who will change transportation costs to your demands.

To me it seems your first step is to discuss " a common range for transportation costs" with the suppliers. The next step would be to convince the "pallet based" suppliers to convert to and accept your negotiated "common range approach".

This does NOT appear to be a database problem per se -at least not initially.

If I have misunderstood or misguessed the requirement, then we need more detailed information to proceed.

 

[DcoyElement]

If I understand correctly, you want all Suppliers to base their prices on some common range. And, although you haven't provided details on pallet based suppliers, you want them to adopt the common range approach.

If I look at that from a supplier's view, seems you want current custom range suppliers and current pallet based suppliers to change their current business practices. It may be possible, but that isn't your current mode of operation.
This seems a different requirement (or perhaps a misinterpretation of your initial post.

What you are seeking is a change in the way the suppliers do their business. Seems to me that would involve some sort of negotiation. We don't know your business sufficiently well to know whether you could reduce your suppliers and only accept those who will change transportation costs to your demands.

To me it seems your first step is to discuss " a common range for transportation costs" with the suppliers. The next step would be to convince the "pallet based" suppliers to convert to and accept your negotiated "common range approach".

This does NOT appear to be a database problem per se -at least not initially.

If I have misunderstood or misguessed the requirement, then we need more detailed information to proceed.

This is an issue on my end sorry for explaining this poorly,

We work with 5 suppliers, who all supply monitors, 3 of them have the same transport cost method (if the total falls between a range) add x, we want to have a database that takes the total cost puts it into that range for each supplier, takes the transport cost adds it too the total cost then provides which is the cheapest including transport cost.