Linkking all records on both tables

[Rich Mogy]

Can you help me? I have two tables that I would like to join together and
have all records from each table display. Is this possible?

Rich Mogy

 

[Jason Lepack]

As long as you have information that is common to each table then it's
very easy. Just add the two tables into the query editor and join
them on the common field and select all the fields from both tables.

 

[Rich Mogy]

I guess I wasn't clear - -I want all records. I have one table with hours
worked and another with hours collected, but sometimes (I'm cash basis), the
collections are in a year that doesn't match with the worked, and I'm not
pulling all the years.

Year 1 collected -- no match worked
Year 2 collected -- match worked
year 3 collected -- match worked
no match collected -- year 4 worked.

 

[Guest]

Hi Rich,

To add some to Jason's answer, if your definition of "join together" means
displaying records from two tables that have similar fields, then you can use
a union query to accomplish this goal. The data types must be the same, and
you must have the same number of fields, but you can include Null As
FieldName if you need to see a field from one or more tables that is not
present in all tables. For example:

SELECT Customer, City, Region
FROM tblCustomers
UNION <ALL>
SELECT ContactName As Customer, City, Null As Region
FROM tblImportedCustomers
ORDER BY Customer;

The <ALL> keyword is optional (don't include the angle brackets if you use
it). Here is a tutorial that you can download on union queries:

http://home.comcast.net/~tutorme2/samples/unionqueries.zip


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Rich,

If Year 1 collected, Year 2 collected and Year 3 collected (or Year 1
worked, Year 2 worked and Year 3 worked) represent the names of fields in a
table, then you do not have a properly normalized database. If this is the
case, you should spend some time gaining an understanding of database design
and normalization before attempting to build something in Access (or any
RDBMS software for that matter). Here are some links to get you started.
Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of tea or coffee and enjoy reading!

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization"
in the Meeting Downloads page)

<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>

Until you get your DB design corrected (assuming I was right on the
repeating groups of fields), you can likely use a union query to join your
data together.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

I forgot to include a link to other DB design resources. Here it is:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Rich Mogy]

They represent ROWS, not fields. It is possible that in table 1 the row for
year1 doesn't exist, and it is possible that in table 2 the row for year5
doesn't exist, but what I want is a query where to output looks like my
example

TABLE 1 -billed TABLE
2 -- collected

1999 5 hrs
2000 6 hrs
2000 5 hours
2001 7 hrs
2001 6 hours
2002 9 hrs
2002 5 hours
2003 9 hrs
2003 11 hours
2004 1 hrs
2004 9 hours
2005 6 hrs
2005 3 hrs
2006 7 hrs
2006 9 hrs
2007 1 hrs

Other fields are products -- so in 1999 we may have collected on a product
that we stopped selling in 1998 -- in 2007 we have billed but haven't
collected anything yet.

Just understand that it is possible for table 1 to have rows that table 2
doesn't have and table 2 to have rows that table 1 doesn't have, but I want
them all. Would a left join and a right join on the same fields (year)
work?

 

[Guest]

Hi Rich,

Just understand that it is possible for table 1 to have rows that table 2
doesn't have and table 2 to have rows that table 1 doesn't have, but I want
them all. Would a left join and a right join on the same fields (year)
work?

Okay, I think I see what you need. Use the Find Unmatched Query wizard to
create a query that finds all records present in Table1 that are not present
in Table2. Create another (similar) query that finds all records in Table2
that are not present in Table1. You will need to have a suitable key field
that can uniquely identify the records in each table, and a means of joining
the two tables. After you have your two unmatched queries working properly,
copy their SQL statements and use these as the basis for a new union query.
You will union the results of each unmatched query.

Perhaps I can help a bit more if you identify the exact table names, field
names and data types of each field. For example:

tblCustomers
CustomerID Autonumber PK (primary key)
CustFirstName Text
CustLastName Text
etc.

Then give some sample data, but try to prevent the word wrap that was
present in the example that you just posted. It makes it kind of difficult to
see what is going on if the text wraps.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Van T. Dinh]

I think you need what we normally call "Full Outer Join". Unfortunately,
JET doesn't support Full Out Join ...

You need to simulate the Full Outer Join by unioning 2 simple Outer Join
queries like:

========
SELECT T1.BilledYear, T1.BilledAmount, T2.CollectedAmount
FROM Table1 AS T1 LEFT JOIN
Table2 AS T2 ON T1.BilledYear = T2.CollectedYear
UNION
SELECT T2.CollectedYear, T1.BilledAmount, T2.CollectedAmount
FROM Table1 AS T1 RIGHT JOIN
Table2 AS T2 ON T1.BilledYear = T2.CollectedYear
========