Solved Query And Form Design (1 Viewer)

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
I have looked and you have set the properties in the table for a lot of your fields to be Lookups.

In your Employee table for example the DepartmentID has the following properties set:-
 

Attachments

  • Lookup.JPG
    Lookup.JPG
    20.8 KB · Views: 77

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
I have looked and you have set the properties in the table for a lot of your fields to be Lookups.

In your Employee table for example the DepartmentID has the following properties set:-
Thanks for your quick reply. I am sure you study more and take time thanks again for that.
in this table (LtblDepartment) dont linked within 3 Forms. And showed 3 forms I want to solutions mentioned side mark, Others form i have done.
Thank you..
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
Hi

OK so you do not have tblEmployee currently linked to LtblDepartments

You should link these 2 tables on DepartmentID and then in the underlying tblEmployee the property of DepartmentID should be as follows:-

Also in tblEmployee you should not have a field for Gross Salary.

You should have a separate table to record the Employees Salaries. Over time they will get increases in salary and you should record a DateFrom and DateTo for each Salary Increase. This way you will maintain a History of Employees Salaries.
 

Attachments

  • Now.JPG
    Now.JPG
    10.5 KB · Views: 70

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
Hi

OK so you do not have tblEmployee currently linked to LtblDepartments

You should link these 2 tables on DepartmentID and then in the underlying tblEmployee the property of DepartmentID should be as follows:-

Also in tblEmployee you should not have a field for Gross Salary.

You should have a separate table to record the Employees Salaries. Over time they will get increases in salary and you should record a DateFrom and DateTo for each Salary Increase. This way you will maintain a History of Employees Salaries.


Brilliant answer, Thank you so much, As soon I updated it.
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
Hi

OK so 1 Employee has a Basic Salary.

You then want to be able to :-

1. Add any Monthly Allowances
2. Deduct any Monthly Absence Days
3. Deduct any Monthly Deductions
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
Hi

OK so 1 Employee has a Basic Salary.

You then want to be able to :-

1. Add any Monthly Allowances
2. Deduct any Monthly Absence Days
3. Deduct any Monthly Deductions

Exactly, Like that, all of deduction + Overtime from Basic salary.
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
What Rate of Pay is Overtime?
Also I need your complete database to give you an example. Each time you upload a copy of the database there are tables missing?
 

plog

Banishment Pending
Local time
Today, 13:04
Joined
May 11, 2011
Messages
11,611
You need to stop and go back to basic table design.

Lookup fields are the least of the issues, you have a spider web of relationships. You should only be able to trace 1 path between tables in your relationships, yours allows multiple paths which is incorrect.

For example, its possible to connect tblEmployee to tblAllowanceData in at least 5 diferent ways:
tblEmployee-tblAllowanceData
tblEmployee-tblContract-tblPayroll-tblAllowanceData
tblEmployee-tblPayroll-tblAllowanceData
tblEmployee-tblAbsentDates-tblPayroll-tblAllowanceData
tblDeductionProcedure-tblPayroll-tblAllowanceData

I don't know your data well enough to know which is the correct one, but I know data well enough to know that what you have is incorrect. You should focus on fixing your table structure before moving to anything else, otherweise you are probably just building band-aids on band-aids.
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
What Rate of Pay is Overtime?
Also I need your complete database to give you an example. Each time you upload a copy of the database there are tables missing?

tblOverTimeRates
OvertimeID- PK
Overtime - Short Text
Multiplier - Number

Double Time = 2
Half Time = 1.5
Third/Quarter Time = 1.25
Normal = 0
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
Hi

As I said I need you to upload the complete copy of your database as I am not willing to recreate / create the tables that are missing.
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
Hi
As I said I need you to upload the complete copy of your database as I am not willing to recreate / create the tables that are missing.
Hey
Thanks for your reply. Sorry, I can't show you all of data or table information, its company privacy violation.
There is no more table of linked or connection with my others table, if you have any question, welcome. and If you like to recreate or New create as my demand in the fields. I am appreciate to you. Thank you.
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
Sorry there are too many tables to recreate to give you an example of how to achieve what you need.

Luck with your project and I will leave someone else to answer any more questions.
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
Sorry there are too many tables to recreate to give you an example of how to achieve what you need.

Luck with your project and I will leave someone else to answer any more questions.
Thanks for another reply. I dont want to others example, Leave it all,
Just help me for frmPayroll and frmabsent field beside questions mark that said,
Why you take pressure to take more other tables ?
Thank you.
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
Sorry I am not putting pressure on you at all. I just need a copy of your database with all tables. I do not need any data in tblEmployees or tblPayroll.

But I do need data in all of your Lookup tables as I do not want to have to recreate them.
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
i am afraid i cannot if you want the Whole program/package.
it will take lot of time and code.
show the Excel file and someone, perhaps will convert it to Access.
Hey,
I thinking there is no needs VBA code, Please open the db and read field my beside comments Because, all of field are currency. Actually. this db needs expression formula in the table fields.
Exp: in the tblPayroll from Monthly Deductions & Absence Days with calculated Employees Basic salary and add Monthly Allowances.
Hope you give me a reply. Thank you so much.
 

mike60smart

Registered User.
Local time
Today, 18:04
Joined
Aug 6, 2017
Messages
1,899
In your tblPayroll you have a field named "WorkedDays"

What value do you type in this Field? Is it the Total Number of Days worked in a specific Month?
 

smtazulislam

Member
Local time
Today, 21:04
Joined
Mar 27, 2020
Messages
806
In your tblPayroll you have a field named "WorkedDays"

What value do you type in this Field? Is it the Total Number of Days worked in a specific Month?
Hey thanks for reply.
This field value is Number and it is calculated automatic bcoz, depend of month. Please see the table of LtblMonthName
 

Users who are viewing this thread

Top Bottom