在查询中计算表达式 [英] Calculated Expressions in a Query

问题描述

主题标题可能不是很清楚，但我不能想如何恰当地说出我的问题。这里是：


我有一个表格，其中包含文本框，供用户输入第一次约会的信息，然后是子表单（连续），以便他们将后续约会输入（日期和结果框） 。在12周和6个月后（从第一次预约开始），安排预约以检查客户的进度。


我需要做的是生成一个查询，告诉我这些日期是什么时候（12周和6个月以后）。然后，这些将进入报告，以便用户可以从表单上的下拉框中选择一个月，并且它将显示在所选月份中具有12周或6个月更新约会的所有客户端。


现在，这听起来相当简单，但我遇到了一些问题......如下：


有奇怪的场合当客户因多种原因不参加第一次预约时 - 这些是从结果下拉框中选择的（已取消，已重新安排，无法检查等）。显然，由于显而易见的原因，从这一天开始安排12周/ 6个月的更新预约是没有多大意义的。

我试图在查询中运行一些IIf表达式，如果第一个约会结果=有效，则仅添加12周/ 6个月的日期。然后，如果结果没有参加，那么它将从后续表格中取出日期，并从那里计算出12周/ 6个月的约会日期。

我认为这有效，但由于一些客户有几个后续约会，当每个客户只需显示客户名称和12周/ 6个月日期时，查询会为每个客户显示几个字段。我的查询感觉它包含了太多计算的表达式而不是必要的，并且在今天的大部分时间里玩了之后我已经画了一个空白。


我知道这个可能没什么意义，但我已尽力解释这个问题。我正在研究的数据库是由其他人创建的，我已经被选中试图通过多种方式对其进行改进，所以我正在和一个非常温和的野兽一起工作。


任何帮助都会受到大力赞赏，因为我完全陷入困境！我可能忘记提及一些事情，如果需要更多信息，那么我会尝试提供它！


干杯

解决方案

如果你能以我在我的例子中使用的相同方式发布表的元数据（关于布局/结构的信息），这将更好。单击回复按钮，您将可以访问我使用过的所有代码。 PK& FK代表Primary Key&外键分别为。从不使用TAB，因为布局变得混乱。使用空格，一切都很好。
表名= [ tblStudent ]

展开 | 选择 | Wrap | 行号

好的，这是表约会的信息。但是，我使用的查询需要另外两个表（Person and Practitioner）的信息，如果你需要那些信息，请告诉我......

表名= [ tbl_Appointment ]

展开 | 选择 | Wrap | 行号

嗯，就在我发布之后，我发现了我可以查询当前的查询（我认为）已经解决了我的问题...


你可以说这是我第一次使用Access从第6次开始！


编辑：实际上，我想我可能需要一些帮助诅咒表达，我目前有这个（和12周专栏相同，但显然，6个月的部分被12周的部分取代！）：

展开 | 选择 | Wrap | 行号< /跨度>

The thread title probably isn''t very clear but I can''t think how to word my problem properly. Here goes:

I have a form that has textboxes for users to enter information for first appointments and then a subform (continuous) for them to enter subsequent appointments into (Date and Outcome boxes). After 12 weeks and 6 months (from the first appointment) an appointment is arranged to check progress of the client.

What I need to do is produce a query that will show me when these dates are (12 weeks and 6 months onwards). These will then go into a report so the user can select a month from a drop down box on a form and it will display all clients that have either a 12 week or 6 month update appointment in the selected month.

Now, this sounds fairly straightforward but I have ran into some problems... which are as follows:

There are the odd occasions when the client will not attend the first appointment for a number of reasons - these are selected from the Outcome drop down box (cancelled, rescheduled, uncontactable etc). Now obviously, it wouldn''t make much sense to arrange a 12 week/6 month update appointment from this date for obvious reasons.
I have attempted to run some IIf expressions in the query to only add the 12 week/6 month dates if the first appointment outcome = attended which worked. And then if the outcome didn''t = attended then it would take the date from the subsequent form and work out the 12 week/6 month appointment dates from there.
I think this worked but because some clients have several subsequent appointments, the query displays several fields for each individual client when it only needs to display the client name and 12 week/6 month dates. My query felt like it contained way too many calculated expressions than were necessary and after playing around with it for most of today I''ve drawn a blank.

I''m aware this probably makes little sense but I''ve tried to explain this as best I could. The database I''m working on was created by someone else and I have been drafted in to try and improve it in a number of ways so I''m working with quite a tempermental beast so to speak.

Any help would be massively appreciated as I''m completely stuck really! I''ve probably forgot to mention something so if anymore information is needed then I''ll try and provide it!

Cheers

解决方案

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I''ve used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Expand|Select|Wrap|Line Numbers

Okay, here is the information for the table Appointments. However, the query I am using requires info from 2 further tables (Person and Practitioner), let me know if you need the info from those too...

Table Name=[tbl_Appointment]

Expand|Select|Wrap|Line Numbers

Hmm, just after I posted that, I figured out that I could query the current query which (I think) has solved my problem...

You can tell this is the first time I''ved used Access since 6th form!

EDIT: Actually, I think I may need some help with my calculated expressions, I currently have this (and the same in the 12 week column although, obviously, the 6 month parts are replaced with 12 week parts!):

Expand|Select|Wrap|Line Numbers

这篇关于在查询中计算表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！