Make multiple tables with one query and then mail them

[ricogrande]

I want to create many smaller tables from one large table keeping the large
one intact with one query if possible. I want a new table for every change in
Manager ID, without knowing how many there are. There will be many rows for
each Manager Id as each manager has many employees. I want a list of
employees by manager in each table.
Then I would like to email each table to each manager. The manager id is
recognized by our exchange servers as an alternate key for email address or
if that does not work I could just make that field be the explicit email
address. I know this is 2 different concepts and help on either one will be
helpful.

 

[Lord Kelvan]

ok point one you cannot do it in one query even if you want to. What
is the change you are trying to capture because there is most likly a
better way than creating more tables.

point two DO NOT GIVE USERS DIRECT ACCESS TO YOUR DATA create a report
and email the report.
are you expecting access to automatically email this information out.
if so that will require a nice intence bit of code (not that i know
how to do that).

also on another note access cannot do it automatically because there
are no triggers you have to do it via a form where a user modifies the
data. if your users are not using forms then bad on you.

Regards
Kelvan

 

[ricogrande]

Thanks for responding.
Odd how a query can take data from many tables and combine them into one
table but not the reverse. I am doing some research to replace an Excel
spreadsheet. We currently use Excel and parse, email, and append the results
that get emailed back manually. I was trying to find an automated non-web way
to do this. A manager can only see their direct reports because there is
sensitive data involved so that is why I was wanting to use email. Otherwise
I need some advanced security measure I don't even know exists. Sure they
could use forms and host it on the web but they do not even have a web server
and if they do not have a way to secure the view for each manager.

 

[Lord Kelvan]

no i mean access forms and then putting it on the network

a query can split into mutiple tables ACCESS cannot because access
annot handle query that do mutiple tasks.

ie

select * from table1;
select * from table2;

i am not saying vba cannot email out i just dont know how it would
work vba can connect into outlook or groupwise but i woulodnt for the
life of me knwo the code to do it.

but you shouldnt take the data out into mutiple tables you can use
queries to simulate it

select * from thetable where managerid = x

and x is the managers id

this query will give you theos results and will automatically update
whenever a managersid updates

ie

managersid employeeid
1 1
1 2
1 3
2 4
2 5
2 6
3 7
3 8

the query

select * from thetable where managerid = 1

will give you

managersid employeeid
1 1
1 2
1 3

if employee 2 changes manager to manager 2

the table will look like

managersid employeeid
1 1
2 2
1 3
2 4
2 5
2 6
3 7
3 8

and the query will return

managersid employeeid
1 1
1 3

is this what you are looking for

Reagrds
Kelvan