I am using a SQL server backend with a linked Access Db front end through an ODBC connection.
I need to produce the following but I canât seem to figure out the SQL to do it.
I am trying to use fields from 5 tables and two new fields which should only exist for the life of the query as the source for a continuous form
Each Customer may have multiple orders. When a customer calls for a refill they may only want certain items they have ordered before, but they may not all be in the same order. So the form must have contain all the drugs they have ordered in the past but only show one of each thing they have ordered.
So for instance a customer may have ordered
Order 1
drug1 1 bottle of 100 tab
drug2 1 bottle of 90 tab
drug3 3 bottles of 30 tab
Order 2
drug1 3 bottles of 30 tab
drug4 4 packs of 28 tab
Order 3
drug2 1 bottle of 90 tab
drug1 3 bottle of 30 tab
Now they need a refill. The form should be able to get the following result from this customer
Which of the following would you like to refill:
drug1 3 bottle of 30 tab y / n
drug1 1 bottle of 100 tab y / n
drug2 1 bottle of 90 tab y / n
drug3 3 bottles of 30 tab y / n
drug4 4 packs of 28 tab y / n
Rather than creating a new order from scratch or refilling an old order, adding to it and deleting from it. I would like to put a consolidated refill form in the application.
This could can be done with SQL, however I run into the following problems.
1. When adding a temporary field to the query for the y / n part of the structure to control some check boxes, the check boxes are not updateable. It creates constants.
2. When I create a view in the SQL backend and use it as a linked table If I add some fields 0 as refill, 0 as nrx where refill and nrx are the source for the checkboxes that are needed choose whether to include this particular drug in the refill. They come out as constants and are also not updateable.
3. When the distinct operator is used in any SQL it becomes non updateable, which makes sense.
4. When the order by clause is used in SQL server it uses the top 100 percent operator which make s it non updateable.
To recap, the query needs to select distinct values from a combined group of tables and add two new Boolean or bit fields which must be updateable in a form.
Thanks in advance for any advice you might have, I have not been able to successfully implement this, yet.
-Roni
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com