Detailed Query based on Multiple tables (1 Viewer)

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
Hello For All
I hope you doing good in this quarantine

Since I'm Home, I'm trying to learn more about Access because I like it and I've got a new idea for an app. So I need your help building it the right way

I'll explain what's the output from this idea to help me get it - I'm working on a sample for this idea, and I'll attach it

-

That's kinda Employees Database
  • tbl_Grades: Grades is Job Positions names, Every grade have classes
  • tbl_Grades_Classes: is subs from every Grade, Every Class have a salary and travel fees,
    • Total Salary Counted as: if Employee nationality is UK then the total salary will be ( Salary + travel fees ), any other nationality Total Salary will be ( Salary ) only.
So I want to generate one Query from all these tables as output to give me these details, I'll type the output as excel as below
  1. emp id :
  2. emp name
  3. emp nationality
  4. Junior Start Date
  5. Junior Grade
  6. Junior Class
  7. Senior Start Date
  8. Senior Grade
  9. Senior Class
  10. Latest Grade ( Based on Latest Start Date )
  11. Latest Class ( Based on Latest Start Date )
  12. Latest Total Salary
  13. Latest 4-year Reviews Grades in the table of reviews ( 2019 - 2018 - 2017 - 2016 ), every employee having it
Capture2.PNG


^ that the output I want , and i need it to be a Query not a report or Form

I've attached the DB Sample if you wanna make the query on it

Thanks and waiting for your feedback.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    81.2 KB · Views: 236
  • Sample-Grades.zip
    154.8 KB · Views: 201

bastanu

AWF VIP
Local time
Yesterday, 19:24
Joined
Apr 13, 2010
Messages
1,195
Please have a look at the attached. The only thing is you need to run the make table query before looking at the qryFinalData in order to refresh the reviews (couldn't get the crosstab to work with the top 4 by group query as its source).

Cheers,
Vlad
 

Attachments

  • Sample-Grades_Vlad.zip
    45.3 KB · Views: 195

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
Hi.
@Vlad,
Thanks for your help

i've already figured it the same way you did
Capture1.PNG


and stuck with reviews too

and I thought there's an easier way instead of doing all these queries
like building expressions on query fields ( dlookup , switch , iif ), i tried it but i couldnt make it work with dlookup criteria " ' & this quotes making me mad
IS that even possible, if i created a new qry from the employee table, then added a dlookup in a query empty field to get Grade from grades table when max(startdate) and employee ID in grades table = employee id in the opened query !?

Again, thanks alot for your help (y)(y)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 03:24
Joined
Jul 9, 2003
Messages
14,642
I cannot see a way to create a single query. however I think it would be possible to add the information into a table and then you could run a query against that table.

The salary information will need to be calculated and stored. I would suggest you add an extra field into the employee table to store the salary.

If that can be done, then the following method should work.

Create a recordset loop to loop through the records in the employee table and extract the name, salary etc.

We will call this recordset loop the outer loop.

Inside this outer loop you will need two more recordset loops, one to extract the grade information and the other to extract the review information.

Pass this information into variables and then in the final stage of the outer loop, insert the information into a temporary table.

Now you can run a single query against your temporary table to provide the information for the spreadsheet.

See Loops on Nifty Access for more info:-

 

zeroaccess

Active member
Local time
Yesterday, 21:24
Joined
Jan 30, 2020
Messages
671
and I thought there's an easier way instead of doing all these queries
like building expressions on query fields ( dlookup , switch , iif ), i tried it but i couldnt make it work with dlookup criteria " ' & this quotes making me mad
IS that even possible, if i created a new qry from the employee table, then added a dlookup in a query empty field to get Grade from grades table when max(startdate) and employee ID in grades table = employee id in the opened query !?
Don't use DLookup in queries.
 

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
I cannot see a way to create a single query. however I think it would be possible to add the information into a table and then you could run a query against that table.

The salary information will need to be calculated and stored. I would suggest you add an extra field into the employee table to store the salary.

If that can be done, then the following method should work.

Create a recordset loop to loop through the records in the employee table and extract the name, salary etc.

We will call this recordset loop the outer loop.

Inside this outer loop you will need two more recordset loops, one to extract the grade information and the other to extract the review information.

Pass this information into variables and then in the final stage of the outer loop, insert the information into a temporary table.

Now you can run a single query against your temporary table to provide the information for the spreadsheet.

See Loops on Nifty Access for more info:-


Thanks for your reply
but I can't store salary information in employee table ..
 

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
Don't use DLookup in queries.
Thanks for your reply but please tell me why ?
because i was gonna create a new post now to how to use dlookup to get data in query fields !because it will fix some issues with me
 

bastanu

AWF VIP
Local time
Yesterday, 19:24
Joined
Apr 13, 2010
Messages
1,195
I think the biggest issue with your final query is the last 4 reviews, if some employees did not yet have a review for certain years you will need to add some extra columns to accommodate their reviews.

Cheers,
Vlad
 

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
I think the biggest issue with your final query is the last 4 reviews, if some employees did not yet have a review for certain years you will need to add some extra columns to accommodate their reviews.

Cheers,
Vlad
true ,
that's why i wanna use dlookup to get it in the same employee query

so i made a form to write the Promotion year like : 2021 then 4 more unbound txtboxs to get the preview 4 year : 2020-2019-2018-2017

Capture.PNG


Capture2.PNG

then i created a a report and used the employee table then in report i used this dloockups

SQL:
2020 =DLookUp("DegreeNumbers";"tbl_Employee_Reviews";"[eYear]=" & [Forms]![Form1]![Year1] & " And [employee_id]=" & [Employee_ID])

and so on for 3 years , and label for yeah is txtbox getting values from form year1,2,3,4

and every thing is good in repott
Capture3.PNG


then i was thinking of how to use the same dlookups from the report to query of employee !!?? i belive i need to change some quotes and ""''
and i hate this dlookups criteria quotes :mad:🙈:(

i found someone asking a similar question about using dlookups in query
https://answers.microsoft.com/en-us...s/35d62715-1cca-48f3-986e-a3e2d7127404?auth=1

and still i can't make it work with multi-criteria
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 03:24
Joined
Jul 9, 2003
Messages
14,642
Thanks for your reply
but I can't store salary information in employee table ..

Then please explain how to calculate the salary from the information in the table:- Emp_Grades ..
 

alvingenius

IT Specialist
Local time
Today, 04:24
Joined
Jul 10, 2016
Messages
168
Ah! --- I can see why, you have two salaries to extract, so you couldn't just add one salary in the main table, because there are two, Junior & Senior

hi
Salary is based on nationality Not
Junior and senior

junior and senior is just a promotion and nor relates to salary.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 28, 2001
Messages
23,129
When you use a DLookup, you are creating a query dynamically and then creating a recordset to retrieve the result.

I am not going to say that this IS what DLookup does, but if I had to program it in VBA, this is more or less what I would do

Code:
Public Function DLookup( FieldName As String, DomainName As String, Criteria As String ) As Variant
Dim DLRS as DAO.Recordset
Dim DynSQL as String
    DynSQL = "SELECT " & FieldName & " FROM " & DomainName & " WHERE " & Criteria & " ;"
    CurrentDB .CreateQueryDef( "TEMP", DynSQL )
    Set DLRS = CurrentDB.OpenRecordset( "TEMP", dbOpenDynaset )
    DLSR.MoveFirst
    DLookup = DLRS!Fields(FieldName)
    DLSR.Close
    Set DLSR = Nothing
    CurrentDB.QueryDefs.Delete "TEMP"
End Function

From this you can see that you are churning the database considerably, including that you are creating and deleting a query within each step of an outer query. The implied overhead of this way of doing things is horrendous.
 

Users who are viewing this thread

Top Bottom