Need help querying from multiple tables

[BJS]

Hello Access Friends,

I am having trouble writing a query. Here is what I am trying to accomplish:

I have 3 tables: 1981-1990, 1991-2000, and 2001-2003

Each of these tables contains employee records with totals in each year.
I am trying to write a single query that will pull the record for any employee when prompted for the emp # and the data for that employee should appear from years 1981 through to 2003.

My problem is that sometimes the employee will only exist in the 1981-1990 table, sometimes only in the 1991-2000 table, sometimes only in the 2001-2003 table, sometimes in only 2 of the three tables or sometimes in all of the tables.

I have tried various joins and basing one query on another, but I can't get it going?

I have attached a zip file. I'm hoping someone can look at my queries and help me with this.

THANKS VERY MUCH IN ADVANCE!

BJS

 

[Liv Manto]

DO a Union SQL.

 

[BJS]

Hi Liv Manto,

Thank you for your response.

I tried doing a union SQL, but wasn't successful. I am not good at writing the SQL statements...better at using the design grid. Will a union query work if only the Emp # is the same, but all the other fields are different in the tables? I need to retrieve the Emp # and the value from each year in each table.

I am able to do the union query just to get all the Emp #'s from each table. My problem is that I also need the other fields from the other tables, which are all different.

BJS

 

[BJS]

Hi All,

I've tried using a union query, but it doesn't work, since the only common field among the three tables is the emp # field. The rest of the fields in the tables are different, but I need to display them all.

The fields for the tables are as follows:

Table 1: Emp #, 1981, 1982, 1983, 1984, 1985 .....1990
Table 2: Emp #, 1991, 1992, 1993, 1994, 1995 .....2000
Table 3: Emp #, 2001, 2002, 2003

My query should prompt the user for the emp # and the result of the query should display the emp# and all the fields from each of the tables (1981 trhough 2003 for that employee.

Any ideas. Please help! and Thanks!!

 

[ByteMyzer]

Why not simply create a single table (Table1, for example) with the following fields:
NAME (text field)
EmpNumber (number field)
Year (number field)
Total (number field)

And have entries like:

NAME        | EmpNumber | Year | Total
--------------------------------------
John Smith  | 1         | 2002 | 1234
John Smith  | 1         | 2003 | 237
Richard Roe | 2         | 2003 | 1475

Then use a crosstab query like:

TRANSFORM CDbl(Nz(Sum(Table1.Total),0)) AS Total
SELECT Table1.NAME, Table1.EmpNumber
FROM Table1
GROUP BY Table1.NAME, Table1.EmpNumber
PIVOT Table1.Year;

which, with the data from the above example, would return something like:

NAME        | EmpNumber | 2002 | 2003
--------------------------------------
John Smith  | 1         | 1234 |  237
Richard Roe | 2         |    0 | 1475

See if this method works for you.

 

[BJS]

Hi ByteMyzer,

Thank you for your suggestion. Unfortunately that will not be possible for me to do. This is a database that has been in use for many years, and thus has thousands of records in it. The boss does not want the tables changed. He just wants me to create a query that will get this information from all 3 tables (by the way, one of the tables in the real database is actually linked to DB2.) and have the information displayed as I described.

Any other suggestions that would work with keeping the tables as they are?
My boss needs this data displayed in one row, so that he can cut and paste in all at once into an excel spreadsheet instead of having to cut and paste it from three different tables to get his results.

Thanks again for the help and any further suggestions.

BJS

 

[ByteMyzer]

What your boss wants is not possible with a simple, single query. Moreover, this is not a recommended way to store data in tables.

If you want to be able to retrieve the data as you have described, either be prepared to write an extremely involved Union-Select query, or prepare to redesign your whole table structure as I have described.

 

[BJS]

ByteMyzer,

Restructuring the table is not something my boss wants me to do.
I am stuck with writing this complex query. I've been working on it for a couple of days.

If anyone knows how to write this query, I'd certainly appreciate some help.

Thanks!

 

[BJS]

I think I have come up with a workaround solution which does not require the creation of a very complex union query. Here is what I have done, and what seems to do the trick:

1. I created a table that contains all the fields required for the final result (Emp #, and years 1981 - 2003). I created a single record for this table and all the field values are set to 0.

2. I created a form with a field used to enter the desired Emp# and a command button that will run an update query and display the results in the table I created in Step 1.

3. I created an update query, which uses the DLookup function to get the data for each of the years in each of the 3 tables. This query uses the Emp# entered on the form as the criteria for the employee to look up.

4. The button on the form, runs update query and then opens the table and displays the information for the emp # entered on the form and shows all data for years 1981 - 2003.

For those of you reading this, what do you think of this approach? I found it quicker to do rather than trying to create that complex union query, which I have had no success with.

I look forward to your comments.

BJS

 

[Rich]

Perhaps you should try pointing out to your boss that by following ByteMyzer's advice you would save yourself a lot of time and trouble in the long run.
You might also point out to him/her that Access is a relational database and not a spreadsheet.
Pointing out some of the articles on normalisation/normalization might help your case.
If your db was properly constructed you would only need a Totals query and would only have to create the query ONCE.

 

[Liv Manto]

Embed your union query (here called a) in another query ; A left joining the three tablles.

Here, cut and paste an SQL editor. I used Access 2000, so tell me if it doesnt work, i'll redo in it in a 97.





SELECT a.NAME, a.EmpNumber, b.[1981], b.[1982], b.[1983], b.[1984], b.[1985], b.[1986], b.[1987], b.[1988], b.[1989], b.[1990], d.[1991], d.[1992], d.[1993], d.[1994], d.[1995], d.[1996], d.[1997], d.[1998], d.[1999], d.[2000], c.[2001], c.[2002], c.[2003]
FROM (((SELECT [1981-1990].NAME as name, [1981-1990].EmpNumber as empnumber
FROM [1981-1990] union
SELECT [1991-2000].NAME as name, [1991-2000].EmpNumber as empnumber
FROM [1991-2000] UNION SELECT [2001-2003].NAME as name, [2001-2003].EmpNumber as empnumber
FROM [2001-2003] ) AS a LEFT JOIN [1991-2000] AS d ON (a.EmpNumber = d.EmpNumber) AND (a.NAME = d.NAME)) LEFT JOIN [2001-2003] AS c ON (a.EmpNumber = c.EmpNumber) AND (a.NAME = c.NAME)) LEFT JOIN [1981-1990] AS b ON (a.EmpNumber = b.EmpNumber) AND (a.NAME = b.NAME);

 

[BJS]

Hi Liv Manto,

Thanks so much for writing this sql for me. I copied and pasted it into SQL Editor in Access 97 and when I switch to datasheet view, I get the error "Syntax Error in From Clause".

When I click ok on that error message, the "SELECT" keyword in RED below is highlighted. Any ideas?

SELECT a.NAME, a.EmpNumber, b.[1981], b.[1982], b.[1983], b.[1984], b.[1985], b.[1986], b.[1987], b.[1988], b.[1989], b.[1990], d.[1991], d.[1992], d.[1993], d.[1994], d.[1995], d.[1996], d.[1997], d.[1998], d.[1999], d.[2000], c.[2001], c.[2002], c.[2003]
FROM (((SELECT [1981-1990].NAME as name, [1981-1990].EmpNumber as empnumber
FROM [1981-1990] union
SELECT [1991-2000].NAME as name, [1991-2000].EmpNumber as empnumber
FROM [1991-2000] UNION SELECT [2001-2003].NAME as name, [2001-2003].EmpNumber as empnumber
FROM [2001-2003] ) AS a LEFT JOIN [1991-2000] AS d ON (a.EmpNumber = d.EmpNumber) AND (a.NAME = d.NAME)) LEFT JOIN [2001-2003] AS c ON (a.EmpNumber = c.EmpNumber) AND (a.NAME = c.NAME)) LEFT JOIN [1981-1990] AS b ON (a.EmpNumber = b.EmpNumber) AND (a.NAME = b.NAME);

 

[Liv Manto]

Sorry, i think the extra open parenthesis is doing that.

SELECT a.name, a.empnumber, b.[1981], b.[1982], b.[1983], b.[1984], b.[1985], b.[1986], b.[1987], b.[1988], b.[1989], b.[1990], d.[1991], d.[1992], d.[1993], d.[1994], d.[1995], d.[1996], d.[1997], d.[1998], d.[1999], d.[2000], c.[2001], c.[2002], c.[2003]
FROM (([SELECT [1981-1990].NAME as name, [1981-1990].EmpNumber as empnumber
FROM [1981-1990] union
SELECT [1991-2000].NAME as name, [1991-2000].EmpNumber as empnumber
FROM [1991-2000] UNION SELECT [2001-2003].NAME as name, [2001-2003].EmpNumber as empnumber
FROM [2001-2003] ]. AS a LEFT JOIN [1991-2000] AS d ON (a.name = d.NAME) AND (a.empnumber = d.EmpNumber)) LEFT JOIN [2001-2003] AS c ON (a.name = c.NAME) AND (a.empnumber = c.EmpNumber)) LEFT JOIN [1981-1990] AS b ON (a.name = b.NAME) AND (a.empnumber = b.EmpNumber);

 

[BJS]

Hello Liv Manto and Pat Hartman,

I am trying both of your methods:

Liv: I tried your revised SQL and now I get the error: "Syntax Error in Join Operation" ????????

Pat: I created the union SQL as per your sample and then created the cross tab query based on the union query. This works great BUT:

1. I need the final result to display all years (1981-2003) in the crosstab query. Currently if the employee is not in one of the tables (e.g. not in the table 1981-1990) then the crosstab query will only display data for the years 1991-2003 for that employee. I need the years 1981-1990 to display, but of course without any data. Any ideas how I can accomplish this?


2. I can't seem to put a prompt for the employee number in the criteria of the crosstab query. I can set the empNumber (e.g. "3") as the criteria and only that employees record will show, but I need to set the criteria as [Enter Emp #]. When running the query, I get the error: "The Microsoft Jet Engine does not recognize [Enter Emp #] as a valid field name or expression".

Being prompted for the Emp # via the query is mandatory for the user.

Thanks so much for the help....I'm starting to make good progress here.