By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Calculated Expressions in a Query

100+
P: 109
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
Oct 20 '09 #1

✓ answered by NeoPa

@hedges98
You can query other queries of course, but you can also use SubQueries (See Subqueries in SQL) within your query. Generally using existing queries makes more sense when they are needed for other purposes too in their own right, or when you already have them available.
@hedges98
should be written as :
Expand|Select|Wrap|Line Numbers
  1. IIf([Per_Closure_Status_ID] In(2,5),DateAdd('m',6,IIf([App_First_Outcome_ID]=1,[App_FirstDate],[App_Date])),Null)
At least that's the best I can do without a better understanding of what you're trying to do. It may be that your [Per_Closure_Status_ID] test should be in a WHERE clause as a filter instead.

Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,433
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
  1. Field           Type      IndexInfo
  2. StudentID       AutoNumber    PK
  3. Family          String        FK
  4. Name            String
  5. University      String        FK
  6. Mark            Numeric
  7. LastAttendance  Date/Time
Oct 20 '09 #2

100+
P: 109
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
  1. Field               Type      IndexInfo
  2. App_ID           AutoNumber        PK
  3. App_Per_ID       Number        
  4. App_Date         Date/Time
  5. App_Outcome_ID   Number        
  6. App_Comments     Text
  7. App_FirstDate     Date/Time
  8. App_FirstComments Text
  9. App_First_Outcome_ID Number
Oct 21 '09 #3

100+
P: 109
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
  1. 6MonthCalc: IIf(([Per_Closure_Status_ID]<> 2 Or 5),IIf(([App_First_Outcome_ID]=1),DateAdd("m",6,[App_FirstDate]),IIf(([App_First_Outcome_ID]<>1),DateAdd("m",6,[App_Date]))))
Basically, if either 'Closed' (2) or 'Never Seen' (5) are selected from the Closure Status drop down then I don't want the 12 week/6 month milestones to be calculated. However, using this it still calculates values if 'Never Seen; (5) is selected. Should I be using something other than 'Or'? It appears to be working fine other than that.

Sorry if this makes no sense!
Oct 21 '09 #4

100+
P: 109
Right, sorry for the continuous posting (I should probably refrain from posting whilst I'm toying around with Access!) but I think I've sussed it although it's a bit of a botch job.
I altered the code above to something less complicated and more readable:
Expand|Select|Wrap|Line Numbers
  1.  IIf(([Per_Closure_Status_ID]<>2),DateAdd("m",6,[App_FirstDate])))
And then added Per_Closure_Status_ID to the query and in the Criteria part set it to <> 5 so the query wouldn't display the 'Not Seen' statuses. It appears to be working but I'm sure there is a more professional way than this though.
Oct 21 '09 #5

NeoPa
Expert Mod 15k+
P: 31,433
Hedges, you're doing fine.

Let me just see if I can post a few tips related to some of what you've posted, then you can tell mewhere you want to go from there. Whether you want help with the original question or you're happier proceeding by batting ideas backwards and forwards.

BTW. Nice job on the Meta-Data. So many posters fail to make a decent job of that. Well done.
Oct 21 '09 #6

NeoPa
Expert Mod 15k+
P: 31,433
@hedges98
You can query other queries of course, but you can also use SubQueries (See Subqueries in SQL) within your query. Generally using existing queries makes more sense when they are needed for other purposes too in their own right, or when you already have them available.
@hedges98
should be written as :
Expand|Select|Wrap|Line Numbers
  1. IIf([Per_Closure_Status_ID] In(2,5),DateAdd('m',6,IIf([App_First_Outcome_ID]=1,[App_FirstDate],[App_Date])),Null)
At least that's the best I can do without a better understanding of what you're trying to do. It may be that your [Per_Closure_Status_ID] test should be in a WHERE clause as a filter instead.
Oct 21 '09 #7

NeoPa
Expert Mod 15k+
P: 31,433
@hedges98
It looks as if you got pretty close all by yourself.

How about posting the full SQL and I'll see if I can make some better sense of it. I'll comment on anything I see.
Oct 21 '09 #8

100+
P: 109
NeoPa - That's all awesome help and incredibly useful for the future, thanks very much. I went back through and added the 'In(2,5)' bit in place of what I had so it's tidier and more practical.

I've managed to produce the report that was required now but I imagine I'll be back on this forum in the near future for help with more report-y stuff!

As an aside - should I rename the thread (if that's possible on these forums?!) to something that makes more sense to what this thread is about?

Thanks again
Oct 22 '09 #9

NeoPa
Expert Mod 15k+
P: 31,433
@hedges98
We'll look forward to it. Welcome to Bytes!
@hedges98
Generally an appropriate title helps both you and those coming after you, but yours isn't too bad already. If you'd like to though, simply post or PM me the new title & I'll do it for you. Only moderators can update posts once the post-editing delay is up.
Oct 22 '09 #10

100+
P: 109
Well, I think you're much more well versed in this area so it'd probably be better if you renamed it! Something like 'calculated expressions in a query help'? Perhaps something less generic...
Oct 22 '09 #11

NeoPa
Expert Mod 15k+
P: 31,433
That's been done now. It's never a good plan to include the word "Help" in a title mind.
Oct 23 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.