how do you create a subquery in access?
what would you do/type?
select
from employees;
the easy way to get into that is to build 2 queries and use the query designer instead of trying to write sql code first.
so, let’s say you have an ‘employees’ table and a table that tracks absences and they’re keyed on an employee id field
in you primary query from the employee table, you want:
emplid empl name and then you want the 3rd data item to be their number of days absent
create a separate totals query that counts the number of days absent, grouped by Emp id. they view that query in sql mode and copy/paste it in your main query in the 2rd column (some editing is required to remove the ‘;’ at the end and other changes depending on your tables)
so now, for every record in your main query, it will run the sub query and display the empid, name, and # days absent.
NOTE: before everyone jumps on it there are much easier way to accomplish this result but it’s a good basic subquery example.
the easy way to get into that is to build 2 queries and use the query designer instead of trying to write sql code first.
so, let’s say you have an ‘employees’ table and a table that tracks absences and they’re keyed on an employee id field
in you primary query from the employee table, you want:
emplid empl name and then you want the 3rd data item to be their number of days absent
create a separate totals query that counts the number of days absent, grouped by Emp id. they view that query in sql mode and copy/paste it in your main query in the 2rd column (some editing is required to remove the ‘;’ at the end and other changes depending on your tables)
so now, for every record in your main query, it will run the sub query and display the empid, name, and # days absent.
NOTE: before everyone jumps on it there are much easier way to accomplish this result but it’s a good basic subquery example.
References :