 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / February 2007Linkking all records on both tables
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 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. > 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 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. > 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 [quoted text clipped - 5 lines] >> >> Rich Mogy 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 __________________________________________ > 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 [quoted text clipped - 5 lines] > year 3 collected -- match worked > no match collected -- year 4 worked. I forgot to include a link to other DB design resources. Here it is: http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Database Design101 Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/expert_contributors.html http://www.access.qbuilt.com/html/search.html 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? > Hi Rich, > [quoted text clipped - 47 lines] >> year 3 collected -- match worked >> no match collected -- year 4 worked. 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 __________________________________________ > 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 [quoted text clipped - 29 lines] > them all. Would a left join and a right join on the same fields (year) > work? 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 ========  SignatureHTH Van T. Dinh MVP (Access) > 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 [quoted text clipped - 78 lines] >>> year 3 collected -- match worked >>> no match collected -- year 4 worked. 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 __________________________________________ > 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. __________________________________________ > > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.