Adding new records to several tables

I have several tables in my database that have the same primary key,
"site_id."  I am constantly adding new sites to the database, so I am
trying to figure out a way to streamline the process.  As I add
additional sites, I would like new records to be created in several
other specified tables.  I'd like to avoid having to create a new
record by hand in each table, each time I have a new site.

Can anyone help point me in the right direction?  Thanks.

0
lecoughlin
5/8/2007 2:38:09 PM
access 16762 articles. 3 followers. Follow

4 Replies
624 Views

Similar Articles

[PageSpeed] 34

If you include all the tables in your form's record source, you can then 
create invisible fields on your form that store the "site_id" for each table 
and have it filled in.  Build code in the main (visible) site_id control on 
your form that fires after update.  Have it copy that value to the other 
invisible fields on the form.

-- 
Hope that helps!

RBear3
..

<lecoughlin@gmail.com> wrote in message 
news:1178635089.641834.22330@w5g2000hsg.googlegroups.com...
>I have several tables in my database that have the same primary key,
> "site_id."  I am constantly adding new sites to the database, so I am
> trying to figure out a way to streamline the process.  As I add
> additional sites, I would like new records to be created in several
> other specified tables.  I'd like to avoid having to create a new
> record by hand in each table, each time I have a new site.
>
> Can anyone help point me in the right direction?  Thanks.
> 


0
RBear3
5/8/2007 2:45:24 PM
If you are having to create "dummy" records in other tables, you have a 
design flaw.  Perhaps the problem will be solved by changing your join from 
inner to left.  That  will handle missing rows in the "child" table.

<lecoughlin@gmail.com> wrote in message 
news:1178635089.641834.22330@w5g2000hsg.googlegroups.com...
>I have several tables in my database that have the same primary key,
> "site_id."  I am constantly adding new sites to the database, so I am
> trying to figure out a way to streamline the process.  As I add
> additional sites, I would like new records to be created in several
> other specified tables.  I'd like to avoid having to create a new
> record by hand in each table, each time I have a new site.
>
> Can anyone help point me in the right direction?  Thanks.
> 


0
Pat
5/8/2007 6:44:50 PM
I'm not sure I understand.  Right now (for example) I have 2 tables,
one that has information about population, and the other has
information about address.  The primary key for both tables is
"site_id."  If I add a new site_id to the population table, I would
like to automatically create a new record in the address table that
contains the new site_id.

On May 8, 2:44 pm, "Pat Hartman \(MVP\)" <please no e...@aol.com>
wrote:
> If you are having to create "dummy" records in other tables, you have a
> design flaw.  Perhaps the problem will be solved by changing your join from
> inner to left.  That  will handle missing rows in the "child" table.
>
> <lecough...@gmail.com> wrote in message
>
> news:1178635089.641834.22330@w5g2000hsg.googlegroups.com...
>
> >I have several tables in my database that have the same primary key,
> > "site_id."  I am constantly adding new sites to the database, so I am
> > trying to figure out a way to streamline the process.  As I add
> > additional sites, I would like new records to be created in several
> > other specified tables.  I'd like to avoid having to create a new
> > record by hand in each table, each time I have a new site.
>
> > Can anyone help point me in the right direction?  Thanks.


0
lecoughlin
5/8/2007 7:48:10 PM
Why do you have two tables?  It would seem that one table would suffice.

<lecoughlin@gmail.com> wrote in message 
news:1178653689.870146.20350@e51g2000hsg.googlegroups.com...
> I'm not sure I understand.  Right now (for example) I have 2 tables,
> one that has information about population, and the other has
> information about address.  The primary key for both tables is
> "site_id."  If I add a new site_id to the population table, I would
> like to automatically create a new record in the address table that
> contains the new site_id.
>
> On May 8, 2:44 pm, "Pat Hartman \(MVP\)" <please no e...@aol.com>
> wrote:
>> If you are having to create "dummy" records in other tables, you have a
>> design flaw.  Perhaps the problem will be solved by changing your join 
>> from
>> inner to left.  That  will handle missing rows in the "child" table.
>>
>> <lecough...@gmail.com> wrote in message
>>
>> news:1178635089.641834.22330@w5g2000hsg.googlegroups.com...
>>
>> >I have several tables in my database that have the same primary key,
>> > "site_id."  I am constantly adding new sites to the database, so I am
>> > trying to figure out a way to streamline the process.  As I add
>> > additional sites, I would like new records to be created in several
>> > other specified tables.  I'd like to avoid having to create a new
>> > record by hand in each table, each time I have a new site.
>>
>> > Can anyone help point me in the right direction?  Thanks.
>
> 


0
Pat
5/9/2007 8:54:19 PM
Reply:

Similar Artilces:

Off topic: VB6 question
sorry this is way off topic. using _vb6_: i'm trying to add/replace nodes to an xml document via CreateTextNode: xml = "<item>floogle</item>" set newNode = oDom.createTextNode(xml) call dDom.replaceChild( newNode, bloogleNode) It works ok. But I can no longer find the new node in the document via an xpath query. When I save the doc to disk I can see the new node but its been escaped, presumably because it was a text node: <root> <item>wayheyhepahole</item>&lt;item&gt;floogle&lt;/item&gt; </root> I guess my question is: H...

hyperlink open in new window
How do you create a hyperlink and have it open in a new window? refer to http://www.davidbartosik.com/pub2002/pub2002_13.htm -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "sharin" <anonymous@discussions.microsoft.com> wrote in message news:2180BE5B-1783-4F9A-A167-D8E3BF34B3CC@microsoft.com... > How do you create a hyperlink and have it open in a new window? ...

New appointment, nothing happens
Hi, suddenly I'm no longer able to add appointments to my calendar. Nothing happens when trying. I also tried to sync my mobile calendar to it and it seems to sync just fine, but it will not add any new appointments. Any suggestions ? (outlook 2003, winxp pro) Thanks, Jorgen Sorry, just figured out the (possible) cause: I had a microsoft update pending, after innstallation and reboot everything is fine. Jorgen "JGH" <nospam@thanks.org> wrote in message news:eyVH5TpwGHA.1296@TK2MSFTNGP02.phx.gbl... > Hi, suddenly I'm no longer able to add appointments to...

Moving my Outlook files to new computer
How can I move my Outlook 2003 files (Contacts, Calendar, mail, etc) to a new computer? ...

New Project, Different Problem: AutoFilter?
I'm creating a database of outside trainers. Each trainer can handle multiple categories of training. It is essential that the training manager be able to create lists for specific categories as the need arises, as well as being able to do more standard sorts such as Name. Rather than making multiple entries for each Trainer (one for each category, which may reach 10+), I'd like to allow multiple entries in the Category column utilizing a key for each category. It seems that AutoFilter would be the ideal method for creating this db. Well, actually Advanced AutoFilter, since...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

Last Records
I need to query the last records for each date in a table. The table data has three fields - Id (Auto Number), Date and Amount. Each Date field can have one or several records. Example data: 1,7/1/07,5 2,7/1/07,9 3,7/2/07,2 4,7/3/07,4 5,7/3/07,10 6,7/3/07,6 7,7/3/07,3 I need to query the last record (determined by the Id field) for each date. The Id field may or may not be included in the results. The query should return: 7/1/07,9 7/2/07,2 7/3/07,3 How do I do this? Thanks, Scott SELECT TblScott.Date, TblScott.Amount FROM TblScott WHERE TblScott.ID In (SELECT Max(TblScott.ID) FRO...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

Create a new instance of a class that is a reference
Hi all, I am trying to make a class that when you create a 2nd instance the second instance will be a reference to the first. Public Class ClassA Private Shared tableList As Hashtable Public myInt as integer = 0 Public Sub New() MyBase.New() InitializeValues() End Sub Private Function openTable() As Boolean If (tableList Is Nothing) Then tableList = New Hashtable() End If If (tableList.ContainsKey(TABLE)) Then SetReference(Me, tableList(TABLE)) OpenCount += 1 Else ...

Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field service component. Where can I find this information? I need to move RMA into a data warehouse and am having significant trouble identifying the data flows. Thanks, Will You may want to contact I.B.I.S. They wrote the application. -- Charles Allen, MVP "WS" wrote: > The 9 SDK does not reference or define the fields or tables for the Field > service component. Where can I find this information? I need to move RMA > into a data warehouse and am having significant trouble identifying th...

OWA and MX records
I am running in mixed mode and have just completed moving most of my mailboxes to E2K3. OWA access works fine within the domain, either using the IP address or the name of the sever. What I would like to do is test it "for real" without distrupting the 5.5 verison of OWA that is still in use as well as mail flow to the 5.5 server. My ISP currently has pointers (MX and A records) in place that point to my old 5.5 server for mail flow and OWA. Can I ask them to setup another MX record that points to my new E2K3 server to gain access to OWA? Or are you only allowed one MX record ...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Please help with New Database Query
Newbie to New Database Query - Can you change a spreadsheet to a table if so how? I have a lot of spreadsheets that I would like to work with in New Database Query. Can this be done. Any help will be greatly appreciated. In general, if you select a range and give it a name....MS Query will recognize it as a data source: Example for data in Cells A1:Z500, with column headings (EmpID, FName, Lname, etc): Select A1:Z500 In the Names box (just above the Col_A heading) type rngMyData1 Press [Enter] Next, save your workbook. Then...Data>Import External Data>New Database Query When y...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

Cannot add new account to Money 2006
Hi all, I've bought Money 2006 std at last week, and first I've tried to import my Money 2000 file. The Money 2006 said that the file is not importable. OK. I've tried to create new file, which was successfully created, but I cannot add any new account. I've always got the same error message: The operation cannot be performed. Details: Product: Money ID: obres:34 Source: 15.0 Version: 15.0 Symbolic name: errUnknown Message: This operation cannot be performed. Reason Usually caused by a corrupt file. User Operation Run the Money File Repair tool to fix th...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

How 2 sort an autosum total list after adding items 2 autosum item
Am using detail dollar amount list to insert new transactions in the middle of a the detail list, and they in turn get included in the 'autosum' total calc which is located in another item total summary list in the same workbook. If we try to sort the autosum total summary list, the autosum totals seem to loose their connection with the detail autosum calc list. Is there anyway to keep this connection, ie be able to add items into the detail autosum calc lists, and then sort the autosum summary totals ? -- Thanks again for your help. akm (XPpro,SP-2,IE-7,Office2003...

Limiting Number of Records per User
Just wondering if there is a way to limit the number of records a user can take ownership of. For example....leads. A salesperson can only have x amount of leads assigned to them at a given time. Thanks! ------=_NextPart_0001_36201CDB Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi and thank you for your post. Unfortunately there is not a way to limit a user's record ownership. Just to be sure I checked with the SDK team and they don't have a way to do that either. But I would like to encourage you to make a product suggestion along these lines. We are very i...

New computer and office 2007
I ordered a new computer at work and already have office 2007. I want to activate it on the new one and deactivate it on the old one. Has anyone done this or what will I have to do? I did a search on Microsoft and didn't get any hits on it. Thanks, Lee "Neophyte" <wleecoleman@.nospam.ev1.net> wrote in message news:%237S3ME1KJHA.3496@TK2MSFTNGP04.phx.gbl... >I ordered a new computer at work and already have office 2007. I want to >activate it on the new one and deactivate it on the old one. Has anyone >done this or what will I have to do? I did a search on...

RMS CAPN Complainance with Visa & Mastercard new PCI Standard?
Hello, Does anyone if RMS v1.2 and/or other version is CAPN Complainance with Visa and Mastercard new PCI Standard? I am looking to upgrade our PC Charge to the new version for PCI Standard, but I have learn that our RMS v1.2 must be pushing the new fill that PCI standard is requiring "62.23" to PC Charge so they can send it to our processor. ED ...

New Record question 01-17-08
DoCmd.Close acForm, "Invoice Database Launch", acSaveYes DoCmd.OpenForm "Invoice Tracker New Entry", acNormal Application.Forms("Invoice Tracker New Entry").Caption = "CREATE INVOICE" DoCmd.GoToRecord , , acNewRec When I use the above code. It creates a blank record in the datasheet. Is there a way to delete the record if it is blank and keep the record if there are data in it? There is always a new, empty record at the end of a datasheet unless you have set AllowNewRecords to False, or your query is not updateable. -- Arvin Meyer, MCP,...

New version of Office for Mac.
Dear all: Do any of you know when the new version of Microsoft Office for the Macintosh is scheduled to be released ? An approximate date would be sufficient. Thank you in advance, Joseph Chamberlain Hello Joseph, Microsoft has given no clear indication other than "late 2007", so we are all just speculating here. Those who know when Office 2008 is coming out are not allowed to tell us. Those who tell us, don't know. Personally, I would guess at early 2008 -- but you never know how much effort Microsoft will put in to catch the pre-Christmas market. Cheers, Clive Hugg...