如果另一个中有数据，则更新一个表中的字段 [英] Update a Field in One Table if There is Data in Another

问题描述

我已经使用数据库多年，但从未达到过这个水平。


i设置了一个数据库，其中两个表通过ID上的关系链接，一个用于预订，一个用于餐饮;这一切都运行正常，但我不想在预订表上提供信息，说明是否有餐饮，而无需返回其上的子表格或数据表视图。

我可以获得查询和报告以检查所有这些并且工作正常。但我希望它更新表而不使用queires只是说是或否。


tbl_Bookings

EventID - Autonumber

会议日期 - 日期

餐饮 - 是/否

CateringRequests

EventID - 数字

location - text

数量 - 数字


因此，如果在餐饮请求表中有餐饮，则tbl_bookings中的餐饮将显示是。如果没有显示没有


任何帮助？

解决方案

我会做的是使用查询来告诉你这些信息，而不是存储它的字段。只需在tbl_Bookings和CateringRequests之间进行LEFT JOIN，以便显示来自tbl_Bookings的所有记录，并且只显示匹配的CateringRequests中的记录。使其成为聚合查询，对tbl_Bookings中的每个字段进行分组，然后使用Count（）语句计算匹配的CateringRequests中的记录数。如果你只是想要一个是/否，那么使用IIF（）函数测试值是否为0。

不太清楚你的意思，然后不确定如何让它在其他窗口中显示。


由于我正在使用的数据库是来自另一个文件，我一直试图改变以符合我的目的。


附加的是文件，希望让它更清晰

附加文件

i have been dabling with databases for years but never to this level. i have set up a db with two tables linked via relationships on there ID, one for booking and one for catering; this all works fine, but no i would like to have on the booking table the information to say if there is catering or not without having to go back to the sub form on it or the data sheet view. I can get queries and reports to check all this and that works fine. but i want it to update the table without using queires just to say yes or no. tbl_Bookings EventID - Autonumber Meeting date - date catering - yes/no CateringRequests EventID - number location - text qty - number so if there is catering in the cateringrequest table the catering in the tbl_bookings to then show "yes" if not show "no" any help? 解决方案 What I would do would be to use a query to tell you this information and not have a field storing it. Just make a LEFT JOIN between tbl_Bookings and CateringRequests so that all records from tbl_Bookings show up and only those in CateringRequests that match. Make it an aggregate query, grouping on each of the fields in tbl_Bookings and then use the Count() statement to count the number of records in CateringRequests that match. If you simply want a yes/no, then use the IIF() function to test if the value is 0 or not. not quite sure what you mean, and then wouldnt be sure how to get it to show in other windows. As the db i am working from was from another document which i have been trying to alter to fit my purpose. attached is the file, hope that make it a little clearer Attached Files lite version.zip (340.5 KB, 34 views) Many years ago I did a calendar program that looked similar to yours. The substantial difference was that each of the dates was a subform rather than a text box. That meant that if there were several events on the same day, they could be colour coded. The same thing could happen for catering set to true or false. Additionally, though the entry could not be altered directly on the calendar, a double click could either open another form, or in your case, change the value of Catering from false to true. Something along these lines? A discussion about it can be found: MS Access Calendar - Need help with parameters 这篇关于如果另一个中有数据，则更新一个表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ 登录 关闭 扫码关注1秒登录 发送“验证码”获取 | 15天全站免登陆 友情链接： IT屋 Chrome插件 谷歌浏览器插件 IT屋 ©2016-2022 琼ICP备2021000895号-1 站点地图 站点标签 SiteMap <免责申明> 本站内容来源互联网,如果侵犯您的权益请联系我们删除.