create query fields based on parameter

[Delindan]

I have a table that is basic employee information including a budgeted and actual start dates as well as budgeted salary and actual salary. I would like to create a query that would calculate a monthly budget based on the fiscal year the user chose. So for example, I would like to be able to calculate the budgetd salaries for October (budeted salary/12) if the budgeted start date if before October of the users year parameter. It seems like it should be able to be done. any ideas?

Thanks!

 

[PWise]

Select Sum(budeted salary)/12
from tablename
where month(actual start dates)>=[prammonth]
and Year(actual start dates)>=[pramyear]

 

[Delindan]

thank you! I'm sure this is so basic however where do you pull the requested parameter (in this case it would be year.) Because I will have 12 months of budget summaries (b1, b2 etc) that would use that parameter.

Thanks

 

[PWise]

from your 2nd post i think that i dont understatd your needs can u please elabarate some more

 

[Delindan]

I think I don't know how to ask properly...sorry. Their fiscal year runs from Sept to August and would just like an easy way to figure out the monthly salary expense based on an employee's start and end employment dates and getting myself all messed up. I would like it to be flexible enough that they can bring up any year's information. Hope that makes the above questions more clear.

 

[dhookom]

Delindan,
Please provide some significant table and field names. It would also help if you provided some records and desired output.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Using the fields position (job number 101), annual salary, start date (01/01/1998), end date (3/1/2011), calc the monthly salary costs for the fiscal year starting 9/1/2010 thru 6/1/2011.

From what I've come up with, calculated fields for each month of annual salary/12 for Sept it would be records that have a start date <= 10/01/start year and end date >= 10/01/start year where start year and end year are input by user. Because their fiscal years don't follow one calendar year they name them fy10 for example which would start 9/1/09 and go thru 8/30/10. I could have a table that would assoc start and end date with a particular fiscal year name.

My question is this: Can you create a query that uses a user input variable in each month's calculation? I haven't figured out how to do that. It's totally possible I'm making this way harder than it needs to be...maybe the answer in more obvious.

Thanks!

 

[dhookom]

What do you expect to see if the start or end date isn't the beginning or end of a month? I would expect one solution might involve a table of all months with a field that identifies the Fiscal Year.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Here is what I think I'd like to do: By position number list the budgeted salary dollars for each month (once I get this working I"ll be able to do one for actual and forecast as well)
for month 1...B1: IIf([Head Count]![Budgeted Start]<[FY]+30,([Head Count]![Budgeted Annual Salary]/12),0)

where [FY] is the date of the start of the fiscal year. For example: 1999 which would be input by the user. I can't get it to work though syntax...

 

[dhookom]

Delindan,
Can you please take the time to type in about 5-6 records with the actual table and field names and then how you would want these to appear in your query results?

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I am going attach the file I'm working on. I believe I have the budget query worked out. The actual dollars query is where I am having trouble. The fiscal year runs from 9/1/year to 8/31/year. I am trying to capture actual salary dollars which includes current employees (they have no end date) and those that quit working but had worked during the period being reported. I tried splitting into two separate queries and still not getting the appropriate results. I have 3 records but I believe they capture the different scenerios.

Thanks!

 

[dhookom]

Can't access your file from work computer. Maybe someone else can assist or you could take the time to type in about 5-6 records with the actual table and field names and then how you would want these to appear in your query results.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Job # 101
Forecast Start Date null
Actual Start Date 10/1/2010
Actual Annual Salary 100000
End Date 5/3/2011

Job # 102
Forecast Start Date 7/15/2011
Actual Start Date null
Actual Annual Salary null
End Date null

Job # 103
Forecast Start Date null
Actual Start Date 12/1/2011
Actual Annual Salary 45000
End Date null

The purpose of my query is to capture actual salary costs for a fiscal year that runs from September to September. Using a input parameter [Fiscal Year: ] which input would be 9/1/2011, would calculate actual salary costs. I tried creating an expression for month 1 of IIF(actual start date < dateadd("m",1,[Fiscal Year: ]) AND End Date is null or > dateadd("m",1,[Fiscal Year: ]), (actual annual salary/12)). Needless to say I couldn't get it to work. I also tried just putting the expression (actual annual salary/12) with the criterea below which also didn't work. I tried creating two separate queries....one for those employees still working and those who quit mid year without luck. It seems like it should be straight forward but I can't get it to perform the right calculation. Thanks for your help!

 

[PWise]

how about

Select Sum(Actual Annual Salary)
from table 
where [Fiscal Year:  ] between [Actual Start Date] and nz([End Date],[Fiscal Year:  ] )

this should give you the total of Actual Annual Salary that that have started before the fiscal year started and have not ended yet and not ended

 

[dhookom]

I personally wouldn't answer until we understood:
Current data
[Your table Name Here]
[tt]
[Job #] [Forecast Start Date] [Actual Start Date] [Actual Annual Salary] [End Date]
101 null 10/1/2010 100000 5/3/2011
102 7/15/2011 null null null
103 null 12/1/2011 45000 null
[/tt]

[Your criteria/specification listed Here]

Expected display based on a query criteria:
[tt]
[Column 1 Name] [Column 2 Name] [Column 3 Name]
Data 1 Data 2 Data 3
Data 1 Data 2 Data 3
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

The expected display for the query would be job number and months 1-12 of actual salary results. So if someone worked the first three months of the fiscal year it would be annual salary/12 for those 3 months and 0 for the remainder.

Thanks

 

[dhookom]

Delindan,
I'm not sure I can be more specific regarding what I think you need to provide for us to help you. I took the time to format the data and also the expected display. Can you at least provide similar efforts in typing in the actual records and actual records returned? Nothing you have provided looks anything like a datasheet view of records.

I hate to be so demanding but if you want assistance, you should attempt to provide the information that is asked of you. If you don't understand then please say so.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

good grief...so sorry here is what I believe you're asking for:

data:
job # start date salary end date
101 10/1/2010 100,000 5/3/2011
102 null null null
103 12/1/2011 45,000 null

User Input: 9/1/2011 (start of fiscal year)

query output:
job # a1: a2: a3: a4: a5: a6: a7: a8: a9: a10: a11: a12:
101 8333 8333 8333 8333 8333 8333 8333 0 0 0 0 0
102 0 0 0 0 0 0 0 0 0 0 0 0
103 0 0 0 3750 3750 3750 3750 3750 3750 3750 0 0

so as of june 2011, these are the actual salary expenses so far for the fiscal year starting 9/1/2011.

 

[dhookom]

If 101 ends before 103 even begins, why are there months with salary expenses for both? It looks like 101 ends before the fiscal year 9/1/2011 begins.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

If I understand you correctly, I would create a table/query with the starting date of every month. Then create a crosstab query like:

PARAMETERS [Enter FY Start] DateTime;
TRANSFORM Sum([Salary]/12) AS MthlySalary
SELECT data.[Job #]
FROM data, qgrpMonthStartEnd
WHERE (((qgrpMonthStartEnd.MonthStart) Between [Start date] And nz([End Date],#12/31/2020#)) AND ((qgrpMonthStartEnd.MonthStart) Between [Enter FY Start] And DateAdd("yyyy",1,[Enter FY Start])))
GROUP BY data.[Job #]
PIVOT "A" & Format(DateDiff("m",[Enter FY Start],[MonthStart]),"00") In ("A01","A02","A03","A04","A05","A06","A07","A08","A09","A10","A11","A12");

Duane
Hook'D on Access
MS Access MVP