Update Query - 118817

I have three tables:  tblVendors, tblForecast and tblTemp

tblTemp 
VENDOR_NAME, SERVICESUITE_NAME, Q1, Q2, Q3, Q4, REGION_NAME

tblForecast
VENDOR_ID, FHIST, SERVICESUITE_ID, REGION_NAME, Q1, Q2, Q3, Q4

tblVendors
VENDOR_ID, VENDOR_NAME, VENDOR_DESC, REGION_NAME

I am trying to populate (append) records into the tblForecast from tblTemp 
by matching VENDOR_NAME in tblVendors with the VENDOR_NAME in tblTemp.  If a 
match occurs I want to write the entire tblTemp record to the tblForecast and 
add the VENDOR_ID from tblVendors to tblForecast. 

PK in tblVendors = [VENDOR_ID]
PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]


0
Utf
11/26/2007 3:31:09 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
866 Views

Similar Articles

[PageSpeed] 51

Create a new query

Select tblTemp and tblVendors from the table list and add them to the query 
grid.

Join these two tables on the Vendor_Name field.

Select the fields you want to import into tblForcast

Run the select query.  This will tell you where your matches are, if you 
have any.

Change the query type to an Append query and select tblForcast from the list 
of tables in the combo box.

Save the query if you will need to do this again in the future.

When are you going to delete the records from tblTemp?

HTH
Dale
-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"THINKINGWAY" wrote:

> I have three tables:  tblVendors, tblForecast and tblTemp
> 
> tblTemp 
> VENDOR_NAME, SERVICESUITE_NAME, Q1, Q2, Q3, Q4, REGION_NAME
> 
> tblForecast
> VENDOR_ID, FHIST, SERVICESUITE_ID, REGION_NAME, Q1, Q2, Q3, Q4
> 
> tblVendors
> VENDOR_ID, VENDOR_NAME, VENDOR_DESC, REGION_NAME
> 
> I am trying to populate (append) records into the tblForecast from tblTemp 
> by matching VENDOR_NAME in tblVendors with the VENDOR_NAME in tblTemp.  If a 
> match occurs I want to write the entire tblTemp record to the tblForecast and 
> add the VENDOR_ID from tblVendors to tblForecast. 
> 
> PK in tblVendors = [VENDOR_ID]
> PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]
> 
> 
0
Utf
11/26/2007 3:54:01 PM
Thank you for your help.   The issue that I have encountered at this point is 
that some of the tblTemp records are SELECTED twice in the query results.   
tblTemp will be deleted after each successful import executes.

"Dale Fye" wrote:

> Create a new query
> 
> Select tblTemp and tblVendors from the table list and add them to the query 
> grid.
> 
> Join these two tables on the Vendor_Name field.
> 
> Select the fields you want to import into tblForcast
> 
> Run the select query.  This will tell you where your matches are, if you 
> have any.
> 
> Change the query type to an Append query and select tblForcast from the list 
> of tables in the combo box.
> 
> Save the query if you will need to do this again in the future.
> 
> When are you going to delete the records from tblTemp?
> 
> HTH
> Dale
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "THINKINGWAY" wrote:
> 
> > I have three tables:  tblVendors, tblForecast and tblTemp
> > 
> > tblTemp 
> > VENDOR_NAME, SERVICESUITE_NAME, Q1, Q2, Q3, Q4, REGION_NAME
> > 
> > tblForecast
> > VENDOR_ID, FHIST, SERVICESUITE_ID, REGION_NAME, Q1, Q2, Q3, Q4
> > 
> > tblVendors
> > VENDOR_ID, VENDOR_NAME, VENDOR_DESC, REGION_NAME
> > 
> > I am trying to populate (append) records into the tblForecast from tblTemp 
> > by matching VENDOR_NAME in tblVendors with the VENDOR_NAME in tblTemp.  If a 
> > match occurs I want to write the entire tblTemp record to the tblForecast and 
> > add the VENDOR_ID from tblVendors to tblForecast. 
> > 
> > PK in tblVendors = [VENDOR_ID]
> > PKin tblForecast = COMPOSITE key of [VENDOR_ID,FHIST, SERVICESUITE-ID]
> > 
> > 
0
Utf
11/26/2007 6:06:01 PM
Reply:

Similar Artilces:

Cross-Tab Query
I have a cross-tab query that works as desired. I can get it to post to a report. My problem is the report (legal - landscape mode) is limited to 12 items. How can I get the report to go beyond the 12 items? The data in the cross-tab query will take multiple pages to print all the data. Is there a way to get the report to pick up with the next "12" items if the cross-tab has more than 12 items? I can post a stripped-down version of the database if needed. Creating a dynamic cross-tab report is by no means easy, but it can be done. Here's Microsoft's take on it: ...

Linking Queries
Can anyone help? I have a system where employees register property with us. They then report back to us when they sell the property. Registered properties are held in one table and sold properties are in another, where the common field is the employee. I want to create a query where it will show the employee and the details of the registerly and the sale. I cant seem to get the details of the registry and the sale to assign to the correct employee. Thanks If you were limited to working with a spreadsheet, you might come up with a design that puts properties in one status on one s...

How to create DSN Less connection on pass through query?
I have DSN Less connection code as follows for linked tables which the code is on the switchboard form: Private Sub Form_Open(Cancel As Integer) ' Minimize the database window and initialize the form. ' Move to the switchboard page that is marked as the default. Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' " Me.FilterOn = True Dim ServerName As String Dim UserName As String Dim Password As String ServerName = "CPSHOUDB15" UserName = "********" Password = "********" 'Attach the linked database table...

The right way to update a tool bar
Hello newsgroup, I searched pretty hard, but I couldn't find some valuable piece of information about updating tool bars. I have an SDI application with a tool bar. If the user retrieves an image (e.g. by loading from file) I want to enable some buttons in this tool bar. I wrote an OnUpdate... handler for the button, that will check if an image is available (or if the document is "empty"). But I can't convince the tool bar to update (I even tried to invalidate the CToolBar window that displays the tool bar, but this doesn't trigger my OnUpdate... handler). Curiously enou...

UNION query and Data type mismatch in criteria expression
I did some searches on this problem before trying to post here to the newsgroup, but what little information I found did not seem to help. Here is the problem. I have a UNION query that joins 2 queries into 1 dataset. Each sub-query works fine individually, but when I place them into a UNION query it gives the error message "Data Type mismatch in criteria expression" I first double-checked to make sure any JOIN fields were of the same data type - which they are (everything is in TEXT format). Also, the information I found suggested there might be problems when an empty field...

Messy update no longer accepts serial number!
Similar problem here, only perhaps even more of a puzzle. I've just downloaded and installed the update. Upon starting Word, I'm asked to enter my name and serial number, which I do, only to have it rejected. I've very, very carefully re-entered, re-checked and re-tried the serial number that was with 10.0, but now I'm told it is incorrect. Looks like an uninstall 10.1.6 and a re-install of 10.0. Waste of time and energy (in other words, a waste of money). In article <ae9d8f91.0410260224.276db7d6@posting.google.com>, david.neale@pandora.be (David) wrote: > Similar p...

update a table based on a sum query
Hi All, I have seen the zillions of answers to this that state "never add a total into the table.." But there are cases, such as mine when it is prefered. I want to update a project table with the final invoice cost. I want it entered as a hard figure so that I can then export the table for accounting purposes. Additionally, once an invoice has been generated, there isn't any need for the total to ever, ever change! In fact it shouldn't. So the question is.... is there a way to do this? To update a table with a value from a sum query? Thanks for any help. ...

How to use different type Query for Excel PivotTable? (ODBC)
How to use two different type of Database Query for Excel PivotTable? (ODBC) My PivotTable gets data from Microsoft Query by ODBC. But I can not get two different type database for ONE PivotTable. For example, one table is *.dbf, the other is *.btr, Can i use these different table for ONE PivotTable? I know Crystal Report can do it. How about Excel PivotTable? try Vb, ADO programs or SQL.Request function (Excel ODBC Add-In) with "SELECT....IN... UNION SELECT....IN... " statement. I do such a queries into another file and make PivotTs basing on it ...

List Box to create a Report based on crosstab query
I have a form with a list box to select a value, and a button that is pressed to create a report based on the value selected in the list box. The problem is the query used to create the report and populate the values in the list box is a crosstab query, which is not updateable of course. Here is the SQL for my query: TRANSFORM Avg(Val([tblCourseGrades].[Grade])) AS AvgOfGrade SELECT TblStudents.[ClassNumber], TblStudents.Rank, TblStudents.[Last Name], TblStudents.[First Name], Avg(tblCourseGrades.Grade) AS AvgOfGrade1 FROM TblStudents INNER JOIN (tblCourses INNER JOIN tblCours...

Why doesn't Excel 2007 automatically update links in Trusted Files
I have a number of workbooks that are all linked to each other. I also occasionally open other workbooks that have links but I may not want to upadate these. In 2003 I could manually set each workbook to update the links so only the ones I wanted would update the links when opened, but others would not. So I had a choice. Now the only choice I have is to update all links (apparently not recommended) or to have Excel ask me to update for every file, which is really no choice at all. All the files I want to update are in my Trusted Locations, so why is there no option to automatical...

tableadapter Query linked to Access 2003 table
I'm trying to write a query for my Dataset's table adapter. It is linked to an access 2003 database. Basically, I'm trying to duplicate the following query. SELECT Parts_Inventory.Aisle, Parts_Inventory.Rack, Parts_Inventory.Shelf, Parts_Inventory.QNTY, Parts_Inventory.MFG_PN, Parts_Inventory.Manufacturer, Parts_Inventory.Description FROM Parts_Inventory WHERE (((Parts_Inventory.Manufacturer) Like "*" & [?] & "*")); I'm using a text box as my control to the manufacturer field. If it is blank, it will show all my parts. I'm able...

Mutliple Complex Queries
Thank you in advance for your assistance. I have data that I must access on a weekly basis that involves a number of criteria. To date, I have performed each query and then coalesced my findings (by hand) into a table. Something tells me that this would be far easier with better knowledge of the program. I have three major data categories (Poor Performance rating, Location, and Seniority) that are dependent upon sorting the Employees into three different sections (Dismissed, Retained, and Reallocated). For each of the sections, I must calculate the percentage based upon the main data...

Update Mutual Fund prices?
Hi, Is it possible to get Money (2004) to download (Fidelity) Mutual Fund prices automatically? How will it work if there is no stock symbol? Currently I have to update them manually. Thanks, Sridev Automatically? Check out Background Banking in help. No stock symbol? It won't download a quote without a stock symbol. "Sridev Raghavan" <sridev@hotmail.nospam.com> wrote in message news:2hn8d.10130$g%5.3295491@news4.srv.hcvlny.cv.net... > Is it possible to get Money (2004) to download (Fidelity) Mutual Fund > prices automatically? How will it work if ther...

Visio 2003
.... the BOOM! Visio 2003 Enterprise Architect installs with no problems (Windows XP Pro, VS.NET 2003, etc..), but when I try to run I get the behavior above. The "directory cache" seems to update, but when Visio starts to paint the design surface - BOOM! (Visio has encountered and error and needs to shut down...) I get the same behavior when I install to a Virtual PC (Windows 2003 Server, VS.NET 2003). I'm trying to study for the 70-300 exam and use Visio EA for the ORM materials. Any feedback/direction would be greatly appreciated. Regards! <wbhm/> wbhm@isual...

Bank of America Update
I just completed the update to Money 2005 that BoA required. After I finished, I found that the update had created duplicates of each of the BoA accounts I had in my file. The new accounts are now the ones linked to BoA; presumabley the old ones no longer are. My problem is that the new accounts do not contain the complete history. For example, my old "Checking" account contained data extending back several years. The new account, "Bank of America (All except CA, WA, & ID) Checking", only contains transactions back to about August 2005. I tried the Merge Dup...

error message preventing installation of office SP1 update
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel keep getting request to install SP1 update12.1.0 and end up with this message"You can't install office 2008 SP1 update (12.1.0) on this volume. A version of the software required to install this update was not found on this volume" I was offered 2 choices, to select my external hard drive(4101GB Free) or my Mac HD (232 GB free)- neither one works. Should I install this update or is this update not meant for the Mac version of office 2008? If not for me how can I get rid of the repeated request that I Update? ...

Access Runtime 2000 prevents office updates
Hi, I have a strange problem that's been bothering me for a long time. I've finally gotten it narrowed down, but can't find anything else about this on the web. I'm using an application that uses Access Runtime 2000 (developed by someone else.) After I install this application, I can no longer use Office Updates. It'll get to about the 88% mark, then gives me the "Office update is unable to check for updates" screen. If I use "add/remove programs" and remove the Runtime 2000 environment, the updater works just fine. Any thoughts on how I can fix the en...

Use Same Variable multiple times in a query with different criteri
ok i have this variable i am trying to use twice with different criteria and it doesnt work help after i do with i want to count them November: Date_mailed (Variable Name) tblRecruitment2 (Table name) Between #11/1/2009# And #11/30/2009# (Criteria) October: Date_mailed tblRecruitment2 Between #10/1/2009# And #10/31/2009# I think you want to count? If so, set the E looking button on your tool bar, which turns on the Group ON feature then change the Group On to count. -- Milton Purdy ACCESS State of Arkansas "BZboarding101" wrote: ...

Excel Microsoft query
Hi I have a few queries, 1. Can a .txt file be joined to another database table? 2. Can I join a two tables in different sheets in the same workbook? 3. I tried to query a table on Excel sheet using the source as Excel Files*, but a field populated with numbers returned blank cells as the results. Why? ...

Auditing Select Queries , so that the full query is logged
Hi, I currently looking into auditing, we want to see who (and when) has viewed/updated patient information on our database. (I am using SQL Server 2008 and our application is developed in VS 2008). I have set up an audit , and a database audit to log any selects for our patient table. However instead of showing the full select statement i.e. select top 1 * from patient where patientnumber = 111111 it is showing select top 1 * from patient where patientnumber = @1 So i also enabled change tracking on the database, this was great if i entered the query in management s...

Parameter query to find null dates at runtime
Hi Everybody I am trying to be able to filter records by whether the date field which is called "RiskDate" in the recordset, is either: 1. Is Null 2. Not Null The parameter in the query field RiskDate is: Like "*" & [Forms]![frmCommissionContainer]![ComboRiskDate] But I cannot figure what to put in ComboRiskDate to supply the parameter at runtime. Most grateful if anyone could help. One approach would be to put anything you like in the combo box ("Null" and "Not Null" or even "Red" and "Blue" if that means something to you...

microsoft update KB936960 wont download
I keep receiving new updates to install but 2 always fail! KB936514 & KB936960! ...

query vs forms
A query looks very similar to a form in datasheet view. Is it possible to call a form from a query if you click on a certain field - or is this only possible using a form. Is is possible to check if a one of two checkboxes has been ticked (say male or female) in a record just by entering the data in a query using some sort of table validation - I know that you can do some limited required field validation. Just wanted to know if this was at all possible because at the moment I am doing all data entry through select queries and it seems to be working Ok, but need to know whether its really nec...

Update value in tbl source for combo box
Using Access 2003. I have a Form (frmRequests) and a SubForm(frmRequests_sub) for capturing report requests. The frmRequests_sub is based on the table "tblRequests" where all data entered in the subform is stored. Within the subform, I have a few combo boxes where the row sources are other tables (with values). I want these tables (supplying the combo boxes data) to be updated if the end user cannot find what they are looking for in the existing list. Example: In the "Requestor" field/combo box where row source is tblRequestor, if "Jane Doe" is not is ...

Querying external data thru macros
Hallu, I'm trying to import some external data, and my query for it is that it needs to be a specific date. I was wondering if i could write a macros so, when i run it there will be a inputbox that pops up asking which date i would like to query for before it imports the data. Any ideas?Thanks, bLySs --- Message posted from http://www.ExcelForum.com/ bLySs (This in XL2003, don't know if this will effect anything)... Maybe you can extract the relevant from the code below, which I set up this morning. It queries an access database on my hard drive called test.mdb, takes two field...