Query returns incomplete results

[jiblankman]

I am trying to write a program in Access 2003 VBA that can import tables from three different databases, combine the tables and perform a query against the combined tables.

There are two tables: a client list and an employee list. The client list has a single entry for each clientid but the employee list can have multiple employees for each clientid.

I can import and combine the tables using VBA without much difficulty. However, when I run a query I only get a single employee for each clientid. I know this is incorrect. When I isolate the query to a single clientid that has 65 employees, I still get just 1 from the query.

I am reasonably new to Access VBA programming. Any help is greatly appreacted.

Here is the code that I am using. I included the lines where the tables are imported and combined just to make sure that there isn't anything I am missing.

'import Client Info tables from each database
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB1.mdb", acTable, "client info", "DB1ClientInfo", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB2.mdb", acTable, "client info", "DB2ClientInfo", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB3.mdb", acTable, "client info", "DB3ClientInfo", False

'import Employee tables from each database
'I did not name the tables!!!
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB1.mdb", acTable, "Main", "DB1Employees", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB2.mdb", acTable, "Main", "DB2Employees", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "s:\database\DB3.mdb", acTable, "Main", "DB3Employees", False

'convert clientid to text in the DB1 and DB2 tables
'DB3 is already text
CurrentDb.Execute "ALTER TABLE DB1ClientInfo ALTER COLUMN clientid TEXT"
CurrentDb.Execute "ALTER TABLE DB2ClientInfo ALTER COLUMN clientid TEXT"
CurrentDb.Execute "ALTER TABLE DB1Employees ALTER COLUMN clientid TEXT"
CurrentDb.Execute "ALTER TABLE DB2Employees ALTER COLUMN clientid TEXT"
   
'combine Client Info tables into a single table called "Random Clients"
DoCmd.RunSQL "CREATE TABLE [Random Clients] ([clientid] TEXT PRIMARY KEY, [clientname] TEXT, [mailaddress] TEXT, [mailcity] TEXT, [mailstate] TEXT, [mailzip] TEXT, [primarycontact] TEXT, [primarytitle] TEXT, [primarysalutation] TEXT, [randompool] TEXT);"
CurrentDb.Execute "INSERT INTO [Random Clients] SELECT [DB1ClientInfo].clientid, [DB1ClientInfo].clientname, [DB1ClientInfo].mailaddress, [DB1ClientInfo].mailcity, [DB1ClientInfo].mailstate, [DB1ClientInfo].mailzip, [DB1ClientInfo].primarycontact, [DB1ClientInfo].primarytitle, [DB1ClientInfo].primarysalutation, [DB1ClientInfo].randompool FROM DB1ClientInfo WHERE [DB1ClientInfo].randompool = '6'"
CurrentDb.Execute "INSERT INTO [Random Clients] SELECT [DB2ClientInfo].clientid, [DB2ClientInfo].clientname, [DB2ClientInfo].mailaddress, [DB2ClientInfo].mailcity, [DB2ClientInfo].mailstate, [DB2ClientInfo].mailzip, [DB2ClientInfo].primarycontact, [DB2ClientInfo].primarytitle, [DB2ClientInfo].primarysalutation, [DB2ClientInfo].randompool FROM DB2ClientInfo WHERE [DB2ClientInfo].randompool = '6'"
CurrentDb.Execute "INSERT INTO [Random Clients] SELECT [DB3ClientInfo].clientid, [DB3ClientInfo].clientname, [DB3ClientInfo].mailaddress, [DB3ClientInfo].mailcity, [DB3ClientInfo].mailstate, [DB3ClientInfo].mailzip, [DB3ClientInfo].primarycontact, [DB3ClientInfo].primarytitle, [DB3ClientInfo].primarysalutation, [DB3ClientInfo].randompool FROM DB3ClientInfo WHERE [DB3ClientInfo].randompool = '6'"

'combine Emplouyee tables into a single table called "Random Employees"
CurrentDb.Execute "CREATE TABLE [Random Employees] ([clientid] TEXT PRIMARY KEY, [ename] TEXT, [employeeid] TEXT);"
CurrentDb.Execute "INSERT INTO [Random Employees] SELECT DB1Employees.clientid, DB1Employees.ename, DB1Employees.employeeid FROM DB1Employees"
CurrentDb.Execute "INSERT INTO [Random Employees] SELECT DB2Employees.clientid, DB2Employees.ename, DB2Employees.employeeid FROM DB2Employees"
CurrentDb.Execute "INSERT INTO [Random Employees] SELECT DB3Employees.clientid, DB3Employees.ename, DB3Employees.employeeid FROM DB3Employees"
    
'create the Pool
[COLOR=Red]'THIS IS WHERE THINGS STOP WORKING
'WHEN I MANUALLY COUNT THE NUMBER OF EMPLOYEES THAT SHOULD
'BE ADDED TO THE TABLE THERE SHOULD BE 65, ONLY 1 APPEARS[/COLOR]
DoCmd.RunSQL "CREATE TABLE [Random Pool] ([clientid] TEXT PRIMARY KEY, [employeeid] TEXT, [ename] TEXT);"
CurrentDb.Execute "INSERT INTO [Random Pool] SELECT [Random Employees].clientid, [Random Employees].employeeid, [Random Employees].ename FROM [Random Employees] INNER JOIN [Random Clients] ON [Random Employees].clientid = [Random Clients].clientid WHERE [Random Clients].randompool = '6';"