Creating new row in other table

Dear All,

Please teach me, how to make a new row/list in other table by entering 
number in other table. Sample, In the table A and in the field "Details", I 
put the number 2 and it automatically creates two rows in table B. Can this 
be done in MS Access? Thank you for your help.

Regards,
Maulwy
0
Utf
3/7/2010 12:46:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

5 Replies
1275 Views

Similar Articles

[PageSpeed] 42

Why?  What business need are you attempting to solve with this approach?

It's generally considered not very good database design to be creating 
'empty' rows in a second table.  Why do you want empty rows?

And if the number you enter in TableA is a 'limit', why not just create new 
rows in TableB (related, of course), until that limit is reached?

-- 

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.

"Maulwy" <Maulwy@discussions.microsoft.com> wrote in message 
news:4BF7BF82-5F23-4024-8871-4BBE74E625AE@microsoft.com...
> Dear All,
>
> Please teach me, how to make a new row/list in other table by entering
> number in other table. Sample, In the table A and in the field "Details", 
> I
> put the number 2 and it automatically creates two rows in table B. Can 
> this
> be done in MS Access? Thank you for your help.
>
> Regards,
> Maulwy 


0
Jeff
3/7/2010 3:30:31 PM
On Sun, 7 Mar 2010 04:46:01 -0800, Maulwy <Maulwy@discussions.microsoft.com>
wrote:

>Dear All,
>
>Please teach me, how to make a new row/list in other table by entering 
>number in other table. Sample, In the table A and in the field "Details", I 
>put the number 2 and it automatically creates two rows in table B. Can this 
>be done in MS Access? Thank you for your help.
>
>Regards,
>Maulwy

As Jeff says, it's very rarely either necessary nor a good idea to create
empty "placeholder" records in a table. They have an unpleasant habit of never
getting filled in. 

The normal way to do this is to use a Form based on TableA, with a Subform
based on TableB, using the subform's Master/Child Link Field properties to
link them. When you enter data into the form and then the subform, Access will
automatically fill in the link, *when it is needed* and not before.

If you have some special need to do it the other way please explain.
-- 

             John W. Vinson [MVP]
0
John
3/7/2010 9:59:54 PM
Dear Jeff & John,

For example, if we do a sales order, where there is a column for the model, 
quantity and serial number. To model and quantity, comes from one table, 
while for the serial number, which I want, be inputted in the other table. 
But not all existing models, has a serial number, this is the basic one, the 
serial number I did not join in a single table. To avoid input mistakes 
serial number, if the quantity, I suppose input 2, then automatically, the 
database will add the 2 lines at the next table (the serial number tables). 
Database like this is what I mean. Example data:
Model TransID Qty
1 AAA 2
2 BBB 3

TransID SerialNumber
1 AAAXXX01
1 AAAXXX02
2 BBBXXX01
2 BBBXXX50
2 BBBXXX65

Rgds,
Maulwy

"John W. Vinson" wrote:

> On Sun, 7 Mar 2010 04:46:01 -0800, Maulwy <Maulwy@discussions.microsoft.com>
> wrote:
> 
> >Dear All,
> >
> >Please teach me, how to make a new row/list in other table by entering 
> >number in other table. Sample, In the table A and in the field "Details", I 
> >put the number 2 and it automatically creates two rows in table B. Can this 
> >be done in MS Access? Thank you for your help.
> >
> >Regards,
> >Maulwy
> 
> As Jeff says, it's very rarely either necessary nor a good idea to create
> empty "placeholder" records in a table. They have an unpleasant habit of never
> getting filled in. 
> 
> The normal way to do this is to use a Form based on TableA, with a Subform
> based on TableB, using the subform's Master/Child Link Field properties to
> link them. When you enter data into the form and then the subform, Access will
> automatically fill in the link, *when it is needed* and not before.
> 
> If you have some special need to do it the other way please explain.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/8/2010 2:39:01 PM
Dear All,

Sorry, the example should be:

TransID Model Qty
1 AAA 2
2 BBB 3

TransID SerialNumber
1 AAAXXX01
1 AAAXXX02
2 BBBXXX01
2 BBBXXX50
2 BBBXXX65 (null?)

Many Thanks,
Maulwy

"Maulwy" wrote:

> 
> Dear Jeff & John,
> 
> For example, if we do a sales order, where there is a column for the model, 
> quantity and serial number. To model and quantity, comes from one table, 
> while for the serial number, which I want, be inputted in the other table. 
> But not all existing models, has a serial number, this is the basic one, the 
> serial number I did not join in a single table. To avoid input mistakes 
> serial number, if the quantity, I suppose input 2, then automatically, the 
> database will add the 2 lines at the next table (the serial number tables). 
> Database like this is what I mean. Example data:
> Model TransID Qty
> 1 AAA 2
> 2 BBB 3
> 
> TransID SerialNumber
> 1 AAAXXX01
> 1 AAAXXX02
> 2 BBBXXX01
> 2 BBBXXX50
> 2 BBBXXX65
> 
> Rgds,
> Maulwy
> 
> "John W. Vinson" wrote:
> 
> > On Sun, 7 Mar 2010 04:46:01 -0800, Maulwy <Maulwy@discussions.microsoft.com>
> > wrote:
> > 
> > >Dear All,
> > >
> > >Please teach me, how to make a new row/list in other table by entering 
> > >number in other table. Sample, In the table A and in the field "Details", I 
> > >put the number 2 and it automatically creates two rows in table B. Can this 
> > >be done in MS Access? Thank you for your help.
> > >
> > >Regards,
> > >Maulwy
> > 
> > As Jeff says, it's very rarely either necessary nor a good idea to create
> > empty "placeholder" records in a table. They have an unpleasant habit of never
> > getting filled in. 
> > 
> > The normal way to do this is to use a Form based on TableA, with a Subform
> > based on TableB, using the subform's Master/Child Link Field properties to
> > link them. When you enter data into the form and then the subform, Access will
> > automatically fill in the link, *when it is needed* and not before.
> > 
> > If you have some special need to do it the other way please explain.
> > -- 
> > 
> >              John W. Vinson [MVP]
> > .
> > 
0
Utf
3/8/2010 2:48:01 PM
Why not use a form instead of table A?  Where will the query get the serial 
numbers to use in table B?

-- 
Build a little, test a little.


"Maulwy" wrote:

> Dear All,
> 
> Sorry, the example should be:
> 
> TransID Model Qty
> 1 AAA 2
> 2 BBB 3
> 
> TransID SerialNumber
> 1 AAAXXX01
> 1 AAAXXX02
> 2 BBBXXX01
> 2 BBBXXX50
> 2 BBBXXX65 (null?)
> 
> Many Thanks,
> Maulwy
> 
> "Maulwy" wrote:
> 
> > 
> > Dear Jeff & John,
> > 
> > For example, if we do a sales order, where there is a column for the model, 
> > quantity and serial number. To model and quantity, comes from one table, 
> > while for the serial number, which I want, be inputted in the other table. 
> > But not all existing models, has a serial number, this is the basic one, the 
> > serial number I did not join in a single table. To avoid input mistakes 
> > serial number, if the quantity, I suppose input 2, then automatically, the 
> > database will add the 2 lines at the next table (the serial number tables). 
> > Database like this is what I mean. Example data:
> > Model TransID Qty
> > 1 AAA 2
> > 2 BBB 3
> > 
> > TransID SerialNumber
> > 1 AAAXXX01
> > 1 AAAXXX02
> > 2 BBBXXX01
> > 2 BBBXXX50
> > 2 BBBXXX65
> > 
> > Rgds,
> > Maulwy
> > 
> > "John W. Vinson" wrote:
> > 
> > > On Sun, 7 Mar 2010 04:46:01 -0800, Maulwy <Maulwy@discussions.microsoft.com>
> > > wrote:
> > > 
> > > >Dear All,
> > > >
> > > >Please teach me, how to make a new row/list in other table by entering 
> > > >number in other table. Sample, In the table A and in the field "Details", I 
> > > >put the number 2 and it automatically creates two rows in table B. Can this 
> > > >be done in MS Access? Thank you for your help.
> > > >
> > > >Regards,
> > > >Maulwy
> > > 
> > > As Jeff says, it's very rarely either necessary nor a good idea to create
> > > empty "placeholder" records in a table. They have an unpleasant habit of never
> > > getting filled in. 
> > > 
> > > The normal way to do this is to use a Form based on TableA, with a Subform
> > > based on TableB, using the subform's Master/Child Link Field properties to
> > > link them. When you enter data into the form and then the subform, Access will
> > > automatically fill in the link, *when it is needed* and not before.
> > > 
> > > If you have some special need to do it the other way please explain.
> > > -- 
> > > 
> > >              John W. Vinson [MVP]
> > > .
> > > 
0
Utf
3/8/2010 3:46:01 PM
Reply:

Similar Artilces:

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data? I have a data entry sheet. Then I have a report. The report pulls data from the entry sheet. If there is no data for a specific line/row item, is there a way to automatically hide or not show the row(s) with no data? Thanks Thanks can I have more than one autofilter on a sheet? Sloth wrote: > Use the filter function > Select the data and click on... > Data->Filter->Autofilter > This should make an arrow appear at the top of the data (in the header row). > click the arror and select "Nonblanks"....

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Setting Defaults in New Item Wizard
How can I set a default value in the New Item Wizard? I want to always use a barcode type that is about 10 down from the top and I want Taxable Item to always be checked. Help... Good question, I need an answer too. "A StanTech Associate" wrote: > How can I set a default value in the New Item Wizard? I want to always use a > barcode type that is about 10 down from the top and I want Taxable Item to > always be checked. Help... You can set the default sales tax under Manager, File, Configuration, Sales Tax, Default item tax group. To set a default barcode type yo...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...

new activity not in the appropriate queue
Help! I installed the Rollup 3 for MS CRM 4 and since then all created activities are redirected to the users assigned folder in queue and not in progress(my current activities) folder! It's not logical because a new activity is not yet assigned to a different user to be in the assigned folder !! I can't believe this is on purpose and I DON'T have a workflow on this. Can someone tell me how to fix this? ...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

How do I create upper/lower case letters in cells?
I have a large spreadsheet with names/addresses that are all capitalized. I want to make them upper and lower case (SMITH = Smith). What's the formula? You could create helper cells with this formula =proper(A1) "boz130" wrote: > I have a large spreadsheet with names/addresses that are all capitalized. I > want to make them upper and lower case (SMITH = Smith). What's the formula? ...

Pivot Table Summary Error?
Greetings, Sorry, wasn't sure which subforum to post this in, as there's nothing dedicated to pivot table. My dilemma is as follows: I have a spreadsheet where data is entered daily. For my example, just imagine a spreadsheet with three columns: date, date, delay. Two date columns are used for the pivot table. The pivot table is displayed with month and weekly ranges as the row headers. There is a field in the pivot table that is summarized as a MAX of one of the source data columns. Now, the problem I am coming across is the monthly summary MAX is not c...