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

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.
0
Utf
1/29/2010 9:29:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1289 Views

Similar Articles

[PageSpeed] 49

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.

"mikeo" <mikeo@discussions.microsoft.com> wrote in message 
news:A6E94AFD-A80F-415F-9EAB-728CFA50EF6B@microsoft.com...
> 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. 


0
Jeff
1/29/2010 11:47:24 PM
On Fri, 29 Jan 2010 13:29:01 -0800, mikeo <mikeo@discussions.microsoft.com>
wrote:

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

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


-- 

             John W. Vinson [MVP]
0
John
1/30/2010 12:20:54 AM
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:

On Friday, January 29, 2010 4:29 PM
mikeo wrote:

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.

On Friday, January 29, 2010 6:47 PM
Jeff Boyce wrote:

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/tutorials/aspnet/223c6e57-f81f-44b3-ab05-5995f2b0ab63/sharepoint-video-library.aspx
0
Teig
3/25/2010 3:20:43 PM
jhall@myglnc.comm
"Teig Levingston" wrote in message news:2010325112043teig@levingstons.net...
> 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:
>
> On Friday, January 29, 2010 4:29 PM
> mikeo wrote:
>
> 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.
>
> On Friday, January 29, 2010 6:47 PM
> Jeff Boyce wrote:
>
> 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/tutorials/aspnet/223c6e57-f81f-44b3-ab05-5995f2b0ab63/sharepoint-video-library.aspx 

0
James
3/25/2010 10:04:03 PM
Mabuk-mabuk....ehehheheh

MoneiTakuya
Ba, ada yang nak curi data ka? O..diff format..


"James Hall" <Jhall@microsoft.com> wrote in message 
news:0F7B41D9-FB96-4DF1-B51B-76CBF787729A@microsoft.com...
> jhall@myglnc.comm
> "Teig Levingston" wrote in message 
> news:2010325112043teig@levingstons.net...
>> 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:
>>
>> On Friday, January 29, 2010 4:29 PM
>> mikeo wrote:
>>
>> 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.
>>
>> On Friday, January 29, 2010 6:47 PM
>> Jeff Boyce wrote:
>>
>> 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/tutorials/aspnet/223c6e57-f81f-44b3-ab05-5995f2b0ab63/sharepoint-video-library.aspx
>

 

0
Eighte
3/26/2010 12:17:41 AM
Reply:

Similar Artilces:

Novice trying to create zip code look-up
I know that it is possible to create a situation where a zip code is typed into one column, and the city identified with that zip code pops up in another column, but cannot figure out where to start with this. Can anyone give some guidance? -- Karen Karen, Use a 2 column combo box. (ex. cboZip) First Column for Zipcode and second column for City. Set the combo's ControlSource (bind it) to your ZipCode field. Since this combo control will capture the ZipCode, it is not necessary to also "capture" the City value in another control... just "...

Pivot table question #9
I recently had a problem with values not showing properly in my PivotTable, until it was poited out to me that I was using Text in some cells instead of numbers. Ideally, I would like to include text. Is there anyway at all that I can? My data comes from an Access Table by the way My reasoning. the data in the pivot shows scores overall of several years competition, and each meet has 2 reserves, who can step in for an excluded competitor. However, the reserves might not compete, but still need to be recorded as a competitor at the event. Rather than showing zero (0) as their score, I'd r...

How can you customize the left Menu?
Hi all, I would like to display only "Opportunity" in the "Sales" area. How can do that? Thanks in advance. Alan Alan, I know you can add items to the menu through the ISV config option. If you can't remove items you may have to develop a small application to just show the opportunities. Calvin Luttrell How e-commerce is done. http://blog.projectthunder.com http://www.projectthunder.com "Alan" <Alan@discussions.microsoft.com> wrote in message news:77D317F7-3065-47F3-9031-DF83A64EA9AA@microsoft.com... > Hi all, > > I would like to dis...

Can Microsoft Outlook import email sent to AOL?
AOL 9.0 permits members to save email on their own computer, but clicking on that file to open still seems to link back to AOL as if still on their server. Will Microsoft Outlook retrieve my email from AOL so that I can read it offline and truly save to my computer? can you copy it back to the aol mailbox? if so, connect ot the mailbox using imap to get it into outlook. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Ti...

csv-files with '+' in field -> treated as formula
When I make up a CSV-file (from our oracle-database) we have to fill in a line like this: 9603144;01;TEST;+BE;01/01/1000;31/12/9999 When opening the file in Excel, the 4th field (+BE) is treated as a formula, but that is not an option. Anyone have an idea how to write it in the CSV-file so that it is treated as a text-field but without showing any quotes (so I would like to see +BE and not '+BE) Tnx already in advance! Dave found something myself: just add a space before the + and it's ok if anyone has another idea: post it >-----Original Message----- >When I make up ...

Lost Subject Field
I don't know how I did it but somehow I managed to delete the subject field in outlook so now when I go to create a new email to send to someone I only have the TO: line showing! I am unable to enter a subject! How do I get the subject back???? Go up to VIEW | MESSAGE HEADER. Kenny "Rockets" <wcrockets@hotmail.com> wrote in message news:00b601c3dc66$d8456fb0$a501280a@phx.gbl... > I don't know how I did it but somehow I managed to delete > the subject field in outlook so now when I go to create a > new email to send to someone I only have the TO: line &g...

2 Subforms, can't change link fields for one
I have two subforms on my main form. The second is a copy of the first, but I want to link it to separate fields on the main form. When I open the main form in design view, the first subform shows in design view, but the second is just a blank control. The controlsource field is set to the same as the first. When I try to change the link child/master fields, it says: "The expression you entered refers to an object that is closed or doesn't exist." and blanks it out. What am I doing wrong? Thanks, =Ray= ...

Creating a closed Phone activity in a single step
Hi, I have looked absolutely everywhere for a solution to this. As many of our processes are still outside of MS CRM much of our outbound sales/prospecting calling is driven by external processes. (example 1) our receptionist (who has no CRM access) gets a call from an existing customer interested in purchasing product from us. The account manager gets the message, looks up the record in CRM and immediately calls them. We need the sales person to record a phone activity to say they've 'touched' this account, however this isn't a to-do item, it is already closed. ...

MoveCopy, unable to create test msg
I have a user running Office 2002 SP2 on Windows XP. Recently he began receiving the following message when he opens Outlook for the first time each day. "Server-Requested client action - MoveCopy, unable to create test msg" I can log in at that PC and setup Outlook without issue. does this appear to be a profile issue? I would appreciate any assistance on this. Best regards, Jim ...

Can someone help me to understand schema locations?
I have this xml: [xml] <Report p1:schemaLocation="Data_Feed http://server/reportserver?param1=foo&param2=bar" Name="Data Feed" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="Data_Feed"> <Data/> </Report> [/xml] And, I'm trying to read it... without success. I'm using code from this KB318545 (http://support.microsoft.com/kb/318545), but not having much luck. That its not working is probably because I don't understand how to map the info above to the parameters needed ...

can't send messsages in window mail
I keep getting an error code (0x800cccD) and yet i can recieve mail in the sme ppage Please post the error message in its entirety. You can left click on it to highlight and then right click to copy and then paste it into this thread. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Lonnie" <Lonnie@discussions.microsoft.com> wrote in message news:12CAB0C7-C8EE-4B3D-8099-23F52C9930C6@microsoft.com... >I keep getting an error code (0x800cccD) and yet i can recieve mail in >the > sme ppage I tried moving all mess...

Can not scroll down
Hi I was able to scroll down in my spread sheet before but know I am not able to. I don't have a clue how to correct this problem. Hi Felipe! One possibility but only if you're zooming instead of scrolling: Tools > Options > General Remove check from "Zoom on Roll with Intellimouse" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. =?Utf-8?B?RmVsaXBl?= <anonymous@discussions.microsoft.com> wrote in news:3E6A8F9A-6FCA-4542-A...

Can't Clear Check
In Money 2002 I have two accounts, checking and credit card. The check written to pay the credit card won't accept the mark as c entry, nor will it from the credit card account. No change to this entry can be made at all, date, amount, memo, etc. The program displays, "This transaction could not be entered." or "Money could not write to your Money file. The operation cannot be performed or another application finished this task before you." Money doesn't crash, all other functions work (so far). I've used both the Salvage and Super Salvage and no ...

I can't print my incoming e-mail. Jibberish is printed instead.
Is this a new issue or has it always happened? "Digger" <Digger@discussions.microsoft.com> wrote in message news:16BA329C-4D87-46E3-BB8D-D29777466EA3@microsoft.com... > ...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

Can exchange2000 delay large messages?
Hiya. I have taken over the administration of an Exchange2000/PD server. Some of the network users are sending stupidly big files over email during the middle of the day, and this is filling up the internet link, so I want to delay them to be sent after hours. If I can't do that, can I limit the size altogether? Can I define what is a large message? I am not really an experienced Exchange admin, coming over to it from the land of linux and postfix. TIA googleboy. Assuming you are going to continue to let them send out these large messages, you can do this with a SMTP connector. ...

can't send replies to msgs
Try to send a reply to a received message and computer freezes? Whats up? We'd need a lot more info to help you - version of Outlook, SP level, type of email account, how big is your PST file? anonymous@discussions.microsoft.com wrote: > Try to send a reply to a received message and computer > freezes? Whats up? ...

can I have a multiselect option for a list box in excel 2003
I am working with Excel 2003, I created different list boxes for different fields but I wanted to know if I could select more then one answer by using CTRL command? Is that only available for later versions? thanks Yes, you can. Depending on what type of listbox it is and where it's located, the instructions to modify the settings would be different. If it's a listbox from the Forms toobar placed on the sheet, then rightclick on it and choose Format Control, then Control tab and choose Multi. If it's a listbox from the Control toolbox toolbar placed on a workshee...

Can I just reinstall Pub 2000 when it is part of Office 2000
Having just upgraded to Office 2003 I am tearing my hair out with Publisher 2003. I just cannot get it to work with my web site files made in Pub 2000. My site is now out of date and I haven't got time to learn Front Page! Can I un-install Pub 2003 and re-install Pub 2000 even if they are part of Office 2000 and 2003? Help!! You can install Publisher 2000 as a standalone application. Create a separate folder on your hard drive and install it there. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Maudie" <Maud...

Outlook From Field Disappears
The from field for the inbox disappeared. When I try to re-add it, Outlook crashes. Office XP Service Pack 3 on a Windows XP Pro workstation. Error Signature: AppName: outlook.exe AppVersion: 10.0.6626.0 ModName: outllib.dll ModVersion: 10.0.6515.0 Offset: 0028e199 I have uninstalled Office, ran regclean and reinstalled to no avail. I also tried replacing the dll from another install of Office. Any ideas? Please email me if possible. Thanks, Rob Coombe ...

at least one recipient does not have an e-mail error
This error message continues to show up after our upgrade from CRM3 to CRM4: At least one recipient does not have an e-mail address or is marked as "Do Not Allow" e-mail. It happens in emails and appointments that I've noticed so far. It can happen to an appointment when only one user is both the required attendee and the regarding person. I have looked for any missing emails in the user records, and the email is there. The user is capable of receiving emails. I have looked for inactive and active contacts, and open and closed leads to see if there is another object wi...

Fields... what's going on
Hi Sorry, I expect this is really basic. I'm trying to write fields in Word 2000 with the IF command. The problem is that, suddenly, when I try to write them (CTRL+F9), I get the braces fine, but can only input the letter "I" - nothing else. 1 character, and it can only be a capital "i". Can someone tell me why this is, and how to stop it? It worked fine until 1/2 hour ago. *sob* Thanks in advance Steve This newsgroup is for discussions about Microsoft Access, a relational database program. A Word newsgroup would be a better choice for your question. "Cr...

Size of file with pivot table
I have a file that was 8MB when I started. I created a pivot table pulling 7 fields from a worksheet. Now the size is 20MB. Any ideas why this one pivot table would cause the file to be so large when the 8MB file already had pivot table sin it? When it was 8MB, it had 6 pivot tables. Now it's 20MB and it has 7 pitvot tables. Why is this one table so much larger? Were the 6 PivotTable independent, or did they all use the same cache? If you build PivotTables that use another PivotTable as reference (for when you're reference the same raw data, just taking a different...

tables in PowerPoint
I have made a 25"x25 " banner. I want to add a grid that will display on the banner when i print it. I put a 25 x 25 cell table on it, and I can make it fit horizontally but I can't compress it vertically. How can I do that? Atlernatively, is there a better way to put a grid on the banner? I have a picture underneath that I want to show through the grid. Thanks. Change the internal margin of the cells to 0 for top and bottom. You may need to change the font size to something like 2 point (or as small as you can go) also. -- Echo [MS PPT MVP] http://www....

Exporting data from a table to blogs
Hi, i have seen the sharepoint function offered by MS Excel. Would like to check with you whether this could be performed and exported into a blog? I've used sharepoint, but am not sure what you're talking about. Can you give some specific info on what you want to do? "owen.cxy" wrote: > Hi, i have seen the sharepoint function offered by MS Excel. Would like to > check with you whether this could be performed and exported into a blog? As in i have some data in MS Excel which i would like to export it and publish into a blog. Is it possible and is sharepoint ...