Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
natg504
Starting Member
14 Posts |
 Posted - 2005-05-18 : 14:18:53
|
| I'm trying to join two existing tables (I can't add fields at this point) in a query to report on the data. I'm not sure if this is possible to do in one query though. I'm using MS Access 2003. An example of my tables are below.tblSurvey1-UserID-CollectionDate-field3...tblSurvey2-UserID-DateRecorded-field3...The way the data is gathered is that a person collects data on one day for a userID and enters it into tblSurvey1. On the next day, that user is supposed to come back and fill out a survey entering data into tblSurvey2. So, the tables can be joined by UserID since each user has a unique ID. CollectionDate would be one day before DateRecorded. That same user can also come back the next day and fill out a new entry in tblSurvey1, and possibly tblSurvey2 the next day too.What i want to display is everything in the tblSurvey1 and tblSurvey2 in one row. If the user did come back the next day and fill out tblSurvey2, their responses should be displayed next to the tblSurvey1 results from the day before. If they didn't fill out tblSurvey2, the survey1 results should still be there, but be followed by blank survey2 results.Is this all possible in one query???Thanks! |
|
|
jhermiz
3564 Posts |
Posted - 2005-05-18 : 18:57:21
|
quote:
Originally posted by natg504
I'm trying to join two existing tables (I can't add fields at this point) in a query to report on the data. I'm not sure if this is possible to do in one query though. I'm using MS Access 2003. An example of my tables are below.tblSurvey1-UserID-CollectionDate-field3...tblSurvey2-UserID-DateRecorded-field3...The way the data is gathered is that a person collects data on one day for a userID and enters it into tblSurvey1. On the next day, that user is supposed to come back and fill out a survey entering data into tblSurvey2. So, the tables can be joined by UserID since each user has a unique ID. CollectionDate would be one day before DateRecorded. That same user can also come back the next day and fill out a new entry in tblSurvey1, and possibly tblSurvey2 the next day too.What i want to display is everything in the tblSurvey1 and tblSurvey2 in one row. If the user did come back the next day and fill out tblSurvey2, their responses should be displayed next to the tblSurvey1 results from the day before. If they didn't fill out tblSurvey2, the survey1 results should still be there, but be followed by blank survey2 results.Is this all possible in one query???Thanks!
Yes but I really really dont think you need two tables, you only need one table and proper normalization.However,SELECT t1.YourField, t2.YourField FROM tblOne t1 INNER JOIN tblTwo t2 ON t2.YourID = t1.YourID
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-18 : 19:21:33
|
| I think you also want to do a join on the Date values so as to align multiple visits with their corresponding setup. Assuming that you actually enforce that the tblSurvey2.DateRecorded IS ALWAYS AND FOREVERMORE one day later than tblSurvey1.CollectionDate, then your statement would look like:SELECT ... FROM tblSurvey1INNER JOIN tblSurvey2 on tblSurvey1.UserID = tblSurvey2.UserID AND tblSurvey1.CollectionDate = (tblSurvey2.DateRecorded - 1)---------------------------EmeraldCityDomains.com |
|
|
|
natg504
Starting Member
14 Posts |
Posted - 2005-05-19 : 09:05:35
|
| AjarnMark,Thanks for the help. That query works, except I still do not get a record listed if the person filled out only tblSurvey1 and not tblSurvey2. Is there a way that I can list everything in tblSurvey1 and if they don't have a record in tblSurvey2 that is one day later, I would like to list blanks for the rest of the row.Thanks, again! |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-05-19 : 09:32:14
|
Use left join rather than inner joinSELECT ... FROM tblSurvey1LEFT JOIN tblSurvey2 on tblSurvey1.UserID = tblSurvey2.UserID AND tblSurvey1.CollectionDate = (tblSurvey2.DateRecorded - 1)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
natg504
Starting Member
14 Posts |
Posted - 2005-05-19 : 10:36:07
|
| That works. Thank you! |
|
|
|
|
|
|
|
|
Query Question