Question How do you make relationship with this sort of data?

[k31453]

Hi..my purpose of this to make accurate wages for employee

What am i doing at the moment is that i am ceating 4 tablea

Table 1.
Employment details

firstname..last name..gender..houes worked ...employees id...has parked...



Table 2: login details of employees

Username ..employees id...password
this table is for security


Table 3: setion data

which includes section...base hour.
.hours working parking and stuff


Table 4: tax data

which i create same as above worksheet

from... too... tax rate

they said in sheet that rate cant be hard wired values so it can be change..and therefore when i will have to count the net pay and gross income...if i change rate i need actual change in the net income and etc.

first of all i know i m asking u big favour but the real problem is that how u can make relationship out of it ...which is difficult part...

so will u help me or at least any advice or guidence..????? please??

 

[jdraw]

Research Normalization and get your tables designed before getting into Programming and Forms.

Review the Entity Relationship Diagramming here
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[k31453]

i did mate but cant able to do it

 

[Pat Hartman]

they said in sheet that rate cant be hard wired values so it can be change

What they mean by that is they want you to define a table to hold the rates. They don't want you to use an IIf() or some other code method to determine the rate. You will need to create a query with a non-equi join to lookup the rate. I'll code a little of it. You change the names as needed and add the additional columns.

Select GrossPay, TaxRate, GrossPay * TaxRate as TaxAmt, (GrossPay * TaxRate) + GrossPay as NetPay
From EmpInput Inner Join TaxRates on GrossPay Between FromAmt AND ToAmt;

Here's a best practices lesson - notice that none of my column or table names contain embedded spaces or special characters.

 

[k31453]

What they mean by that is they want you to define a table to hold the rates. They don't want you to use an IIf() or some other code method to determine the rate. You will need to create a query with a non-equi join to lookup the rate. I'll code a little of it. You change the names as needed and add the additional columns.

Select GrossPay, TaxRate, GrossPay * TaxRate as TaxAmt, (GrossPay * TaxRate) + GrossPay as NetPay
From EmpInput Inner Join TaxRates on GrossPay Between FromAmt AND ToAmt;

Here's a best practices lesson - notice that none of my column or table names contain embedded spaces or special characters.

yeah i got that but it saya if we change rate the net income all stuff has to change..

thats what i want look at the sheet i think and thanks

 

[Pat Hartman]

That will happen. Each time the query runs, it picks up the current values.

 

[k31453]

Select GrossPay, TaxRate, GrossPay * TaxRate as TaxAmt, (GrossPay * TaxRate) + GrossPay as NetPay
From EmpInput Inner Join TaxRates on GrossPay Between FromAmt AND ToAmt;

so u hqve to put this one equation in query...and what is toamt and stuff??

 

[k31453]

but i think withour relationship its not possible...so i m worrying about relationship at the moment

 

[jdraw]

There are free data models at www.databaseanswers.org some of these may help you with tables and relationships. But none will be an exact fit.
This one deals with Payroll and may help
http://www.databaseanswers.org/data_models/payrolls/index.htm

You really do need to have a clear understanding of the business issue/problem/opportunity if you are going to automate a solution. It seems to me that you are not clear in what is being asked of you.
Good luck with your project.

 

[Pat Hartman]

You can't create a relationship involving the tax table. It has no PK field that you would use as a FK. You have to lookup the rate by joining to the table using a value range.

Relationships are used to enforce referential integrity and apply cascade update and/or cascade delete. Joins in queries are used to specify how the data in one table is used to find data in another table. Normally joins will mirror relationships but not always. This is a case where they don't.

 

[k31453]

You can't create a relationship involving the tax table. It has no PK field that you would use as a FK. You have to lookup the rate by joining to the table using a value range.

Relationships are used to enforce referential integrity and apply cascade update and/or cascade delete. Joins in queries are used to specify how the data in one table is used to find data in another table. Normally joins will mirror relationships but not always. This is a case where they don't.

so how do u create look up query for text is it like normal query??? and also how. do i deal with the range of the tax

 

[k31453]

You can't create a relationship involving the tax table. It has no PK field that you would use as a FK. You have to lookup the rate by joining to the table using a value range.

Relationships are used to enforce referential integrity and apply cascade update and/or cascade delete. Joins in queries are used to specify how the data in one table is used to find data in another table. Normally joins will mirror relationships but not always. This is a case where they don't.

i dont know about lookup and thing i m just begginer trying to learn see

this is my database

i didnt make realationship


and sorry but how do u join the table to range

and i tried to do it but when i run the query it gives me blank thing...

and the code u give me

i cant understand

View attachment Database1.accdb