Weekly calculation query?

[katie_88]

Hi guys,

Ok well i've got a query with how long it takes to get specialist diabetes appointments in weeks as shown in the attachment with the datasheet and design views of the query.

here we have the SQL coding:

SELECT [Time taken].Month, [Time taken].ArrivalDate, [Time taken].AppointmentDate, [Time taken].[Time Taken], [Time taken]![Time Taken]/7-0.4 AS [Time in weeks]
FROM [Time taken]
WHERE ((([Time taken].Month)=#5/1/2010#))
ORDER BY [Time taken].Month, [Time taken].[Time Taken];


Now what i'd love to be able to do is group the number of appointments that are are seen in each week to give a total. For example, if there were 2 appointments seen within 1 week, another 3 appointments within the second week then it'd would be great if the query could show this.. ideally with the total number of patients seen each week within weeks 1-8 then with a total number of patients seen after 8 weeks i.e. 8 week plus total, with the weeks as headings so something like...



week1 week2 week3 week4 week5 week6 week7 week8 week8+
1......... 2........ 3....... 5...... 2....... 1....... 2....... 3....... 20

(the dots are only there because it kept bunching the numbers at one end for some reason)

Pretty lost on this so any help would be great!!!

Thanks!!
Katie x

[jzwp11]

Welcome to the forum!

First, I would recommend changing your field called "month". The word "month" is a reserved word in Access. Reserved words should not be used as table or field names.

When you say week #, do you mean the week number relative to the beginning of the year? If so, you can use the datepart() function to return the weeknumber: datepart("ww", somedatefield) There are a couple of optional terms to the function, so you will want to review the help section.

As to how you want to display your data, you will need a crosstab query.