Create field from append query based on linked table name

Here's the setup:

Two linked tables called 'PHD' and 'XANS' bring in daily data from two
CSV files.
A union table-query puts the common data in both into the same name
fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a
new table called 'SOLS_MAIN' and I ran an append query called
'SOLS_DATA_APPEND' to append the data in the table-query,
'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for
this was so that I could assign my data a primary key.

Even though I have achieved my goal of merging the data from the two
linked tables with a primary key, I have no way of telling which data
has come from which linked table. Ideally I would like a new field in
'SOLS_MAIN' that contains the name of the linked table that the record
came from i.e 'PHD' or 'XANS'. I need a way of telling which linked
table each record has come from, because I need to run queries to
compare data from both. Is there a way I can create a field
automatically based on the name of the linked table??

Regards,

Tom

0
Tommy
8/17/2007 10:16:13 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1185 Views

Similar Articles

[PageSpeed] 30

You can add a field in the union query like this ---
          ...., Your_current_fields,  "Table_X" AS New_Field
UNION ALL SELECT ....
-- 
KARL DEWEY
Build a little - Test a little


"Tommy" wrote:

> Here's the setup:
> 
> Two linked tables called 'PHD' and 'XANS' bring in daily data from two
> CSV files.
> A union table-query puts the common data in both into the same name
> fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a
> new table called 'SOLS_MAIN' and I ran an append query called
> 'SOLS_DATA_APPEND' to append the data in the table-query,
> 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for
> this was so that I could assign my data a primary key.
> 
> Even though I have achieved my goal of merging the data from the two
> linked tables with a primary key, I have no way of telling which data
> has come from which linked table. Ideally I would like a new field in
> 'SOLS_MAIN' that contains the name of the linked table that the record
> came from i.e 'PHD' or 'XANS'. I need a way of telling which linked
> table each record has come from, because I need to run queries to
> compare data from both. Is there a way I can create a field
> automatically based on the name of the linked table??
> 
> Regards,
> 
> Tom
> 
> 
0
Utf
8/17/2007 3:14:30 PM
Reply:

Similar Artilces:

How To Create UI Automation Test Suites for VC++ 7.2 Application
Hi All Envoirement -: VC++ 7.2 OS -: Win 2000,XP As i can Develop UI Automation Test Suite For .NET Application Plz Anyone tell me,Can I Develop this Tool for VC++7.2 Application ? If yes ,How ? plz suggest me for it. Regards Tarandeep Singh tarandeep@abosoftware.com Fundamentally, this is hard. There are some commercial programs out there that do this, but getting a test suite that works well, particularly in all resolutions, and is fully scriptable, is a nontrivial problem (well, let's put it this way: I would not accept a fixed-price contract to do this, and wou...

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

Results from blank linked cells
I am linking cells from different worksheets in the same workbook, using the copy/paste/link cell method. How can I get a blank space (as opposed to the zero I am presently getting), in the destination, if the source cell is blank. I am linking a input sheet to several forms that must be sent out, but I don't want a form that will have a number of zeros in it. =if(sheet1!a1="","",sheet1!a1) If the linked cell looks empty, show empty, else show the value. Mr. Anolog wrote: > > I am linking cells from different worksheets in the same workbook, using the &g...

String Table (VC6 IDE)
I have strings in English language in the "String Table" of my project (myProject.rc). I'm loading them using: CString msg; msg.LoadString(150); Now, I need to internationalise my app. How can I do that? How can I add support for multiple languages? Which is the best way to do that? >I have strings in English language in the "String Table" of my project >(myProject.rc). I'm loading them using: > >CString msg; >msg.LoadString(150); > >Now, I need to internationalise my app. How can I do that? How can I add >support for multiple lang...

linking a dll
I am trying to use functions from an open-source maths library (fftw) which has been written in C for Unix. In their instructions for Windows use they have pre-compiled the functions. I have now got .lib, .def, ..dll and .exp files for the functions, but am having great difficulty getting them to link. All the above files are in the local directory, and I have added the ..lib to the list in Project->Settings->Link->General. I get a number of error LNK2001: unresolved external symbol _fftw_free error messages. If anyone could point out some obvious errors or omissions, or even ex...

pivot table %
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

How do I TRIM a field in existing fields....?
I didn't realize when I imported some data into my database that there were a bunch of spaces after all the data. I know that I can do a RTRIM the data in a query, but I don't want to have to remember every time I create a query to TRIM it. Do I have to use a query and make a temp table and then delete the data from my original table and then put the data back in from my temp table. I can do this, but wanted to know if there is a better way. Thanks Kelvin Kelvin A standard approach to importing (and "cleaning") data is to import/append to a temporary table, then ...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

lotus approach queries VS access queries.
Hi, We are migrationg from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Conversion Errors Table
Hello, I'm new to working with Access, I just converted an Access 97 databas into Access 2002. It tells me there were errors, and to look at th Conversion Errors Table. But nowhere in the message or in the MS Hel is there anything telling me where to find this table. Can someon help? Thanks Patric -- psha ----------------------------------------------------------------------- pshaw's Profile: http://www.officehelp.in/member.php?userid=493 View this thread: http://www.officehelp.in/showthread.php?t=125029 Posted from - http://www.officehelp.i I'd expect to find it in the new...

Creating a Calendar in Publisher
I have created a calendar page in Publisher. How do you save that design so that each consecutive month will hold the format? On Thu, 26 Jan 2006 17:31:02 +0000, Toni wrote (in article <A49FAC01-7290-48FA-9576-5FB4761A9CD6@microsoft.com>): > I have created a calendar page in Publisher. > How do you save that design so that each consecutive month will hold the > format? You use the correct tool for the job. Publisher isn't it. Serif PagePlus, OTOH, is. I have Publisher 2000 Can't you use SAVE AS and it saves as a Publisher file? If the tabs on the bottom are ...

worksheet labels based on cell results
How can I build a macro to use the contents of several cells in a column to label a corresponding number of worksheets with their contents. Ideally this would also build links to the tabs so that a user could click on a specific cell (in that column) and be redirected to the corresponding worksheet Thanks, Mitch Hi for labeling the tab try something like activesheet.name=activesheet.range("A1").value For the second question try using a Hyperlink (Insert - Hyperlink) -- Regards Frank Kabel Frankfurt, Germany Mitch wrote: > How can I build a macro to use the contents of ...

Help me create sales chart based on state and quantity
We have a production report on excel. It shows the details for our clients. Part of that data includes the state in which the client lives. We are trying to create a chart showing the percentages of each state( so we know where the most deals are closed) Any suggestions? Hello mr_merchant_man, this sounds like a job for a pivot table, using Average as the data calculation operator. Or, depending on your version of Excel, you can use AVERAGEIFS (in Excel 2007) or calculate an averate with a combination of SUMIF divided by COUNTIF. To be more specific, it would help to s...

Move a particular named sheet to the end.
Using macros, how can I move a sheet called TOTAL to become the very last sheet within a workbook? Your assistance will be appreciated. -- Thank U and Regards Ann Try this: Worksheets("Total").Move after:=Worksheets(Worksheets.Count) "Ann" wrote: > Using macros, how can I move a sheet called TOTAL to become the very last > sheet within a workbook? > > Your assistance will be appreciated. > > -- > Thank U and Regards > > Ann > Barb, Works a treat, thank you for your assiatance. -- Thank U and Regards Ann "Barb Reinhar...

Appending XML to an existing XML file
Hey all, I've read a few articles about speed and XML processing - so I just want to make sure that I'm using the right strategy for what I want to achieve. I have an XML file that I'm appending to every time a user submits their information. Right now I'm using XMLDocument (Load and Save) in conjuncture with XmlElement objects. Is this the right approach or is there a faster approach? Thanks, Novice PS Here is a simplified version of what I'm doing: XmlDocument xdoc = new XmlDocument(); xdoc.Load("results.xml"); XmlNode node = xdoc.SelectSingleNode("...

creating a formul
Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George I bet you want to use =vlookup(). Debra Dalgleish has some nice instructions at: http://www.contextures.com/xlFunctions02.html George A. Yorks wrote: > > Trying to create a formula to do the following: > Sheet 1 column A a list of personal names a1-a10 > ...

Using the classes created with xsd.exe
I have created classes from several xsd files. These files create about 150 classes and spot checking them they do represent types in the xsd files. the question is how do I use these files. How do I load data into them and create xml from them. Is there some articles about this subject. Thank you, -- Jerry Hi Jerry, As for the classes you've generated, are they normal classes or dataset classes? As for the normal classes you generated through xsd.exe, you can use XML serialization to convert those class instances into XML content or deserialize the XML content back into objec...

How do I create a summary page from multiple worksheets
Trying to roll-up information from multiple worksheets within the same workbook to a summary page. These worksheets are copies of each other. For example: each worksheet has a column labeled "defect number". The users can record multiple defect numbers within a cell (e.g. 897, 992, 1001) So sheet1, row1 = 897, 990 sheet2, row1 = 992 sheet3, row1 = 995, 1001, 1012 sheet4, row1 = empty How do I (or can I) rollup this information to a summary page where sheet5 is the summary worksheet and row1 = 897, 990, 992, 995, 1001, 1012. Here's what I have so far [=Sta...

Report repeats a field
Example: John Smith 2,14 5,27 3,18 John Smith 3,17 4,27 7,34 John Smith 1,22 6,57 8,92 I want that the report shows a name(John Smith) only one time like this: John Smith 2,14 5,27 3,18 3,17 4,27 7,34 1,22 6,57 8,92 The report get the informations from a Query that get the information from a table. One way to do this would be to set the "name" control's Hide Duplicates property to Yes. Another way to do this would be to use Sorting & Grouping, then Gro...

Pop-Up Subform not linking to Main Form
I have a subform - frmLawEnforcement - that is accessed on the main form - frmCorporateSecurity - by clicking on a command button from the main form. The two forms are linked by the CaseIDNumber field. If I place the subform as just an entry from with the main form, the information shows as linking by the CaseIDNumber. When I enter the information into the subform using the command button, the information does not link to the case number. I am not sure what I could be doing wrong. I am an intermediate Access user but am fairly limited on VBA. ABradley, It would help...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

File Naming for Picture Order
I want my photos within a folder to appear in a certain order and want them in numberical order; however, when I put them in numerical order and get past 9 (into 2 digit numbers) the order gets all whacky. bjackson wrote: > I want my photos within a folder to appear in a certain order and > want them in numberical order; however, when I put them in numerical > order and get past 9 (into 2 digit numbers) the order gets all whacky. ======================= Instead of... 1, 2, 3,.....10, 11, 12... Try this... 0001, 0002, 0003,.....0010, 0011, 0012 If you are batch renumbering the f...