Fiscal Year

[uzairahm]

Hi!

I wanted to modify the query the query is given below.

SELECT Format([OpDate],"yyyy") AS [Year], ProcFees.ProcName, Count(ProcFees.ProcName) AS CountOfProcName
FROM ProcFees INNER JOIN (Procedures INNER JOIN ProcDetails ON Procedures.[Order ID] = ProcDetails.[Order ID]) ON ProcFees.[Proc ID] = ProcDetails.[Proc ID]
WHERE ProcFees.ProcName<>"General Anaesthetic" And ProcFees.ProcName<>"Local Anaesthetic"
GROUP BY Format([OpDate],"yyyy"), ProcFees.ProcName
HAVING ((Format([OpDate],"yyyy")<>"IsNull") And (ProcFees.ProcName<>"IsNull"));

The above query generates following results:

Year Procedure Year Total
2000 Abdominoplasty 4
2000 Bilat Pinnaplasty 3
2001 Abdominoplasty 1

I actually gives the procedure and its total for the year starting at 1st January and ending at 31st December.i want to edit the query so it may provide me results starting at 1st Apr and ending at 31st March. i.e for year 2006 should now start at 1st Apr 2006 and end at 31st March 2007 and for year 2007 i would start at 1st Apr 2007 and end at 31st March 2008.

I am unable to create any such query.
Could anyone help me out please. It would be really apreciated if you could give me the exact query for the solution.

Thanking in Advance.
Uzair

[MMcCarthy]

You can't do this with a simple query. You could work out the criteria to return the data for one fiscal period but not to group the data by that fiscal period.

You will need to introduce a new column to the table for the FiscalYear with a value something like "2006/2007"

Then create a update query like the following to populate the column.

Update TableName SET FiscalYear = IIf(Month([OpDate])>3, """ & Year([OpDate]) & "/" & Year([OpDate])+1 & """, """ & Year([OpDate])-1 & "/" & Year([OpDate]) & """
 

Once you have done that you can now run the query as follows:

SELECT ProcFees.FiscalYear, ProcFees.ProcName, Count(ProcFees.ProcName) AS CountOfProcName
FROM ProcFees INNER JOIN (Procedures INNER JOIN ProcDetails ON Procedures.[Order ID] = ProcDetails.[Order ID]) ON ProcFees.[Proc ID] = ProcDetails.[Proc ID]
WHERE ProcFees.ProcName<>"General Anaesthetic" And ProcFees.ProcName<>"Local Anaesthetic"
GROUP BY ProcFees.FiscalYear, ProcFees.ProcName
HAVING ((Format([OpDate],"yyyy")<>"IsNull") And (ProcFees.ProcName<>"IsNull"));
 

I haven't touched the Having part as I'm not sure what if anything it is doing.

[uzairahm]

Thanks for u'r intrest.

I am real novice to Access so could you please tell me how would i be able to run the below sql you mentioned

UPDATE Procedures SET FiscalYear = IIf(Month([OpDate])>3, """ & Year([OpDate]) & "/" & Year([OpDate])+1 & """, """ & Year([OpDate])-1 & "/" & Year([OpDate]) & """);

What i have done to run this query was i added a column named FiscalYear in the Procedures database and gave its datatype to Date/Time but following message is shown:

"Procedure didn't update 61 Fields due to type conversion failure, 0 records due to key violation,......."

What should be the data type of Fiscal Year?

And when ever a new procedure would be added, fiscal year would be auto populated or i have to run the above mentioned query again.

I really appreciate the help you have provided.

Thanking in Advance,
Uzair.

[MMcCarthy]

Thanks for u'r intrest.

I am real novice to Access so could you please tell me how would i be able to run the below sql you mentioned

UPDATE Procedures SET FiscalYear = IIf(Month([OpDate])>3, """ & Year([OpDate]) & "/" & Year([OpDate])+1 & """, """ & Year([OpDate])-1 & "/" & Year([OpDate]) & """);

What i have done to run this query was i added a column named FiscalYear in the Procedures database and gave its datatype to Date/Time but following message is shown:

"Procedure didn't update 61 Fields due to type conversion failure, 0 records due to key violation,......."

What should be the data type of Fiscal Year?

And when ever a new procedure would be added, fiscal year would be auto populated or i have to run the above mentioned query again.

I really appreciate the help you have provided.

Thanking in Advance,
Uzair.

The datatype should just be text. You will need to set up a procedure to add the Fiscal Year to new records.

Once we get the query sorted you can tell me how the records are being added. Presumably you are using a form.

[uzairahm]

Thanks a lot i figured it out. I kept FiscalYear's DataType as Text and my problem is solved Thank You so much.

[uzairahm]

weel the input is through form where the record for Procedures is added.
And i entered the update sql as Queries not as procedure.

What would be the method of running this query. Should i ammend it with insertproc query if so show should it be ammended.

regards,
Uzair

[MMcCarthy]

weel the input is through form where the record for Procedures is added.
And i entered the update sql as Queries not as procedure.

What would be the method of running this query. Should i ammend it with insertproc query if so show should it be ammended.

regards,
Uzair

The update query was just a one off to amend your existing records.

On the form you use to add new procedures. In design view you need to drag the FiscalYear Field on to the form from the Field list.

Then go to the control for the OpDate field and create an After Update event as follows:

Private Sub OpDate_AfterUpdate()
    If Month(Me.OpDate) > 3 Then
        Me.FiscalYear = """ & Year(Me.OpDate) & "/" & Year(Me.OpDate)+1 & """
    Else
        Me.FiscalYear = """ & Year(Me.OpDate)-1 & "/" & Year(Me.OpDate) & """
    End If
End Sub
 

[FishVal]

Hi!

I wanted to modify the query the query is given below.

SELECT Format([OpDate],"yyyy") AS [Year], ProcFees.ProcName, Count(ProcFees.ProcName) AS CountOfProcName
FROM ProcFees INNER JOIN (Procedures INNER JOIN ProcDetails ON Procedures.[Order ID] = ProcDetails.[Order ID]) ON ProcFees.[Proc ID] = ProcDetails.[Proc ID]
WHERE ProcFees.ProcName<>"General Anaesthetic" And ProcFees.ProcName<>"Local Anaesthetic"
GROUP BY Format([OpDate],"yyyy"), ProcFees.ProcName
HAVING ((Format([OpDate],"yyyy")<>"IsNull") And (ProcFees.ProcName<>"IsNull"));

The above query generates following results:

Year Procedure Year Total
2000 Abdominoplasty 4
2000 Bilat Pinnaplasty 3
2001 Abdominoplasty 1

I actually gives the procedure and its total for the year starting at 1st January and ending at 31st December.i want to edit the query so it may provide me results starting at 1st Apr and ending at 31st March. i.e for year 2006 should now start at 1st Apr 2006 and end at 31st March 2007 and for year 2007 i would start at 1st Apr 2007 and end at 31st March 2008.

I am unable to create any such query.
Could anyone help me out please. It would be really apreciated if you could give me the exact query for the solution.

Thanking in Advance.
Uzair

Hi!

The following expression will return fiscal year of [OpDate] whish starts at 1st Apr. -3 is the number of last month in fiscal year with "-" sign.

Year(DateAdd("m", -3, [OpDate]))

Good luck.