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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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