How to Populate a Table based on Entries in Another

I have 2 tables, TBL - Claims & TBL - Transactions.  This is a one to many 
relationship, 1 Claim has many transactions associated with it.  The issue 
arises in the fact that I receive the data feed of the transactions, and need 
to populate certain fields into the Claims table.  The Claims table contains 
a unique primary key, Claim Number.  This number is a field on the data for 
the transaction.  What I am thinking I need to do is a find Duplicate query 
to return just each unique claim number and then append to claims table.

Any info is greatly appreciated.

Ed
0
Utf
1/12/2010 2:21:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
730 Views

Similar Articles

[PageSpeed] 47

Ed wrote:
> I have 2 tables, TBL - Claims & TBL - Transactions.  This is a one to
> many relationship, 1 Claim has many transactions associated with it.
> The issue arises in the fact that I receive the data feed of the
> transactions, and need to populate certain fields into the Claims
> table.

Why? The data already exists in the Transactions, doesn't it? Why does
it need to be in two places?

> The Claims table contains a unique primary key, Claim Number.
> This number is a field on the data for the transaction.  What I am
> thinking I need to do is a find Duplicate query to return just each
> unique claim number and then append to claims table.
>
There are several questions about updating a field in one table using a
field in another table just this week.  For example, there was a message
posted at 3PM yesterday with the subject "Update the values in a table
from another table". Why don't you take a look at it. :-)

-- 
HTH,
Bob Barrows


0
Bob
1/12/2010 2:31:54 PM
Hi Ed,
You can execute a SQL query in your application to check if the keys being 
inserted already exist. For example:
SELECT COUNT(*) FROM CLAIMS WHERE CLAIMNUMBER='XXX'
If this returns a value >0, it means there is already a duplicated key 
existed and you do not insert that number; otherwise insert it.

Best regards,
Charles Wang

0
changliw
1/13/2010 9:37:16 AM
Reply:

Similar Artilces:

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

Return a date range based on today's date
I need to build a query (to be used for eternity) to return records for dates between 01 July (the year prior) and 30 Jun (the current year) based on the date when the query is run. Is this possible? Take a look at the DateSerial() function in Access HELP. It should provide you the mechanism for doing what you're describing. 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 o...

Format Text in spreadsheet based on text?
I have a really bad spreadsheet that I am unable to sort or create a list out of without doing a bunch of manual editing so I was thinking that if I could highlight certain text with a different font, color, bold or whatever that would be good enough so I can visually make out the data I need and ignore the rest. Any way of doing that which is simple, quick and a few clicks away? James, You can use conditional formatting to change cell fill colors, font attributes and borders based on the contents of the cell (or other cells, such as in the same row). You have three conditios you can s...

transfer data from one excel sheet to another
Hello, We currently receive analysis data from a contractor on an excel spreadsheet that contains more information than we need. I would also like to rearrange the data into another excel spreadsheet that is easier for me to look at the data. We reenter the data into a paradox database( Yes, it is archaic) that is very difficult to change the format. Is there a way to automatically rearrange and transfer the data to another excel spreadsheet via a macro, xml, etc? My experience with excel is limited and I only know basic functions. Thank You. -- Exceloficio ------------------------------...

POP Table Help
Hello, Great Plains 8.0 on SQL2000 with POP module. I'm trying to run a report to show what purchase orders were issued last month and how much they were for at the line item detail. I can find POP10100 and POP10110. However, it seems the 'QTYORDER' field is really the quantity remaining, not the quantity originally ordered. Also, the unit cost values drop to 0 once the item is received. How can I find a list of the purchase orders issued without regard to whether or not they've been received? -Trevor So, from the silence can I assume this is not possible? Or, ...

Switching from dialog based app to SDI
Hi. I'm new to MFC. I've been programming a dialog based application with VC 6.0. The application basically does the following: - Connect via RS232 to an external device - Send 16 bit values (8 Bit Command, 8 Bit data) to the device to write/read register values of that device (only one register at a time) - There are command bytes to read and write each register - Provide the user with an interface to enter the values to write to the registers/or read the register, when a specific button is pressed I'm supposed to write similar programs now to control other devices. The communi...

Transition Navigation Keys in Pivot Tables
In Excel 2003, I have set Tools > Options > Transition > Transition Navigation Keys to "on". The shortcut keys for highlighting a contiguous range of data work fine EXCEPT for when I am in the pivot table wizard. If I have already created a pivot table, and I decide to change the range of my data, and I hit the "Back" button in the wizard to change the data range that the pivot table draws from, I cannot use the arrow key shortcuts (although I can use the mouse.) When I am not in the pivot table wizard, the shortcut navigation keys work correctly. Does anyone e...

pivot table version issue
A client created a pivot table in Excel 2003. In 2003, the filter dropdown arrows are visible and working. Anyone that opens the file in Excel 2000 does not see the dropdown filters. When you click on the pivot table, Excel does recognize it as a pivot table since the ptable toolbar is active and I am able to access the layout, etc. I copied the data sheet to a new file (using Excel 2003 - different PC), recreated the pivot table and now users with Excel 2000 can see the dropdowns. It is either a setting on the client's PC or a setting within the pivot table itself - but I can't...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

Table lookup and more...
Somewhat of a newbie with excel and here is my problem, will be hard to explain. I need to look to see if a value is in a table and if it is, set a cell in another worksheet equal to a value that is in the same row but in a different (absolut) column as the search term. I have attached a simple example of what i am trying to do. I of course typed in what the results should be. Thank you for any help. Attachment filename: excel help.xls Download attachment: http://www.excelforum.com/attachment.php?postid=428842 --- Message posted from http://www.ExcelForum.com/...

how do i arrange survey data best for a pivot table?
There are pivot table instructions and links on Jon Peltier's web site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Mormonchick wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Formula to select a # from a table
I have two drop downs in my spreadsheet one for the product line(IP, DP, Etc) and one for the the period (001/2010, 002/2010, Total Year, Etc. Below is the table. 001/2010 002/2010 003/2010 IP - 2,355 1,178 WWP - 722 361 DP - 2,115 1,057 CDP - 262 131 MP - - - MISC - 365 182 I need a formula so that when IP and 002/2010 are selected in the drop down 2355 shows up in the cell with the formula. Look in the help index for MATCH and then INDEX. Put em together. -- Don Guillett Microsoft MVP Excel SalesA...

Formula: values in one column based on corresponding data
My goal is simply this - I want to add a series of values (hours and minutes) worked on specific projects within one months time. Now, the month/day/year is listed in a separate column. Thus, one column lists all the hours and minutes used to complete a project and now I want to add up those values for a specific month's time. The reason is so I can explain how much time was spent doing a specific task for various cases. I believe the formula starts with "=sumif(" but after that I simply do not know. SUMIF won't suffice as you have multiple conditions to satisfy fo...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...

How to open another user's folder list?
Hello, I have Outlook 2002 SP3 and Windows XP SP2. In my Folder List I have my mailbox expanded showing all my folders (inbox, calendar, contacts, etc.). I also have the mailbox for our general company email account (info@) that receives the emails submitted from our website. But when I click on the + sign next to it to expand it, I get a message saying "Unable to expand the folder. The set of folders could not be opened. The operation failed." This company email is set up as it's own user on our network, and I am set as it's delegate with full control over all it's ...

Pivot Table: Misordered date Grouping
I'm having a problem where the grouping is coming out wrong. I have dates ranging from October 2003 to June 2004. When I group by month, it gives me a January - November groupings vs. a October - June groupings. In other words, the October and November months should be in the first two columns given their dates come first. Thanks, Mark G. Hi, Can you post a piece of your data/spreadsheet? I can't get it to do what you describe. jeff >-----Original Message----- >I'm having a problem where the grouping is coming out >wrong. I have dates ranging from October 2...

Populating a field
I want to populate a description field based on a par number that is entered in the field before it. In other owrds, you enter a part number and presto, in the next field, the description is entered for you. Can you help?? Thanx Tom On Thu, 6 Dec 2007 08:48:01 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I want to populate a description field based on a par number that is entered >in the field before it. In other owrds, you enter a part number and presto, >in the next field, the description is entered for you. > >Can you help?? > >Thanx >Tom If yo...

CRM Knowledge Base Articles
Can you move the CRM knowledge base articles from one CRM database to another? The old CRM database can only be accessed by SQL Management Studio. ...

Populate Textbox
Hi, I have a table with part numbers and locations. There are multiple locations per part number. I would like to populate a listbox with a filtered list of the locations for the part number shown on my form. I'm not very good at vba and have been looking all over for code, but can't find anything suitable. Thanks You have multiple locations per part number. So first, let's get your form set up correctly. When you have a one-to-many format (one salesman, many products or one part number, many locations) you need a form and subform. The Mainform would be for...

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

Pivot table subtotal select
After recently upgrading my MS Office I cannot select and highlight a subtotal row within a pivot table as I was able to before. By placing my cursor at the left side edge of a subtotal row (cursor changes to a right-hand pointing arrow) I was able to format all subtotal rows within the pivot table with a different color, bold etc. Can you tell me what option I need to select w/in Excel to regain this ability? Thanks Kevin Allen From the PivotTable toolbar, choose PivotTable>Select, and check that Enable Selection is on. Kevin Allen wrote: > After recently upgrading my MS Off...

Help Please: Retrieving the first populated cell value from a specific excel spreadsheet column
I need help with retrieving the first populated cell value from a specific excel spreadsheet column. This value will then be used for later parts in an broader script to mark the entry as used. The 3rd worksheet contains 3 columns. I need this code to go through all the cells in the 2nd column, in order from top to bottom, and have it return the first value found. In this example, it should return 55553. Phone Free Used 55550 55550 55551 55551 55552 55552 55553 55553 55554 55554 55555 55555 ...........

Fixed Assets ACRS tables
Is there a way to update/change ACRS tables? Can new ones be added? Our client here in sydney is reporting to their US headquarters, and they are using depreciation rates table from Gap 20. The rates are not on the ACRS tables of GP. Thanks. ...