EmpID selection with multiple tables field in one query

[mba_110]

Hi eveyone,

I am facing problem with following SQL for my query in access DB.

EmpID is the main key to filter the records from multiple table some are connected with tblEmployees and some are connected with tblEmploymentContracts but tblEmployeee (PK) is connected with tblEmploymentContracts (FK).


SELECT tblEmployees.EmpID, tblEmployees.FullName, tblPayroll.Basic, tblPayroll.Currency, tblPayroll.Food, tblPersonalDetails.DOB, tblPersonalDetails.MaritalStatus, tblEmploymentContracts.Department, tblEmploymentContracts.LineManager, tblEmploymentContracts.ContractID, tblEmploymentContracts.JobTitle, tblEmploymentContracts.HireDate, tblEmploymentContracts.Airticket, tblEmploymentContracts.Leave, tblEmploymentContracts.Location, tblPayroll.Trnspt, tblPayroll.CmpPrvdTrnpt, tblPayroll.Housing, tblPayroll.SharedAcomd, tblPayroll.Others, tblPayroll.[Gross_(CCY)], tblPayroll.[Gross_(SAR)], tblLifeInsurance.PremiumAmt, tblLifeInsurance.InsuredAmt, tblMedical.Class, tblMedical.PremiumAmt, tblGosi.Nationality, tblGosi.GrossTotal, tblIqama.Fees, tblLaborOffice.PayAmt
FROM ((((((tblEmployees INNER JOIN tblLaborOffice ON tblEmployees.EmpID = tblLaborOffice.EmpID) INNER JOIN tblEmploymentContracts ON tblEmployees.EmpID = tblEmploymentContracts.EmployeeID) INNER JOIN tblGosi ON tblEmployees.EmpID = tblGosi.EmpID) INNER JOIN tblIqama ON tblEmployees.EmpID = tblIqama.EmpID) INNER JOIN tblLifeInsurance ON tblEmployees.EmpID = tblLifeInsurance.EmpID) INNER JOIN (tblMedical INNER JOIN tblPersonalDetails ON tblMedical.EmpID = tblPersonalDetails.EmpID) ON (tblPersonalDetails.EmpID = tblEmployees.EmpID) AND (tblEmployees.EmpID = tblMedical.EmpID)) INNER JOIN tblPayroll ON tblEmploymentContracts.ContractID = tblPayroll.ContrctID
WHERE (((tblEmployees.EmpID)=[Forms]![frmExtIndividualRpts]![cboEmpId_RC]));




Now i want to filter it for all fields data available in these tables based on EmpID

Any help can will be appreciated.

 

[Ranman256]

in the employee detail form, put a subform of Contacts.
the subform query will show all contacts from tblEmpContacts table.
But when the user is in the frmEmployee form, the subContacts form will only show
names linked to the EmpID in frmEmployee by:

in frmEmployee design,
select subContacts form
in the property , set the LINK MASTER FIELDS = empID
LINK MASTER FIELDS = empID

 

[mba_110]

I am sorry if i make no sense here, I am little confused here what forms have to do with table and query?

And its tblEmployees & tblPersonalDetails and tblEmploymentContracts i dont know which table you are referring as form and subform i am sorry but i am totally muddled.

I have total 9 tables in the query in which tblEmployees and tblEmploymentContracts are major.

Sorry to bother you again, but please make it little clear to understand the above use of forms and subforms in tables vs query.

I want easiest way to filter this information in query after that i am going to call it from dlookup function on my report based on EmpID selected in frmExtIndividualRpts.

 

[The_Doc_Man]

Here's how you do this the quick & dirty way - except that it ain't that dirty.

Open the query designer in the mode wherein you have a grid at the bottom and a blank area at the top. Use the Add Table option to include the tables from which you draw your data. Use the drag-n-drop interface in the top to establish your one-to-one (INNER JOIN) relationships to tblEmployees.EmpID (unless you already have formal relationships that express these same linkages, in which case the query wizard would detect them and use them.)

Then drag-n-drop the fields you want in your query down to the grid. When you are done, save this query. You can switch from design view to SQL view to confirm it, but in essence you probably just want the big select/join mish-mash. Note that depending on how you approach this, Access might balk and require you to "layer" your queries, but since they are ALL of the INNER JOIN variety, it might allow this approach.

Now when you do your DLookup, include "tblEmployees.EmpID = " plus your selection from the form in the criteria argument. OR... if you are opening a report, include that same criteria statement (including your form reference) in the report's filter.

In essence, you build the bulk of this as a stored query. Then you use your form's selection of EmpID as a modifier when you actually employ the query. No need to make it monolithic, and it frees you from future problems if you have to specify employee ID number in a different way that would otherwise want to use the same query. (Like, maybe from a differently named form.) If you lock the data source into the stored query, it is a one-trick pony. If you leave the query generic, it becomes a shared tool for any number of related things that might need that information.

 

[mba_110]

This is not allowing me because EmpID in tblEmployees is a string

I have modified the following as per your guidance, can you tell me where is the problem.


SELECT tblEmployees.EmpID
FROM (tblIqama INNER JOIN (tblGosi INNER JOIN (tblLifeInsurance INNER JOIN ((tblMedical INNER JOIN tblPersonalDetails ON tblMedical.EmpID = tblPersonalDetails.EmpID) INNER JOIN (tblPayroll INNER JOIN (tblEmployees INNER JOIN tblLaborOffice ON (tblLaborOffice.PayAmt = tblEmployees.EmpID) AND (tblEmployees.EmpID = tblLaborOffice.EmpID)) ON (tblPayroll.[Gross_(SAR)] = tblEmployees.EmpID) AND (tblPayroll.[Gross_(CCY)] = tblEmployees.EmpID) AND (tblPayroll.Others = tblEmployees.EmpID) AND (tblPayroll.SharedAcomd = tblEmployees.EmpID) AND (tblPayroll.Housing = tblEmployees.EmpID) AND (tblPayroll.CmpPrvdTrnpt = tblEmployees.EmpID) AND (tblPayroll.Trnspt = tblEmployees.EmpID) AND (tblPayroll.Food = tblEmployees.EmpID) AND (tblPayroll.Currency = tblEmployees.EmpID) AND (tblPayroll.Basic = tblEmployees.EmpID)) ON (tblPersonalDetails.EmpID = tblEmployees.EmpID) AND (tblEmployees.EmpID = tblMedical.EmpID) AND (tblMedical.PremiumAmt = tblEmployees.EmpID) AND (tblMedical.Class = tblEmployees.EmpID) AND (tblPersonalDetails.MaritalStatus = tblEmployees.EmpID) AND (tblPersonalDetails.DOB = tblEmployees.EmpID)) ON (tblEmployees.EmpID = tblLifeInsurance.EmpID) AND (tblLifeInsurance.InsuredAmt = tblEmployees.EmpID) AND (tblLifeInsurance.PremiumAmt = tblEmployees.EmpID)) ON (tblEmployees.EmpID = tblGosi.EmpID) AND (tblGosi.GrossTotal = tblEmployees.EmpID) AND (tblGosi.Nationality = tblEmployees.EmpID)) ON (tblEmployees.EmpID = tblIqama.EmpID) AND (tblIqama.Fees = tblEmployees.EmpID)) INNER JOIN tblEmploymentContracts ON (tblEmploymentContracts.ContractID = tblPayroll.ContrctID) AND (tblEmployees.EmpID = tblEmploymentContracts.EmployeeID);


the error code is "Type mismatch in expression" not allowing me the query view.

 

[Minty]

If EmpID is a number stored as text then change it to a number. Normally (not always) an ID field is a number precisely because you need a unique reference.

The reason being that an EmpID of JohnSmith is not unique as soon as JohnSmith number 2 is employed...

 

[mba_110]

EmpID is a text field with number and words not a number field only and i can't change it as per my DB requirement.

is their any solution to this problem because i have huge number of reports waiting this combination of information to call upon EmpID

I really need to fix it.

 

[Minty]

Well you'll have to change one or the other datatypes. It's your database you can make any changes you need to to make it work correctly.

An EmpID from an Employees table is a very commonly used structure in databases. Making it a value that is convoluted and messy is a design error imho. Your end users never need to see it, if they want to be known as SmithJ00023 you can create as a formatted field in a query or on a form.

If you have to link in with a silly Payroll ID from another system store that in a separate field that you can display if necessary.

 

[mba_110]

I have to change many fields type and in short almost complete project, because [ContractID] is also a text field and both tblEmployees and tblEmploymentContracts has many many tables under their relationships so, its kind of taking a glass of water from leg and not from your hands.

one more thing PayrollID is just a (PK) of tblPayroll and has nothing to do with import or export data from another systems.

 

[Minty]

That's the rub of the green I'm afraid, we all make errors in design sometimes. Much easier to change it before it's being used in anger than afterwards.

Intrinsically there isn't anything wrong with a text primary key field, but generally speaking it means that you are creating it manually, which can be troublesome - particularly for beginners.

New users often seem very obsessed with "meaningful" primary keys, yet every day deal with things that aren't meaningful at all and are definitely unique, e.g. credit card numbers, car registrations, national insurance numbers etc.

 

[mba_110]

Ok now i have changed the EmpID to Number but ContractID is still text and i have attached strip version for DB.


Have a look its still not working and giving the same error.

 

[Minty]

You have a some major design issues going on here.
Firstly you appear to have three separate tables for you employee details. Your employee personal details contains information that should be in the employee table. And from what I can see the contract details seem to store Employee details again.

But more worrying are the joins you have created in your query;

Why have you joined all these completely unrelated fields to the employee ID? This cannot be correct, and I'm not surprised it doesn't work.

I really think you need to take time to read and learn some Access basic lessons. You don't appear to have grasped some fundamental concepts. http://www.rogersaccesslibrary.com/