 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Appending data and Cross Table queries Question
Listers I have 2 tables: Table 1: Date Category Number ------------------------------------------------- 11/06/05 Cat1 1 11/06/05 Cat2 1 . . . . 12/05/05 Cat1 2 Table 2: Categories --------------- Cat1 Cat2 Cat3 Cat4 Cat5 Somehow, through CrossTable Query, I have managed to obtain the following: Category 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat3 15 0 1 Cat5 8 5 However, I want the output to be more like this: Category 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat2 Cat3 15 0 1 Cat4 Cat5 8 5 How can I do this? Your help is much appreciated Just posting again because of formatting: Listers I have 2 tables: Table 1: Date Category Number ------------------------------------------------- 11/06/05 Cat1 1 11/06/05 Cat2 1 . . . . 12/05/05 Cat1 2 Table 2: Categories --------------- Cat1 Cat2 Cat3 Cat4 Cat5 Somehow, through CrossTable Query, I have managed to obtain the following: Category 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat3 15 0 1 Cat5 8 5 However, I want the output to be more like this: Category 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat2 Cat3 15 0 1 Cat4 Cat5 8 5 Change the join to Table 2 so that all records are returned from Table 2 in the join properties.  SignatureDuane Hookom MS Access MVP > Listers > [quoted text clipped - 45 lines] > > How can I do this? Your help is much appreciated Hello Duane Thanks for your response and your suggestion did work outputting as follows: Category / 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat2 0 Cat3 15 0 1 Cat4 0 Cat5 8 5 What should I do to return the data for a particular month (obtain through a parameter such that the user selects the data). Example the user enters 2006/3 to obtain: Category 2006/3 ------------------------------------------------------------------------------------------------ Cat1 1 Cat2 0 Cat3 1 Cat4 0 Cat5 5 Again, your response will be much appreciated. Regards, Rahman Mooraby > Change the join to Table 2 so that all records are returned from Table 2 in > the join properties. [quoted text clipped - 52 lines] > > > > How can I do this? Your help is much appreciated I would create a new query that joins your crosstab with the category table. Use the appropriate join in the new query. SignatureDuane Hookom MS Access MVP Hello Duane Thanks for your response and your suggestion did work outputting as follows: Category / 2005/12 2006/1 2006/2 2006/3 ..... ------------------------------------------------------------------------------------------------ Cat1 10 5 1 Cat2 0 Cat3 15 0 1 Cat4 0 Cat5 8 5 What should I do to return the data for a particular month (obtain through a parameter such that the user selects the data). Example the user enters 2006/3 to obtain: Category 2006/3 ------------------------------------------------------------------------------------------------ Cat1 1 Cat2 0 Cat3 1 Cat4 0 Cat5 5 Again, your response will be much appreciated. Regards, Rahman Mooraby Duane Hookom wrote: > Change the join to Table 2 so that all records are returned from Table 2 > in [quoted text clipped - 53 lines] > > > > How can I do this? Your help is much appreciated |
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.