Append and Delete queries

[toddbingham]

I am using Access 97, I have several link Databases to Live data. In turn, I have these dump to databases using append queries so that they are easily worked with. These databases represent several different companies (i.e., 1a, 1b, 2a, 2b) each being a seperate company. I would like to be able to search between all of the employees within a single search form. In other words, I would like to append Comapny Name and Employee name from each database into one database with 2 fields (company name and employee name). The only true way I have to search for an employee that is currently employed is to search using their check history database for that company. How would I manipulate a query to allow me to append all of these databases to one database fro searching purposes. Each company database is uniform with the others (i.e., all fields are named the same: company_name, employee_name).

And then, if this is possible, it will bring over more than 1 value for each person because I will be using the check history database for that company and if a person has been paid more than once it will return all of those values, correct? But, all I need is 1 value for each person so that we search through several thousand employees to find the employee and what company they work for, easlily (no other information would be required, such as location, address, etc.).

 

[MattS]

Assuming that whichever database you want to run this in has links to the check_history tables in each companies database, you could run a union query. i.e.

SELECT DISTINCT company_name, employee_name FROM check_history_1a

UNION SELECT DISTINCT company_name, employee_name FROM check_history_1b

UNION SELECT DISTINCT company_name, employee_name FROM check_history_1c;

 

[toddbingham]

Thank You Very Much, I was able to make it work.
BTW, where did you learn to write some of this BA? I would like to learn a little more.

Todd

 

[MattS]

Everything I've learnt has come from a combination of three things really:

1) Attending courses
2) Books - (like "Access 97 - Power Programming" - fantastic for VBA)
3) A lot of Trial and Error!!

The best way I found to get to grips with SQL, is to create a query in the normal fashion, and then view the SQL behind it.

HTH,

Matt.