Can I create a sub-table for one certain field?

  • Thread starter Thread starter mikeo
  • Start date Start date
M

mikeo

The table I've created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.
 
It sounds like you are trying to do this working directly in the tables. If
so, stop now!

Access is not a spreadsheet. The things you learned to do in Excel, you'll
need to unlearn, if you want to get good use from Access.

In a relational database like Access, it all starts with the data.

Please describe your "what" (the data you are working with), rather than the
"how" (as in, how you are trying to do something).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
The table I've created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.
Click to expand...

Tables - and subtable datasheets - are simply too limited in their
capabilities for what you want to do. They're not really designed for data
interaction; you would do MUCH better to use a Form with two (or three, or
seven) Subforms, along with combo boxes, option groups, and all the other
tools that Forms provide.

Take a look at some of these resources to see how best to make use of the many
tools Access provides:

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
 
The table I have created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.


It sounds more like you are trying to use a lookup table and possibly create a constraint. See if this is what you mean.
Create a new table called ProjectNames with 2 columns, an autonumber called projectnameid and a text column called projectname. Save that and fill the projectname column with your project names. Edit your Projects table, change the datatype for your projectname column to number. At the bottom of the screen in the field properties pane, select Lookup (hence the name). Change the display control type to Combobox, change rowsource type to table/query, select the newly created projectnames table as the rowsource, bound column is one, column count is 2, column widths 0;2. Save the table and switch to datasheet view, instead of typing in a name, you will choose it from a list. This ensures that each project has one and only one name, and those names are selected from a list of names.



mikeo wrote:

Can I create a sub-table for one certain field?
29-Jan-10

The table I have created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.

Previous Posts In This Thread:

Can I create a sub-table for one certain field?
The table I have created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.

It sounds like you are trying to do this working directly in the tables.
It sounds like you are trying to do this working directly in the tables. If
so, stop now!

Access is not a spreadsheet. The things you learned to do in Excel, you will
need to unlearn, if you want to get good use from Access.

In a relational database like Access, it all starts with the data.

Please describe your "what" (the data you are working with), rather than the
"how" (as in, how you are trying to do something).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
 
It sounds more like you are trying to use a lookup table and possibly create a constraint. See if this is what you mean.

Create a new table called ProjectNames with 2 columns,
an autonumber called projectnameid and a text column
called projectname. Save that and fill the projectname
column with your project names. Edit your Projects
table, change the datatype for your projectname column
to number. At the bottom of the screen in the field
properties pane, select Lookup (hence the name). Change
the display control type to Combobox, change rowsource
type to table/query, select the newly created
projectnames table as the rowsource, bound column is
one, column count is 2, column widths 0;2. Save the
table and switch to datasheet view, instead of typing
in a name, you will choose it from a list. This ensures
that each project has one and only one name, and those
names are selected from a list of names.
Hopefully this comes out legible.



Teig Levingston wrote:

Subtable vs Lookup Table
25-Mar-10

The table I have created has a sub-table already, but I'd like to make another
sub-table just for one column. Can I do this? For example, my table is called
projects. It has a sub-table connected called tasks. One of my fields in my
projects table is called project name. In that field or column, I'd like to
add a sub-table called project name list.


It sounds more like you are trying to use a lookup table and possibly create a constraint. See if this is what you mean.
Create a new table called ProjectNames with 2 columns, an autonumber called projectnameid and a text column called projectname. Save that and fill the projectname column with your project names. Edit your Projects table, change the datatype for your projectname column to number. At the bottom of the screen in the field properties pane, select Lookup (hence the name). Change the display control type to Combobox, change rowsource type to table/query, select the newly created projectnames table as the rowsource, bound column is one, column count is 2, column widths 0;2. Save the table and switch to datasheet view, instead of typing in a name, you will choose it from a list. This ensures that each project has one and only one name, and those names are selected from a list of names.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Circular Progress Indicator
http://www.eggheadcafe.com/tutorial...a-cc047643fd42/wpf-circular-progress-ind.aspx
 
(e-mail address removed)
The table I have created has a sub-table already, but I'd like to make
another
sub-table just for one column. Can I do this? For example, my table is
called
projects. It has a sub-table connected called tasks. One of my fields in
my
projects table is called project name. In that field or column, I'd like
to
add a sub-table called project name list.


It sounds more like you are trying to use a lookup table and possibly
create a constraint. See if this is what you mean.
Create a new table called ProjectNames with 2 columns, an autonumber
called projectnameid and a text column called projectname. Save that and
fill the projectname column with your project names. Edit your Projects
table, change the datatype for your projectname column to number. At the
bottom of the screen in the field properties pane, select Lookup (hence
the name). Change the display control type to Combobox, change rowsource
type to table/query, select the newly created projectnames table as the
rowsource, bound column is one, column count is 2, column widths 0;2. Save
the table and switch to datasheet view, instead of typing in a name, you
will choose it from a list. This ensures that each project has one and
only one name, and those names are selected from a list of names.



mikeo wrote:

Can I create a sub-table for one certain field?
29-Jan-10

The table I have created has a sub-table already, but I'd like to make
another
sub-table just for one column. Can I do this? For example, my table is
called
projects. It has a sub-table connected called tasks. One of my fields in
my
projects table is called project name. In that field or column, I'd like
to
add a sub-table called project name list.

Previous Posts In This Thread:

Can I create a sub-table for one certain field?
The table I have created has a sub-table already, but I'd like to make
another
sub-table just for one column. Can I do this? For example, my table is
called
projects. It has a sub-table connected called tasks. One of my fields in
my
projects table is called project name. In that field or column, I'd like
to
add a sub-table called project name list.

It sounds like you are trying to do this working directly in the tables.
It sounds like you are trying to do this working directly in the tables.
If
so, stop now!

Access is not a spreadsheet. The things you learned to do in Excel, you
will
need to unlearn, if you want to get good use from Access.

In a relational database like Access, it all starts with the data.

Please describe your "what" (the data you are working with), rather than
the
"how" (as in, how you are trying to do something).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
Click to expand...
 
Back
Top