How do I insert the name of a table into my "master" table?

So I'm doing an append query, appending data from about 300 separate
tables into one master table. I want to include the name of the
originating table name as a field for each record that I append into
the master table.

Does anyone out there have any ideas/suggestions on how I would go
about doing this, please? Responses are very much appreciated, thanks
in advance!
0
Adrienne
1/4/2008 3:36:59 PM
access 16762 articles. 3 followers. Follow

4 Replies
831 Views

Similar Articles

[PageSpeed] 6

Adrienne

Are you trying to set up a single append query that appends from ALL 300 
tables at once?  Are you confident that each of the 300 is "well-formed" and 
matches up properly to the destination?

If creating one append query for one table and then modifying it 299 times 
is not an option (say, if you needed to do this repeatedly), you will need 
to look into creating a procedure that steps through the tables, grabbing 
the tablename, then dynamically modifies a SQL statement handling the append 
(INSERT) operation.  Designing, developing and testing this procedure may 
take you longer than 299 modifications.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Adrienne" <afargas@gmail.com> wrote in message 
news:1e4d01c1-5416-4c18-8b7c-05ba1ec77ff5@f10g2000hsf.googlegroups.com...
> So I'm doing an append query, appending data from about 300 separate
> tables into one master table. I want to include the name of the
> originating table name as a field for each record that I append into
> the master table.
>
> Does anyone out there have any ideas/suggestions on how I would go
> about doing this, please? Responses are very much appreciated, thanks
> in advance! 


0
Jeff
1/4/2008 5:33:11 PM
Jeff,

The question of how many tables I'm inserting isn't what I'm concerned
with. I'm trying to track the table they came from in the master table
by having a field in the master table for the filename of the file of
origin.

In other words, if one of my original tables is
"serverXYZ_2007_09_30", I want each record that originally came from
that table to have a field with "serverXYZ_2007_09_30" in that field.
Does that make sense?

I just need some function or expression to auto-insert the filename
into that field when I do an append query.
0
Adrienne
1/4/2008 6:38:34 PM
Adrienne

Your conceptualization of this as a "just" may be ... insufficient.  This 
isn't something that falls into the "just" category.

Again, let me ask.  Are you doing this one time?  (in that case, build a 
query and modify it for each table -- you can add in a "calculated" field in 
your query into which you will enter the table-name).

Or is this something that you will need to use over and over again.  In that 
case, re-read my previous response -- you will need to engage in coding.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Adrienne" <afargas@gmail.com> wrote in message 
news:81815c34-7f4e-4282-9fbf-54808ddc4209@x69g2000hsx.googlegroups.com...
> Jeff,
>
> The question of how many tables I'm inserting isn't what I'm concerned
> with. I'm trying to track the table they came from in the master table
> by having a field in the master table for the filename of the file of
> origin.
>
> In other words, if one of my original tables is
> "serverXYZ_2007_09_30", I want each record that originally came from
> that table to have a field with "serverXYZ_2007_09_30" in that field.
> Does that make sense?
>
> I just need some function or expression to auto-insert the filename
> into that field when I do an append query. 


0
Jeff
1/4/2008 7:10:20 PM
On Fri, 4 Jan 2008 10:38:34 -0800 (PST), Adrienne <afargas@gmail.com> wrote:

>In other words, if one of my original tables is
>"serverXYZ_2007_09_30", I want each record that originally came from
>that table to have a field with "serverXYZ_2007_09_30" in that field.
>Does that make sense?

Just to add to Jeff's good advice: if it's a one-shot deal you can just type

TableName: "serverXYZ_2007_09_30"

in a vacant Field cell in the append query.

             John W. Vinson [MVP]
0
John
1/4/2008 8:52:37 PM
Reply:

Similar Artilces:

From temporary table to master table and then join this in junction table
Hello, I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets. I end up with these data: [import_tbl_contract] contract_number contract_title start_date end_date [import_tbl_products] product_number product_text price I now append these data to the following two tables: [tbl_contract] contract_id (PK) contract_title start_date end_date e.g. 1; Syringes and needles; 01-01-2010; 31-12-2010 [tbl_products] product_id ...

Jaeger LeCoultre Master Compressor Geographic Stainless Steel Mens Watch 1718470
Jaeger LeCoultre Master Compressor Geographic Stainless Steel Mens Watch 1718470 Discount Watches Site : http://www.hotwatch.org/ Jaeger LeCoultre Master Compressor Geographic Stainless Steel Mens Watch 1718470 Link : http://jaeger-lecoultre.hotwatch.org/jaeger-le-coultre-geographic-1718470.html Jaeger LeCoultre Master Compressor Geographic Stainless Steel Mens Watch 1718470 Information : Brand : Jaeger LeCoultre Watches ( http://jaeger-lecoultre.hotwatch.org/ ) Gender : Mens Code : jaeger-le-coultre-geographic-1718470 Item Variations : Movement : Automat...

Save as Excel from Master Project
I am trying to export from a master project with about 8 sub- projects. When I try and export from the master to excel, it does not include certain text fields. Why? Any work arounds? I need the text field (name) to use the excel sheet. How are you exporting? Data lives in the sub-projects, not the master. However, try expanding all sub-projects and try again Not all custom fields roll up to the sub-project tasks, so their data is not available in the master. For this and other reasons (including no risk of file corruption) I usually create new masters every week but no...

How do you create MASTER PAGE?
I am working in publisher 2000. I don't know how to create my master page and insert the page numbers that I need to make the book. Please email me with your reply. Thank you View, Background (ctrl+m). Create a text box on the background page. With your cursor in the text box, insert, page numbers. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "ministry4thesoul" <ministry4thesoul@discussions.microsoft.com> wrote in message news:AC579C58-5F75-42C7-860C-3C78068B5358@microsoft.com... >I am working in publish...

updating master workbook
I have created a job workbook for several colleagues. Each person's workbook is the same except for pull-down menus that include only their clients' information. If I make an update to the master job workbook, how can I make it also take effect on theirs? ...

Getting Master Page to repeat
Hi: This is embarassing-was answered before but I can't find the post. I am trying to import a 148 page word document into a two-page book format. But when I create the Master Page and import the file, it doesn't repeat the margins even when I click "apply to page range". When the question was answered before I did have one successful import, but could not for the life of me figure out HOW! I must be "challenged!" -- MargeHD You cannot put 148 pages on the master pages... Setup your page as a booklet, in the Arrange menu set your margins. Create a text bo...

Handout master editing
Is there a way to resize the frames in the handout master to increase the size of the slide images so that they take up more of the page? -- Art No. You can use File | Send to Word and resize them in Word. Or you can use a handout add-in. There are two that I know of. http://billdilworth.mvps.org/SlideIntoWord.htm http://skp.mvps.org/how/ -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx ...

Building a Master To Do List
To start, I do not work in excel on a regular basis. I am trying to figure out how to build a Master To Do List. I manage a bunch on projects at once and I'm trying to figure out how to use something like excel to stay on top of them all. I have created an excel file that contains something like 7 sheets. The first sheet is the Master To Do List, the other sheets are all individual projects. Each sheet has these heading across the top, in R1: Done, Date In, Category,Freq.,Action Item, and Date Due. I trying to figure out a how to go one way or the other, or both ways if possibl...

Master behavior
Is it possible to define the Master's behavior on the fly. I mean can I delegate a method to the double-click event of a master ? Thank you very much in advance. You can normally, but not (I believe) if you are using the Drawing Control. "genc ymeri" <genc_ymeri@hotmail.com> wrote in message news:%234w301JAEHA.4060@TK2MSFTNGP10.phx.gbl... > Is it possible to define the Master's behavior on the fly. I mean can I > delegate a method to the double-click event of a master ? > > > > Thank you very much in advance. > > Yes, I'm using a Vi...

Powerpoint Master Bullets Problem
I'm using Powerpoint 2003 and trying to take an old presentation and re-brand it with a new template. I have a saved template with a master containing the format and bullet points that I want. So I select all slides in the old presentation, copy, and paste into my new template. This was working for me earlier with other presentations, but now using a different set of presentations, the bullet points are not adhering to the master slide. They are just staying the same as the old one. Can someone help me figure out why the bullets won't change to the master slide t...

Importing Word document into existing Master
I've created a Master document in Publisher with special footers. When I import a Word file, Publisher creates a new file rather than inserting the Word file into the existing Master. Can you help? Create a text box, select the text box, right-click, click change text, text file, browse to the Word file. Publisher is a desktop publishing application. Long documents are best done in Word. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bronson" <Bronson@discussions.microsoft.com> wrote in message news:68DCA5...

Master Page between Publisher files
Is there any way to save a master page in Publisher 2003 and use it in multiple files/documents? I have over 100 files using the same master page layout but I do not want to create a 100 page file/document. Is there a way to save the master page and import it or something of that nature? Thanks!! Why not create your "master page" and save it. When you need to create a new document, open the saved file, do a Save As with a new file name. New document has been created with the original master page. Or you could also save the "master page" as a Publisher Template u...

Master/Child
I may be slow, but something keeps happening to the master/child properties on my mainform/subforms. How do I get to them? Thanks In the Master form choose the subform. Note: choose the subform not the content within. To tell the subform has been chosen it should have the small boxes for sizing. Then right click and go to properties of the subform. In the properties go to "All" or "Data" tab and you should see a link master and child property. go to the box with ... in it and open to set relationships. Golfinray wrote: >I may be slow, but something keeps happening...

Master Transaction Number & Trx Source Number
In SOP what this numbers mean Master Transaction Number Trx Source Number What is the initial tables for this number. Thanks a lot. V, This really is a developer question that may be better posted/answered on the mbspartner.developer ng. That is for GP and SBF developer questions. Matt "Vitali V" <vitvov@dynamo-ny.com> wrote in message news:uIVuOK1sFHA.4028@TK2MSFTNGP10.phx.gbl... > In SOP what this numbers mean > Master Transaction Number > Trx Source Number > > What is the initial tables for this number. > > > Thanks a lot. > > Tha...

Applying Master Page in Publisher
I've created a master page (for a web page design) as instructed. When I add a new page and do "apply master", it doesn't work. Argh, can anyone help? Do not use the Master Page feature in a web publication. It does not work consistently and should be reserved for print publications. If you have design elements that will be common to each page, then when you add a page choose to duplicate a page, and then edit the parts that differ. If you have more questions about Publisher webs, then post in the web group and we will try to help you there: news://msnews.microsoft.com...

Getting shape instances to update when master's dimensions are changed
I have a shape master that is a rectange with some text inside it. When I edit it to make it longer (to fit more text in it), the instances of the master on the page do not update. Can anyone help me with this please? Many thanks. You might be editing the wrong master. Stencil files have masters, you drag these to your drawing. Visio creates a local master and uses this for your shapes. If you edit a stencil shape, it will not change the local master, or instances of the shape. If you change the local master, it will update the instances. Use drawing explorer to view your local masters. ...

What is SVC00501- Delta Item Master used for?
I ran across this table because I did a search on for all tables using the field "itemtype". I have looked through the sdk for field service and don't find any reference to this table. I do see that there is information in the table and it gets updated periodically. But I don't know what updates it or what the table's used for. Any clues? Thanks DavidF This table stores additional item information used by the Field Service application. The table is populated/updated by the tr_SVC_IV00101_U and tr_SVC_IV00101_D triggers associated to the Item Master t...

Account number in Vendor Master
I am trying to create a smarlist tom list all vendors from different companies. The only way I can identity the companies is thru a user defined field in account master. How can I get the AP account number from the vendor master? I tried linking vendor master to vendor accounts using vendor ID and get the account index from vendor accounts to link to Account master to get the account number. Somehow it work for some vendors. Is the account index unique for different entities? What is the easiest way to get the account number from the vendor master? We are using GP 9.0 Thanks If the ...

Account Master tables
On the GP Account card, there is a checkbox to "Allow Account Entry". Does anyone know what SQL table that is stored in? The table name is dbo.GL00100, the particular column in question is GL00100.ACCTENTR Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "VGrinam" wrote: > On the GP Account card, there is a checkbox to "Allow Account Entry". Does > anyone know what SQL table that is stored in? &g...

master document and endnotes
I have a Word 2007 document with nine separate chapters each with its own set of endnotes. I wish to form these chapters into one document with each set of endnotes at the end but retaining their original numbers; that is similar to a book. I used to know how to do this but have forgotten how -- bobnich If each chapter is a separate section (and only a single section), you can tell Word to put the notes at the end of the section rather than at the end of the book, restarting the numbers in each section. If you want them all collected at the end of the book, then you just need ...

Printing Multiple Workbooks through Master Document?
Word has a facility to pull together multiple documents into one "master" document. I have 120 separate workbooks that need to be grouped and printed in specific orders. Unfortunately that order changes monthly. In order to print easily does Excel have master document capabilities or would a macro openening and closing specific files from an input source be better? I've tried linking and inserting the print ranges into a workbook but this has not worked. Suggestions? Hi ScottP33 If you want to have control of the order you can fill in the names of the workbooks from a d...

Different Master Category Lists?
Running OL 2003 Pro SP1 under WinXP Pro. I have several different .pst files I keep open at once, and would like to have different, non-overlapping Master Category Lists for each .pst file. This is because I have created a lot of categories that are specific to each ..pst file, and it would make each .pst file's category list a lot less cumbersome to wade through if it didn't have all those categories from the other .pst's. Is there any way I can create separate, non-overlapping category lists for each .pst file? The Categories are stored in the registry under the Curre...

Liqour Store Master item file
Guys, Has anybody ever heard of anything like a master item profile sheet for liquor stores, that I can import into RMS? Are there any services out there that gather this kind of info? Any vendors, manufacturers, associations...? I understand there's no item list that has everything on it, but the more comprehensive the better. My idea is to input all the item profile data (or as much of it as possible) into the system, so the customer doesn't have to do all that tedious labor. Any thoughts? Thanks. BTW, Root, if you're reading this, thanks alot for the detailed explanatio...

master sheet hidden after choosing a hyperlink
hi there after i selected a hyperlink to go to another file from the master worksheet,the latter is hidden. if i closed the hyperlinked file, i'm unable to go to my master worksheet. i'll have to unhide the master worksheet first before i closed my hyperlinked file. i have this problem only recently with the master worksheet after some modification done. i only chnage some texts and graphical attachments. nothing is done to alter any formats of the worksheet. thanks Try this: make an insignificant change to the master worksheet. Undo it. (Now excel thinks your workbook need...

Reference constant cells in different files from a master workbook
We need to have master spreadsheets that summarize value from different sets of identical excel spreadsheets. We have a multi-tab template spreadsheet that gets filled in with the data for each shipment and saved. The layout is exactly the same for each shipment and only item serial numbers change. We then generate another summary spreadsheet report periodically that consolidates the serial items and numbers from the individual shipments. Right now we are just pasting the serial numbers from the individual shipment spreadsheets into the summary spreadsheet. I know I can refe...