 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2007Returning too many lines
I have 2 tables that I have linked in a query by stock. I have then created 5 columns by Exchange where the stock is traded (Table 1) and at by looking up who trades it (Table 2). I used if statements to get the results (if a stock trades here, show me the trader). All works very well but when I run the query, there is multiple lines for each stock. So for example, 3 lines of stock ABC has trader1 trading at Exchange1, trader2 at Exchange2 and trader3 at Exchange 6 and all are on different lines. How can I get it to return one line by stock and each trader across the one line? FYI - only one trader trades a stock by Exchange so it's not as if ABC is traded by 4 traders at Exchange1. Thanks It sounds like a crosstab query will do what you want. Post the table and field names with their datatype and a sample of data.  SignatureKARL DEWEY Build a little - Test a little > I have 2 tables that I have linked in a query by stock. I have then created > 5 columns by Exchange where the stock is traded (Table 1) and at by looking [quoted text clipped - 8 lines] > > Thanks I don't know for sure how to post the data???? HELP. Also, I think the problem with a crosstab is that it will only let me have 1 column and I want to have 5 (# of Exchanges). > It sounds like a crosstab query will do what you want. Post the table and > field names with their datatype and a sample of data. [quoted text clipped - 11 lines] > > > > Thanks Type the name of the table and list the fields below like this -- RESERVE NAMES -- Issue - text AMEX - text CBOE - text ARCA - text ISE - text PHLX - text SP ADDS -- Issue - text Expr1 - text Then put a sample of the data like this -- RESERVE NAMES -- Issue AMEX CBOE ARCA ISE PHLX CSU X CSX X ABC X XYZ X SP ADDS -- Issue Expr1 CSU AMEX ABC ISE XYZ CBOE SignatureKARL DEWEY Build a little - Test a little > I don't know for sure how to post the data???? HELP. > Also, I think the problem with a crosstab is that it will only let me have 1 [quoted text clipped - 15 lines] > > > > > > Thanks SELECT [SP ADDS].Issue, IIf([SP ADDS]![Expr1] Like "*AMEX",[RESERVE NAMES]![AMEX],Null) AS AMEX, IIf([SP ADDS]![Expr1] Like "*CBOE",[RESERVE NAMES]![CBOE],Null) AS CBOE, IIf([SP ADDS]![Expr1] Like "*ARCA",[RESERVE NAMES]![ARCA],Null) AS ARCA, IIf([SP ADDS]![Expr1] Like "*ISE",[RESERVE NAMES]![ISE],Null) AS ISE, IIf([SP ADDS]![Expr1] Like "*PHLX",[RESERVE NAMES]![PHLX],Null) AS PHLX FROM [RESERVE NAMES] INNER JOIN [SP ADDS] ON [RESERVE NAMES].Issue = [SP ADDS].Issue GROUP BY [SP ADDS].Issue, IIf([SP ADDS]![Expr1] Like "*AMEX",[RESERVE NAMES]![AMEX],Null), IIf([SP ADDS]![Expr1] Like "*CBOE",[RESERVE NAMES]![CBOE],Null), IIf([SP ADDS]![Expr1] Like "*ARCA",[RESERVE NAMES]![ARCA],Null), IIf([SP ADDS]![Expr1] Like "*ISE",[RESERVE NAMES]![ISE],Null), IIf([SP ADDS]![Expr1] Like "*PHLX",[RESERVE NAMES]![PHLX],Null); > It sounds like a crosstab query will do what you want. Post the table and > field names with their datatype and a sample of data. [quoted text clipped - 11 lines] > > > > Thanks Your problem stems from incorrectly designed tables. Recommended: TblTrader TraderID TraderFirstName TraderLastName <<Other trader contact fields>> TblExchange ExchangeID ExchangeName TblStock StockID StockName StockSymbol TblStockTradedOnExchange StockTradedOnExchangeID StockID ExchangeID TblStockTrade StockTradeID TraderID StockTradedOnExchangeID TradeDate SharesTraded To get the display you want, you need to create a form/subform. The main form would be based on TblStock. For the subform you need to create a query that includes TblTrader, TblStock, TblExchange and TblStockTradedOnExchange. This query would have fields for StockID, trader name, exchange, trade date and shares traded. PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com >I have 2 tables that I have linked in a query by stock. I have then >created [quoted text clipped - 13 lines] > > Thanks It would take me forever to create all of those new tables. The tables I currently have are being used for multiple sources so I really don't want to change them and want to keep it down to minumum (doesn't sound possible?). So here is a breakdown of my tables: Reserved Names Issue: Stock AMEX: text CBOE: test ARCA: test, etc. SP Adds Expr1: stockexchange Issue: Stock Floor: Exchange So I link the Stock and Stock and then apply my if statements to create seperate columns which I need to import into Excel. I'm not too familar with forms and subforms but if that is the only way to reach my goal, I'll try and learn. Any more guidance would be greatly appreciated. Thanks > Your problem stems from incorrectly designed tables. Recommended: > TblTrader [quoted text clipped - 52 lines] > > > > Thanks See in-line responses --- PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com > It would take me forever to create all of those new tables. It takes minimal time to create new tables in design view. You would then need to create a set of append queries to append the data in your existing tables to the new tables. Your current design of the tables will constantly give you problems beginning with exporting data to Excel. As a side note, there probably is no need to export to Excel. Access can most likely do whatever it is you want to do in Excel. The tables I > currently have are being used for multiple sources so I really don't want > to > change them and want to keep it down to minumum Normalized tables is the goal of EVERY database. Minimum number of tables should never be a goal. (doesn't sound possible?). > So here is a breakdown of my tables: > [quoted text clipped - 79 lines] >> > >> > Thanks > It would take me forever to create all of those new tables. Trader Analysis, Good database design does require some effort. Forever, though, is an exageration. > The tables I > currently have are being used for multiple sources so I really don't want to [quoted text clipped - 6 lines] > CBOE: test > ARCA: test, etc. That table design is going to give you a permanent set of problems. Steve's general design is more normalized and thus will better support queries. Here are some general database, SQL, and and MS Access resources. Websites: http://www.mvps.org/access http://allenbrowne.com/ http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Top http://www.rogersaccesslibrary.com/ http://www.accessmvp.com/djsteele/SmartAccess.html Books: General: Beginner Database Design for Mere Mortals by Michael J. Hernandez SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas Books: General: Intermediate/Advanced Advanced SQL Programming, For Smarties, 3rd Edition, by Joe Celko SQL Puzzles and Answers, by Joe Celko Books: General: Advanced The Data Modeling Handbook, Michael C. Reingruber and William W. Gregroy Books: Access : Intermediate Access Cookbook by Getz, Litwin, and Baron (Compilation of solutions, listed by task-category) Books: Access: Advanced Access Database Design & Programming by Steven Roman (3rd Edition) Access Developer's Handbook (for your version of Access) Sincerely, Chris O. |
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.