Combo boxes & Pulling Prices based on specific company selected

[TNorvell]

I am currently working on a database to assist me with estimating costs at work and a variety of other queries, where I am stumped now, is I have an estimate form created now and I can use a combo box to select a company from "company table" and I can use a combo box to select the description of service I am adding to the estimate and it will populate the amount of that description in the amount box. What I would like is, to enter amounts for each company and allow it to choose the specific amount of that description based on which company is selected in the company combo box. I don't have a lot of coding experience but I am willing to learn. If you can assist me I would appreciate it! Thank you!

[Micron]

You're saying that any one particular service can have multiple prices and the price depends on the company? In that case, it seems that you'd need a junction table since you'd have a many to many relationship. Besides having its own primary key field (autonumber) the junction table would contain a field for the company id and a field for the service id field (both as foreign keys). In other words, the field values would be the autonumber values from the company and service tables. I imagine that after picking the company and service you'd populate a form textbox with the price value you'd get from the junction table.

Another approach is sometimes to associate a discount rate with a company, but special effort is required if you sometimes want to modify the discount rate.

[TNorvell]

I will research junction tables. Thank you for the info!

[orange]

In very general terms, my read of your post seems that
-You have WORK/SERVICE, and
-you have COMPANYs, and
-you have EstimatedCOSTs.

And following micron's advise, EstimatedCost could be read as The EstimatedCost for Work XYZ done by COMPANY ABC would be the junction table.

Company ----->EstimatedCost<--- Work

jtblEstimatedCost
EstimatedCostID--PK
CompanyID--FK to Company
WorkID-------FK to Work
EstimatedCost
..Other info as needed..
EstimatedBy
EstimateDate
....

Perhaps you could provide a few examples to ensure readers understand your post/requirement in context.

[TNorvell]

Maybe this will help visualize what im trying to accomplish. I know its kinda confusing. Thanks for the help!

[TNorvell]

So I have a list of clients all with different unit prices, I want those prices to change in correlation with the company combo box selected at the top. However, my description box "line items" are the same for all my clients.

[orange]

So, your business is XRayAnalysis of Welds??
It would be helpful to you and readers f you provided a 30,000 ft overview of your business. Then more details of the specific issue. Always a good approach to provide a sample of the input and the output expected of your proposed processing of that input.

I think we were posting at the same time.

As Micron suggested, you might want to consider a Customer Discount approach.
Customer X discount 2.5%
Customer Y discount 2.2%

There may be various factors involved in how you determine Discount Rate. Could be tricky, but worth investigating.

I suggest you look at this. It may help with analysis and design; and give you a chance to "prototype/test" various ways.

[TNorvell]

All the unit prices dont change per customer, just a couple. So a % discount wouldnt work in my application.

[orange]

"All the unit prices dont change per customer, just a couple."

Suggest you take a few (4 or 5) line items, complete with unit cost, and 2 different Customers and show exactly what is changing. There may be options.

Make up some data just to show how and what changes from Customer X and Customer Z.
A discounting scheme may only apply to certain services (not all).

[TNorvell]

something like this?

[orange]

As I read your post, all unit prices changed except the Pickup Truck with mobile darkroom.
It would seem you have a discount/or unit price for each/every line item(service) for each customer.
Could be very unwieldy?

Do you have some sort of algorithm/formula to determine the work/service unit price for any customer?
In your example, what and/or who determines the unit price for specific customer.

Is it possible, that for the same customer requiring the same work, the unit price could/would/does change based on the Job#?

[TNorvell]

Its the negotiated price per client, I might just have to make a copy of each form to be customer specific and make a customer specific rate sheet table to attach to that form.

[Micron]

I might just have to make a copy of each form to be customer specific

Absolutely not.
I scanned the last few posts and I don't see how you can avoid a junction table. It may be a bit difficult to complete if there are hundreds/thousands of combinations of price/company but once completed it should only be a matter of maintenance. Still haven't see the tables/relationships...

[orange]

How many Customers? How many Services?
Don't do customized form for each customer.
Look for a pattern and try to solve it with some automation.
You don't want to end up with a "manual system" that you have to customize/handhold every operation.

[TNorvell]

Customers are always being added, I would estimate 50 or so, and the service items is 110 different items. How would I create the table so I could have the service - customer - price and then have that follow over to the form as shown in the screenshot?