Question on displaying repeating data

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hello,

I am developing a financial application that tracks various aspects of the stock and financial markets. I am currently developing it using Access 2003 although eventually, all the data will reside in SQL Server. Access will still provide the UI.

On 1 form, which has to sections (top and bottom), I want to enter/display opening and closing stock prices for a given company. On the bottom half of the same form, I want to display a given number of historical opening/closing prices. The list will be in reverse order based on the current. For example, the last 10 days beginning yesterday. There will be a text box so the user can input the number of historical quotes. The query logic is relatively simple. Something like:
Select date, opening_quote, closing_quote
from tbl_daily_quotes
where date is in (today's date - 10 days). I obvisously don't know the SQL syntax here, but I think the meaning is clear.

My problem is that I don't know how to display this. How can I set up what I imagine is basically an array or table to display the data?

Also, a little help in getting my SQL Syntax is also appreciated.

Many thanks in advance,

Rich
 
Rich said:
Hello,

I am developing a financial application that tracks various aspects
of the stock and financial markets. I am currently developing it
using Access 2003 although eventually, all the data will reside in
SQL Server. Access will still provide the UI.

On 1 form, which has to sections (top and bottom), I want to
enter/display opening and closing stock prices for a given company.
On the bottom half of the same form, I want to display a given number
of historical opening/closing prices. The list will be in reverse
order based on the current. For example, the last 10 days beginning
yesterday. There will be a text box so the user can input the number
of historical quotes. The query logic is relatively simple.
Something like:
Select date, opening_quote, closing_quote
from tbl_daily_quotes
where date is in (today's date - 10 days). I obvisously don't know
the SQL syntax here, but I think the meaning is clear.

My problem is that I don't know how to display this. How can I set
up what I imagine is basically an array or table to display the data?

Also, a little help in getting my SQL Syntax is also appreciated.

Many thanks in advance,

Rich
Click to expand...

A subform in continuous view seems the most likely way to display this
information. The subform's recordsource would be the query that returns
the last 10 days of quotes. You don't actually say, but would these be
quotes for the specific company that is shown on the main form? Then
you'll need to include the company code (or other identifier) in the
query and use the Link Master/Child Fields of the subform control to
identify that field on the main form and subform. Your query would look
something like this:

SELECT CompanyCode, StockDate, Opening_Quote, Closing_Quote
FROM tbl_Daily_Quotes
WHERE StockDate >= DateAdd("d", -10, Date())
 
Dirk,

Thanks for the quick reply but I don't quite understand what you mean.
Still looking at this, I have not set this up as a sub-form, which I am now
doing. When going through the form/sub-form wizard, I ams opting to use
existing table, then I select the 4 columns from the daily_quotes table.
Next, I have to choose the link from the main form, to the subform. I have
to "choose from a list", or "Define my own". Problem is that I cannot read
what is presented when I opt to choose from a list. The dialog does not
scroll right and the visible portion of each choice is identical. Duhhh. I
basically want to link on the stock symbol on the primary form (I think)
such that for whatever stock symbol is selected on primary, I get the 10 (or
whatver #) or quotes on the bottom for the same symbol.

Thanks,
Rich
 
Ok, I have the subform. I have 1 row of data. Not sure how to get it to
display more than 1 row. Also, to what data source to I apply the SQL?
 
Rich said:
Dirk,

Thanks for the quick reply but I don't quite understand what you mean.
Still looking at this, I have not set this up as a sub-form, which I
am now doing. When going through the form/sub-form wizard, I ams
opting to use existing table, then I select the 4 columns from the
daily_quotes table. Next, I have to choose the link from the main
form, to the subform. I have to "choose from a list", or "Define my
own". Problem is that I cannot read what is presented when I opt to
choose from a list. The dialog does not scroll right and the visible
portion of each choice is identical. Duhhh. I basically want to
link on the stock symbol on the primary form (I think) such that for
whatever stock symbol is selected on primary, I get the 10 (or
whatver #) or quotes on the bottom for the same symbol.
Click to expand...

Do you have extraordinarily long field names? I know the wizard's list
boxes are sometimes too limited. But you can enter them yourself if you
know the names of the fields in the main table and the daily_quotes
table, and how they should be related.
 
Rich said:
Ok, I have the subform. I have 1 row of data. Not sure how to get
it to display more than 1 row. Also, to what data source to I apply
the SQL?
Click to expand...

If you've set it up right, the subform will show all records related to
the current main-form record. The SQL I gave you, after you've fixed up
the names of the various fields, would replace the Record Source
property of the form that was created to serve as the subform.
 
Back
Top