Relationship-Drugs

[Spentak]

Drug Testing Database. Issue:

I have a table that lists a names of possible drugs that can be tested for.

I need to set up pricing for the tests, but I CAN"T price each drug individually(Amphetamines 3.50 Cocaine 3.50). I need to price it by drugs - 1 drug Tested for, 2 drugs tested for, 3 drugs etc. So I need access to recognize, no matter which specific test(amphetamines, cocaine, etc)is being tested for, it will price it accordingly. Amphetamines and Barbiturates = 2 panel, Cocaine, Amphetamines, Opiates = 3 Panel. I don't want to go and design a "Panel" for every type of drug combination. I simply need access to count how many drugs and price accordingly.This would only work for drugs which all have the same price because some tests are more expensive, like a Hair Test, so I would need to be able to create a specific price panel for that. Any suggestions?

 

[jdraw]

Drug Testing Database. Issue:

I have a table that lists a names of possible drugs that can be tested for.

I need to set up pricing for the tests, but I CAN"T price each drug individually(Amphetamines 3.50 Cocaine 3.50). I need to price it by drugs - 1 drug Tested for, 2 drugs tested for, 3 drugs etc. So I need access to recognize, no matter which specific test(amphetamines, cocaine, etc)is being tested for, it will price it accordingly. Amphetamines and Barbiturates = 2 panel, Cocaine, Amphetamines, Opiates = 3 Panel. I don't want to go and design a "Panel" for every type of drug combination. I simply need access to count how many drugs and price accordingly.This would only work for drugs which all have the same price because some tests are more expensive, like a Hair Test, so I would need to be able to create a specific price panel for that. Any suggestions?

You seem somewhat confused on what you are trying to do. Access can not count something if you can't define that something. I think you need to sort out exactly what you want to charge for. The general forum members do not know what you mean by Panel.
Sounds like you want to charge for tests.

 

[Spentak]

Price of Tests

Yes I want to charge for tests. 1 Test = 1 Panel 2 Tests = 2 Panel and so forth. Perhaps I can write some VBA that has access count how many drugs are selected as some type of count function, and depending on the number it retrieves is what the price will be?
I could make a table that allows me to put certain drugs into a "combo(panel/Test)" but then i would have to make 1 test for each drug, then i would have to design every possible drug combination, which isn't user friendly enough for what i want to do.

 

[neileg]

You aren't being clear on what the business process is. If you can't write down, you can't model it in Access. And as jdraw says, we don't understand what you are talking about!

 

[gemma-the-husky]

you probably need a "test" table, to summarise the test details, date, order ref etc, and a "testeditems" table to store the individual drugs tested for, lab used, technician, traceability etc.

Then you can easily count the number of items for each test to evaluate your charging

Do you have the tables structured in this way.

Most things come back to getting the table design right. - normalisation

 

[Spentak]

Tables

It is structured that way, the only problem is I would like access to calculate that pricing for me automatically, to avoid user errors

 

[jdraw]

It is structured that way, the only problem is I would like access to calculate that pricing for me automatically, to avoid user errors

Perhaps you could draw a picture, or write down the relationships for your tables. We really don't know how to help, because we don't understand the problem.

 

[NicoleGW]

How do users indicate which tests they've done for a panel? Do you have a table with one field for each possible test, and then one record per panel stored in that table? Or does the user enter a separate record for each test?

Basically, in your table where you actually store info about what tests were done, does one record = a panel, or does one record = a test?

 

[andreas_udby]

Another thought: if it's a simple linear relationship (1 drug = x dollars, 2 drugs = 2x dollars) then your form or report can perform the calculations. You can also do this in a quadratic relationship (y = m*x + b). If it isn't either of those, such as in the case of a volume discount which grows as the volume grows, then it becomes more challenging. By all means, if you can provide us with some real-world examples of how the prices increase, then we can give you more specific help.

 

[Spentak]

Pricing

The last post is exactly what it is. 1 drug = x 2 drug = 2x, how do i go about doing that?

 

[andreas_udby]

You can create calculations in reports, forms, or queries pretty easily. I'm not sure what output method you're wanting to use here, but let's assume a report. The controls on the report can have a data source that is a calculated value, such as:

=[Meth]*3.5

...assuming that a meth test costs $3.50.

Now, on your input form, you can have multiple text boxes, one for each type of test, and then the user inputs how many of each test would be performed for that panel. So, let's assume that a barbituate test (field name [Barb]) costs $5, a cocaine test ([Coke]) costs $10, and a opiates test ([Opie]) is $15. On your input form you have text boxes for those tests, and the user puts in the number of each being ordered.

On your report, you have the controls set to calculate the total prices as the report is generated. In the Detail section of your report, you would have:

txtMeth: =[Meth]*3.5
txtBarb: =[Barb]*5
txtCoke: =[Coke]*10
txtOpie: =[Opie]*15

Then, in the Detail footer of your report, you include a text box ([txtTotal]) that has the following data source: =[txtMeth]+[txtBarb]+[txtCoke]+[txtOpie]

When you run the report, each panel you order (assuming that is what you want) shows up with the subtotal costs of each type of tests (5 meth tests = $17.50, 2 barbituate tests = $10, etc.), and the total cost of the panel appears in [txtTotal].

If you want to print just one specific panel on each page, you can place [txtTotal] in the page footer.

I hope that helps. Play around with the report properties and you'll see there's a lot you can do there, that doesn't need to be done in a query.