Ms Access Client Database Question

[prinsr]

Hi



I must create an access database that auto generates a client number(which is used as my primary key) for each company. This part i got covered and it works well. the problem is that i must also add employees from the companies under their respective companies. Is there anyway of doing this?

[nick404]

So you want to have an additional field "Companies" in the same table as your employees and put the company name next to each employee.

[prinsr]

@nick404 And use that as the FK? How will i then run a query to list all the employees of that company?

[nick404]

You could have the company be a foreign key yes, and then have another table with the companies as the primary key (and could just use that to keep company addresses if you need or products you buy from them, w/e).

The query will something like:

SELECT tblEmployees.Employee, tblEmployees.Company_FK
FROM tblEmployees INNER JOIN tblCompanies ON tblEmployees.Company_FK = tblCompanies.Company_PK
WHERE tblEmployees.Company_FK = [Enter company name to search];


This will prompt you to "enter company name to search" when you run the query and then whatever company name you type in it should return all the employee of that company. The table names and field names are hypothetical so tailor it to what you actually have. The foreign key and primary key I name as _FK and _PK so that I can keep track of what is all related easier.

[prinsr]

Awesome stuff, I didn't think of that. Thanks for the SQL code tho, my SQL coding is near to non-existent.

[nick404]

Yep no problem! If something doesn't work or have further questions let us know