Calculated Expressions in a Query

[hedges98]

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

[NeoPa]

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]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[hedges98]

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]

Field               Type      IndexInfo
App_ID           AutoNumber        PK
App_Per_ID       Number        
App_Date         Date/Time
App_Outcome_ID   Number        
App_Comments     Text
App_FirstDate     Date/Time
App_FirstComments Text
App_First_Outcome_ID Number

[hedges98]

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!):

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!

[hedges98]

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:

 IIf(([Per_Closure_Status_ID]<>2),DateAdd("m",6,[App_FirstDate])))

And then added Per_Closure_Sta tus_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.

[NeoPa]

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.

[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 :

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_Sta tus_ID] test should be in a WHERE clause as a filter instead.

[NeoPa]

@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.

[hedges98]

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

[NeoPa]

@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.