counts of occurrences of employee ids in foreign tables

[pumelayurika]

SampleDB.zip

Hi guys,
I'm currently trying to do innerjoins of all tables associated with employee table via employee id,
and then make a count of each foreign key of each of the tables associated with the employee table.
There are some tables that had no employee id inserted in it so it sohould return 0 counts


and for those who does have an occurence of employee id should have a count.


However, It returned all zeroes for all the id's the fact that some tables were populated with the
specific employee id in the query i created ("Query 1")in the accdb file.


Kindly advice


Thanks and Regards!
Pum -

[NTC]

you have employee table with employee id

you have 'other' table with employee id field (foreign key)

the other table may or may not have records matching employee table ids

in query design view make the join line between the tables on the employee ID fields; right click on the line and select the option so you have an arrow that is pointing toward the other table

drag both ID fields into the query

in the ribbon change the query to be an aggregate query (big E looking sigma symbol)

it will default both field to be 'group by' - change the other table's ID field to be 'count'

this should get you on the way to what you need. you may need to vary this idea with an Is Null criteria in the other table field - depending on exactly what type record results you seek

[pumelayurika]

you have employee table with employee id

you have 'other' table with employee id field (foreign key)

the other table may or may not have records matching employee table ids

in query design view make the join line between the tables on the employee ID fields; right click on the line and select the option so you have an arrow that is pointing toward the other table

drag both ID fields into the query

in the ribbon change the query to be an aggregate query (big E looking sigma symbol)

it will default both field to be 'group by' - change the other table's ID field to be 'count'

this should get you on the way to what you need. you may need to vary this idea with an Is Null criteria in the other table field - depending on exactly what type record results you seek

I done it but whenever I go in datasheet view,it returns 244 on all counted fields (for other tables) but those who had no occurrence of employee id has returned 0 as expected. here is the sql code:

SELECT Count(AI_History.AI_ID) AS CountOfAI_ID, Count(PB_History.PB_ID) AS CountOfPB_ID, Count(NDHP_History.NDHP_ID) AS CountOfNDHP_ID, Count(Position_History.PH_ID) AS CountOfPH_ID, Count(Rate_History.RH_ID) AS CountOfRH_ID, Count(TP_History.TP_ID) AS CountOfTP_ID, Count(Taxi_History.TH_ID) AS CountOfTH_ID, Employee_Table.Employee_IDFROM (((((((Employee_Table LEFT JOIN AI_History ON Employee_Table.Employee_ID = AI_History.Employee_ID) LEFT JOIN Holiday_History ON Employee_Table.Employee_ID = Holiday_History.Employee_ID) LEFT JOIN PB_History ON Employee_Table.Employee_ID = PB_History.Employee_ID) LEFT JOIN NDHP_History ON Employee_Table.Employee_ID = NDHP_History.Employee_ID) LEFT JOIN Position_History ON Employee_Table.Employee_ID = Position_History.Employee_ID) LEFT JOIN Rate_History ON Employee_Table.Employee_ID = Rate_History.Employee_ID) LEFT JOIN TP_History ON Employee_Table.Employee_ID = TP_History.Employee_ID) LEFT JOIN Taxi_History ON Employee_Table.Employee_ID = Taxi_History.Employee_ID
GROUP BY Employee_Table.Employee_ID
HAVING (((Employee_Table.Employee_ID)=44));

[NTC]

well you have multiple joins; queries are never wrong. they may not be resulting in the records you expect but they are never wrong... you should do 1 join at a time and inspect the results... and develop an understanding of how each new join varies the logic of which records qualify....

queries can be made based on other queries - not just tables - and so probably to get the result that you seek it cannot be done in a single query. You must instead set up a series of queries to establish the logical result you seek.