Returning too many lines

[TraderAnalysis]

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

 

[KARL DEWEY]

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.

 

[TraderAnalysis]

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).

 

[TraderAnalysis]

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);

 

[KARL DEWEY]

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

 

[Steve]

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
(e-mail address removed)

 

[TraderAnalysis]

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

 

[Steve]

See in-line responses ---

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

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?).

 

[Chris2]

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
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.

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.