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

Comment Utility
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
Comment Utility
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

Comment Utility
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
Comment Utility

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now