Running months data in query (1 Viewer)

Hi,

I have a query where I have allotted various allowances to employees. It is as follows:

EmpName; Desig; AllowName; AllowValue; AllowGivenFromDate.

I am now trying to build a query where it gives me employee names and relevant allowances details but only from the "GivenFromDate" onwards in "monthwise" data. Like for example, for Mr.ABC who was given car allowance from 1st Dec 2017:

Mr.ABC; Worker; CarAllow; $12345; Dec2017
Mr.ABC; Worker; CarAllow; $12345; Jan2018
Mr.ABC; Worker; CarAllow; $12345; Feb2018
Mr.PQR; Manager; RentAllow; $65432; Feb2018
and so on.

I do have 3 tables called Days, Months, Years. They contain numbers from 1 to 31; 1 to 12 and years 2017 and 2018 for Days, months, years tables respectively. I would really appreciate if someone helps me with this problem. Thanks

I would venture a guess that you may have a data problem.
Is AllowGivenFromDate an actual date datatype of is it text datatype?
what is the purpose of the 3 tables for month day years?

Further to moke123's comments, what exactly is the purpose of this database?
You seem to be dealing with
Employees where each Employee has an EmployeeType
Employees may receive an Allowance(s)
Allowance is assigned an AllowanceType
Allowance has a specific StartDate (but seems to lack Duration)
Allowance has a defined Value (Dollar)

You should describe how these subjects relate to one another in your environment.
Dates should have Day Month and Year

and so on.....

Click to expand...

(the devil is always in the details.)

Could you show some "raw" data from the tables you're using in the query, either in an Excel sheet or MS-Access database.

Task Objective: To assign and store fixed allowances of sales force people.

The AllowGivenFromDate has datatype of 'Date/Time' with format selected as 'Medium Date'.

Sales people join and resign during the year. They may or may not be entitled to fixed allowances. I want a query where if fixed allowance(s) is assigned to a person, it gets repeated every month till the current month unless the employee resigns some way during the year. For this purpose, I created the months and years tables with datatypes 'Numbers' and was thinking of some kind of cartesian product of these with the query containing allowances and their 'GivenFromDate' so as to get the above mentioned result in my first post. If successful, I would have then used this result in generating various reports. like fixed allowances report for all sales force for the selected month only or any time period desired.

i have a separate tables for employee names; Allowances. Their structure is as follows:
tblAllows: AllowCode(datatype:Text), AllowName(datatype:Text), AllowAmt(datatype:Text).
tblEmps: EmpCode(datatype:Text), EmpName(datatype:Text), EmpDesig(datatype:Text).

I hope this helps.

princeofdumph said:

I want a query where if fixed allowance(s) is assigned to a person, it gets repeated every month till the current month unless the employee resigns some way during the year.

Click to expand...

How do you wish to store this?
Are you planning to have an append query run once per month to add the current allowances for the current month? Something else?

Dear Mark, I am trying to accomplish this via simple select queries or an SQL query may be.

i have a separate tables for employee names; Allowances. Their structure is as follows:
tblAllows: AllowCode(datatype:Text), AllowName(datatype:Text), AllowAmt(datatype:Text).
tblEmps: EmpCode(datatype:Text), EmpName(datatype:Text), EmpDesig(datatype:Text).

Click to expand...

Looking at the above many questions come to mind and that's the reason I commented on a possible data problem. How are the tables related?

When designing your tables you need to put a lot of thought into what changes may occur in the future and how that may effect data from the past. For instance Joe currently gets a $100 car allowance and has for the past year. But Now Joe's allowance needs to go up to $150 a month. So you would need a table just to track allowance rates per employee with a start and end date. This way all past records are unaffected by the change.

You also have to consider how to deal with employees that get hired or fired or promoted or demoted, etc. How do you determine their current or past employment status?

It would be helpful if you posted all your table structures or a stripped down copy of your DB.

princeofdumph said:

Dear Mark, I am trying to accomplish this via simple select queries or an SQL query may be.

Click to expand...

If you are looking to do this in a query, can you please explain in simple terms the business rules you are trying to implement?

Something like
"Each person in list A gets an allowance"
"Each month, all people on list A get a payment added to their wages and we record it as a journal entry charged against the allowance expense account"

Please also list the tables that are included in replicating the above.