Avatar of an_and
an_andFlag for India asked on

Select Query Needed

Hi there,

i have 3 tables in my database (MS Access) namly  i)EmpReg ii) EmpDate iii)cmpDetails
the tables has the following columns.

emp_code (int)
emp_Fname (varchar)
emp_Lname (varchar)
cmp_code  (int)    

ii) EmpDate
emp_code (int)
emp_date (datetime)
emp_status (double)      --> only  0 [OR]  0.5  [OR] 1 will be stored in this column

cmp_code (int)          
cmp_name (varchar)

so i need a query to  select sum of emp_status   i.e. sum(emp_status)   from  EmpDate  on emp_date  date range given and along with emp_code, emp_Fname, emp_Lname from EmpReg   and cmp_name  from cmp_name

Best regards,


Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Wills

seems to be a reasonable query in SQL syntax, open a new query window in design mode, right click the header, put into SQL view and paste:

Select R.emp_code, R.emp_Fname, R.emp_Lname ,C.cmp_name , sum(D.emp_status)
from EmpReg R ,EmpDate D ,cmpDetails C
where R.cmp_code = C.cmp_code
and R.emp_code = D.emp_code
Group by R.emp_code, R.emp_Fname, R.emp_Lname ,C.cmp_name

Then run the query. Sometimes, when you save, it tries to resort to access, so might need to open again, tidy (all the brackets) and save again...

Mark Wills

@pratima_mcs: sorry didn't see your posting - had zero replies when I started to type - absolutely perfect code though :)
Mark Wills

Oh, and before the group by put in your "where" clause for date range...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Mark Wills

OK, just to be different, lets give pratima_mcs the credit for the equi join, and I will use traditional join syntax:

Select R.emp_code, R.emp_Fname, R.emp_Lname ,C.cmp_name , sum(D.emp_status)
from EmpReg R
inner join EmpDate D on R.emp_code = D.emp_code
inner join cmpDetails C on R.cmp_code = C.cmp_code
where D.emp_date between "thisdate" and "thatdate"
Group by R.emp_code, R.emp_Fname, R.emp_Lname ,C.cmp_name


both pratima_mcs and mark wills give the same solution i give point to pratima_mcs  for her first and fast reply

Finally after modifying the query  here's the query

SELECT R.emp_code, R.emp_Fname, R.emp_Lname, C.cmp_name, sum(D.emp_status)
FROM EmpReg AS R, EmpDate AS D, cmpDetails AS C
WHERE R.cmp_code=C.cmp_code And R.emp_code=D.emp_code And D.emp_Date Between #11/8/2006# And #11/17/2006#
GROUP BY R.emp_code, R.emp_Fname, R.emp_Lname, C.cmp_name;