Multiple Tables

[DesignUniv]

Have designed a call tracking database that has a table set-up for each user (same table features in each). Trying to figure out the best way to gather the information from each of those tables at once and be able to report all together, rather than having to go into each separate table, gather the information, then combine all manually. Any help would be greatly appreciated.

 

[pbaldy]

Mistake one was having a separate table for each user, instead of one table with a field for user.

 

[The_Doc_Man]

Mistake two was not searching this forum for articles on "Call Tracking" and "Help Desk"

 

[DesignUniv]

Response

Definitely agree, it was a mistake. The reason this had to be done was that a shared table on our network continued to lock up on the users. Our Access versions and our network was causing some issues, so to alleviate this shared issues, the plan was to separate out the information by user. So far, the process on the front-end has worked great and now issues with locking up, but on the back-end, it has been a challenge.

 

[pbaldy]

It would have been better to solve that problem, rather than create a kludge to get around it, which has had the inevitable result of creating another problem. Your workaround solution is a UNION query that joins the various tables together into one. Personally though, I'd correct the design.

 

[DesignUniv]

Response

So if the design were to be corrected, how would a different field for each user work? Each of the current user tables have the same fields, and the only difference I am using is a default value of the user's name to identify their information.

 

[pbaldy]

Not a different field for each user, one field that contains the user. If the tables all have 5 data fields, your combined table would have those 5 plus an extra one for user. Then if you just want my info, your query is:

SELECT * FROM TableName WHERE UserName = 'Paul'