asked on

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

aikimark

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

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

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?

ASKER CERTIFIED SOLUTION

titorober23

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial