Access 2010:如何使查询结果可更新？
我正在处理我的第一个Access 2010数据库，在编辑从查询返回的记录集时遇到了问题。
我有两张表，"drugs“和"warehouse_stock”。在“毒品”里我有毒品的名字。在另一个表中，我有他们相应的信息。对于那些在“药品”表中的药品，药剂师将管理它们的数量和其他相对值，如每种药品的输入数量和具体的有效期。药剂师可以将同一药物的相同信息导入"warehouse_stocK“，但有效期不同。
active_substance/strength/strength_type/dosage_form.是"warehouse_stock“中的每一行数据的特征这四个值必须在"drug“中的一行数据中相同。药剂师将能够更新现有行数据的curr_date、in_quant、out_quant_expiry_date和品牌(从"warehouse_stock")，并导入“药品”中存在的新数量的药品。
我现在的问题是：
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 & "*" ) );有没有办法让结果是可更新的？
提前感谢您的任何建议！
齐诺纳斯
回答 3
Stack Overflow用户
发布于 2012-05-15 19:24:21
尝试：
<...>
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 )Stack Overflow用户
发布于 2012-05-16 04:33:19
您的需求根本不清楚，标题与您对问题的文字描述非常不同，所提供的查询甚至是不同的。
如果像active_subs，strength，strength_type，dosage_form这样的字段在两个表中应该有相同的值，那么你的设计可能是有缺陷的。你应该把它们从一张表中去掉。为什么不将这些字段放在一个表中并通过id将它们连接起来呢？
编辑：在无休止的讨论之后，这是我所理解的。有一个很大的沟通差距，所以我相信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想要更新什么值。在update查询中使用合适的where子句。
Stack Overflow用户
发布于 2012-05-16 23:29:20
为了使这个表可更新，您需要删除From或Where子句(直到现在)中没有使用的连接。但是，您提到您正在尝试仅过滤那些具有drugs值的warehouse结果。为此，您应该使用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
)https://stackoverflow.com/questions/10598702
复制相似问题
腾讯云开发者
