Simple Question

[Nimish]

Hi all,

I am completely new to Access and trying to learn it through my simple
Investment management data. There will be tons of questions to be followed as
I start developing my skills and I sure I am in right place to learn from
this site.

I have book Access 2007 Inside Out and I find it extremely useful, but
sometimes a direct question in the group can help faster than surfing through
the pages.

I have following two tables - one to indicate securities I deal with and
other for trades -

Table Name : tblSecurities

Market: Text
Security:Text
Description: Text

Table Name : tblTrades

AccountNumber: Text
Market: Text
Security: Text
Action: Text
TradeDate: Date
Quantity: Number
Amount: Number
Commission: Number
Tax: Number

in tblTrades, I want restraints in Security column such that, after
selecting Market (US / CAN) I want to show only securities available in that
Market.

Is it possible in table design.

Thanks,

Nimish

 

[John W. Vinson]

Hi all,

I am completely new to Access and trying to learn it through my simple
Investment management data. There will be tons of questions to be followed as
I start developing my skills and I sure I am in right place to learn from
this site.

I have book Access 2007 Inside Out and I find it extremely useful, but
sometimes a direct question in the group can help faster than surfing through
the pages.

I have following two tables - one to indicate securities I deal with and
other for trades -

Table Name : tblSecurities

Market: Text
Security:Text
Description: Text

Table Name : tblTrades

AccountNumber: Text
Market: Text
Security: Text
Action: Text
TradeDate: Date
Quantity: Number
Amount: Number
Commission: Number
Tax: Number

in tblTrades, I want restraints in Security column such that, after
selecting Market (US / CAN) I want to show only securities available in that
Market.

Is it possible in table design.

No.

And you shouldn't be using table datasheets to interact with your data in any
case!

Tables are to store data; Forms are the proper tool to edit and enter data. If
you have a one to many relationship from Securities to Trades, one approach
would be to have a Form based on securities (with a combo box to navigate to
the particular security of interest), with a Subform for trades for that
security.

You might want to look at some of the tutorials here:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

_Inside Out_ is a great book but it's also very detailed, and may be confusing
when you're just getting started. Crystal's video or Roger's intro tutorial
might clarify some of the issues.

 

[Tom Wickerath]

Hi Nimish,

I think what you want to do is much easier accomplished using synchronized
combo boxes on a form.

Limit content of combo/list boxes
http://www.mvps.org/access/forms/frm0028.htm

By the way, Description (in tblSecurities) is a reserved word. I recommend
avoiding the use of any reserved words for things that you assign a name to
within Access (fields, tables, queries, forms, reports, macros, modules, and
the names of controls on forms and reports). Take a look at Access MVP Allen
Browne's page of reserved words:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

Database Issue Checker
http://www.allenbrowne.com/AppIssueChecker.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Nimish]

Tom,

Thanks for your help. The suggested way probably requires a form to be
designed for the underlying table.

Is is possible to accomplish this in Datasheet View of table.

Nimish
PS: Thanks for your additional input regarding reserved words. I have
renamed the column name of table.

 

[Nimish]

Thanks Tom and John,

I will design form to acquire data from user (I am the only user!!!)

Regards,
Nimish

 

[Tom Wickerath]

Hi Nimish,

Is is possible to accomplish this in Datasheet View of table.

Not that I can think of, off-hand. You could use a lookup field, but it
would not be filtered.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________