Query using two tables

[Melissa]

I am trying to create a query with two tables; from one table I'm using the
fields EmpName, DateAbsentFrom (specifying a certain date range),
DateAbsentTo, TypeOfAbsence, and from the 2nd table, PR_Code which is
basically the EmpID.

The query runs fine without the use of PR_Code from the 2nd table. Without
using that, I get 68 records. When I add in the PR_Code, it gives me 18,020
results, and the PR_Code is not correct, as in it's not actually that persons
PR_Code plus there are many duplicates of that PR_Code, and that Code is in
many people's records.

How do I fix this problem?

 

[Jeff Boyce]

Melissa

I didn't notice in your description which field is 'in common' between the
two tables. If there is none, then you can't "join" your tables, and
putting each table in a query results in "multiplying" the records (called a
Cartesian product, equal to one result row for every combination possible).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Melissa]

I was afraid of that - those two tables don't have a relationship between
them. So that means this isn't possible? Is there a way around it?

 

[Banana]

It's not a requisite that a relationship exist to join a pair of tables.
However, you can't just join on any plain old column. Jet (as well as
any other database engines) need to be told which column from each table
has data in common in order to join the table together. Even if it
happens that the table doesn't share a key (mind, this is exceptional
situation and if this is something you're doing regularly, there is a
much bigger problem!), we still can join on a common column.

For example, suppose we had imported two tables from different source
and need to merge the data & filter out duplicates. There are no key or
relationship, but both has column for people's names. We can then join
using that column containing names so we can process the rows in
whatever fashion we like.

Did that help?

 

[Melissa]

Thank you. Yes it did. There are similar columns....however, I do not care
for how these tables were created. In the first table, the field is "EName,"
and in the second table, there are 2 seperate columns - First Name and Last
Name. Very frustrating. Any ideas? I would appreciate any.

 

[John Spencer]

If you look at a record in the Absence table how can you tell which record(s)
in the PR_Code table it is associated with.

If you can't do this then neither can the computer. If you can do it reliably
from record to record then you should be able to set up a join in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Banana]

In that case you would write the join something like this:

SELECT *
FROM TableA a
INNER JOIN TableB b
ON a.EName = (b.[First Name] & " " & b.[Last Name]);

Be aware of the following:

1) INNER JOIN only returns whatever matches in both tables. If you want
to get all nonmatches from one table, use either LEFT JOIN or RIGHT JOIN
(depending which table you want to show all records). To show only
nonmatchs, 'frustrate' the join by adding this line:

WHERE a.EName IS NULL

(this is assuming you want to select all nonmatchs found in TableB. If
you want to show all nonmatch in TableA instead, substitute the a.Ename
with the appropriate column from TableB.)

2) This is just a process of importing/merging data, right? The reason I
ask is that if this will be the permanent format for the database, there
will be bigger problems unless you take step to sanitize the data into a
final normalized table.

HTH.

 

[Jeff Boyce]

If there's no relationship between the tables, tell us again why you want
records from both?

If you need to see what's in both, one approach might be to build one report
on one table and another report on the other, then embed the second report
in the first as a "sub-report" but without any connection/linking between
them.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Melissa]

I want records from both because the second table is the one with the
EmpID....I know this is a really messed up situation haha but I feel a little
better because I did not create this database. Also, the first table has
"EName" as a field, and the other table has 2 seperate fields (FirstName and
LastName).

Thanks to everyone for their help.