Multiple Price query based on customer type

Posted on 2008-10-28
Medium Priority
Last Modified: 2013-11-28
I have a job_type table
id Description       Price1  Cost1    Price2   Cost2   Price3   Cost3
1 Basic install       100       80        105         85        95        83
2 IRD                      110     90         114       89         101      87
and so on

now in the Job form, where i enter the job details
i have a subform job_type, which should be filtered according to price and cost level(combo box in the form, we enter price and cost level), so if i enter price level 1 and cost level3, the subform should be  something like this
id Description       Price1     Cost3
1 Basic install       100              83
2 IRD                      110          87
and so on

Any idea how to implement this

best regards
Question by:titorober23
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 46

Expert Comment

ID: 22827759
you will need to construct the sql with string concatenation for the column numbers.

public function pricequery(pricelevel, costlevel) As String
   pricequery = "Select ID, Description, Price" & pricelevel & " , Cost" & costlevel & " From job_type"
end function

Open in new window

LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 22827934
titorober23 said:
>>I have a job_type table
>>id Description       Price1  Cost1    Price2   Cost2   Price3   Cost3

With respect, that is a bad table design: what if next week your company adopts a 4th price/cost scheme?

You are much better off having a separate, related table that shows the price points in a normalized fashion.
LVL 46

Expert Comment

ID: 22828123
I agree with matthewspatrick.  It is a bad table design.

Not only that, but how well does your subform respond to different column sets being presented?

Accepted Solution

titorober23 earned 0 total points
ID: 22923047

I fixed, what i did is this
the record source is based on a query which select the field name according to the form's control value.
For example if the form-company combobox is n, then orice is Fieldn; if Form tech-combobox is x the Costvalue is Fieldx.
Sothe record source change according to the controls values.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question