Geek asks and answers

Access 2010: How to make the query result updated?

I am working on my first Access 2010 database and ran into the problem of editing the recordset returned from the query.

I have two tables: drugs and warehouse_table. In "drugs" I have the name of the drug. In another table, I have relevant information. For those drugs that are in the “Drugs” table, the pharmacist will manage their quantity and other relative values, such as the amount of each drug administered with a specific expiration date. The pharmacist can import the same drug information into store_stocK, but with a different expiration date.

Each row of data in "store_stock" is characterized by an active_object / strength / strength_type / form_dose. These four values ​​must be the same in the string data in the “drug”. The pharmacist will be able to update curr_date, in_quant, out_quant_expiry_date and brand (from "store_stock") for existing row data and import new quantities of drugs that exist in the "drugs".

My request right now:

SELECT warehouse.active_subs,
   warehouse.strength,
   warehouse.strength_type,
   warehouse.dosage_form,
   warehouse.curr_date,
   warehouse.in_quant,
   warehouse.out_quant,
   warehouse.expiry_date,
   warehouse.available_stock,
   warehouse.brand,
   warehouse.ID
FROM   drugs
       RIGHT JOIN warehouse
         ON ( drugs.active_substance = warehouse.active_subs )
            AND ( drugs.strength = warehouse.strength )
            AND ( drugs.strength_type = warehouse.strength_type )
            AND ( drugs.dosage_form = warehouse.dosage_form )
WHERE  ( ( ( warehouse.active_subs ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_act_sub & "*" )
AND ( ( warehouse.strength ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_strength & "*" )
AND ( ( warehouse.strength_type ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_strength_type & "*" )
AND ( ( warehouse.dosage_form ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_dosage_form & "*" ) );

Is there a way to make the result updated?

Thanks in advance for any suggestions!

Zinonas

+3
source share
3 answers

Try:

<...>
warehouse.ID
FROM  warehouse 
LEFT JOIN Drugs
     ON ( drugs.active_substance = warehouse.active_subs )
        AND ( drugs.strength = warehouse.strength )
        AND ( drugs.strength_type = warehouse.strength_type )
        AND ( drugs.dosage_form = warehouse.dosage_form )
0
source

Your requirement is not entirely clear, and the title is very different from your textual description of the problem, and the request provided is even different.

, active_subs, strength, force_type, dosage_form, , , , , . . ?

: , . , , OP :)

SELECT warehouse.ID, drugs.quantity, warehouse.active_subs, warehouse.strength, 
       warehouse.strength_type, warehouse.dosage_form, warehouse.curr_date, 
       warehouse.in_quant, warehouse.out_quant, warehouse.expiry_date, 
       warehouse.available_stock, warehouse.brand --whatever values you want
FROM   warehouse
JOIN   drugs ON drugs.active_substance = warehouse.active_subs AND 
                drugs.strength = warehouse.strength AND 
                drugs.strength_type = warehouse.strength_type AND
                drugs.dosage_form = warehouse.dosage_form
WHERE  (....); --if ever there is one
UPDATE warehouse_stock SET curr_date=@curr_date, in_quant=@in_quant, 
                           out_quant=@out_quant, expiry_date=@expiry_date, 
                           brand=@brand;

, OP . where .

0

, , From Where ( ). , warehouse , drugs. Exists, ( , ).

Select W.active_subs, W.strength, W.strength_type, W.dosage_form
    , W.curr_date, W.in_quant, W.out_quant, W.expiry_date
    , W.available_stock, W.brand, W.ID
From warehouse As W
Where  ( ( ( W.active_subs ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_act_sub & "*" )
AND ( ( W.strength ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_strength & "*" )
AND ( ( W.strength_type ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_strength_type & "*" )
AND ( ( W.dosage_form ) LIKE
"*" &
Forms!Pharmacy.form!pharmacy_warehouse_stock_Subform.form!warehouse_stock_dosage_form & "*" ) )
    And Exists  (
                Select 1
                From drugs As D1
                Where D1.active_substance = W.active_subs
                    And D1.strength = W.strength
                    And D1.strength_type = W.strength_type
                    And D1.dosage_form = W.dosage_form
                )
0
source

More articles:


All Articles