Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Multiple Price query based on customer type

Hi
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
0
titorober23
Asked:
titorober23
  • 2
2 Solutions
 
aikimarkCommented:
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

0
 
Patrick MatthewsCommented:
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.
0
 
aikimarkCommented:
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?
0
 
titorober23Author Commented:
Hi

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now