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!

 

[Delindan]

I haven't done a query through sql however there's no time like the present. I get lost at FROM data, qgrpMonthStartEnd. It doesn't look like qgrpMonthStartEnd is defined anywhere(bear with me please...)

 

[dhookom]

qgrpMonthStartEnd is a query based on a table I use that has every date in it. My table name is tblCalendar and it has a single field CalendarDate with every date from long ago to far into the future. This query returns the start and end dates of each month:

SELECT tblCalendar.CalendarDate AS MonthStart, DateAdd("m",1,[CalendarDate])-1 AS MonthEnd
FROM tblCalendar
WHERE (((Day([CalendarDate]))=1));

You should create the table and possibly use Excel to quickly create all the dates. Then build qgrpMonthStartEnd so you can create your crosstab query.

Can you respond to my previous question regarding your dates?

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

My mistake on the dates. I was trying to create 3 records that would encompass 3 job scenarios...one where the job started before the start of the fiscal year and ended before the end, one that had no start, and one that started after the beginning of the fiscal year and had no end date so I could be sure I was grabbing what I wanted.

 

[dhookom]

Ok, so did you create the table and queries?

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

You may have guessed I am struggling through this and couldn't figure out how to do the table in Excel in the amount of time I had however I went back in and edited my query and dates in the records and did get correct results. I think the lesson for me here is to go through the process of actually writing out the records, parameter and expected results. I would still like to try and do the query the way you suggested again not clear on what the excel table should look like and how to fill it.

what form to list the dates?
1/1/1995
1/2/1995 etc
and how does this help figure the beginning and end of each month?

Thanks!

 

[dhookom]

The dates are in a date type field and include all dates that you might need. If you want, you can create table with only start of every month:
[tt]
MonthStart
=============
1/1/2011
2/1/2011
3/1/2011
-- etc --
12/1/2020 (or whatever)
[/tt]
This could be used in place of the query qgrpMonthStartEnd.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

thx...I'll give it a try