Multiple Price query based on customer type

Posted on 2008-10-28
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
  • 2
LVL 45

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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 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 45

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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