If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to append query w/lookup fields....
I have a table that I need to append w/new records on a weekly basis.
format of original info: UserName TimeStamp RequestType #OfRequests Source pettaj 11/10/2009 ACT notes 2 BR Split the data into UserName, RequestType & Source table - as this information fairly static and the request is in Request table. When appending the Request table with the weekly data the Date & Number of Request, populates just fine, but the UserName, RequestType & Source ends up being blank (these are all lookup field to other tables). What do I need to do? Thanks! -- Krystal K. Peters |
#2
|
|||
|
|||
How to append query w/lookup fields....
You have to join those tables in your append query.
-- Build a little, test a little. "Krystal Peters" wrote: I have a table that I need to append w/new records on a weekly basis. format of original info: UserName TimeStamp RequestType #OfRequests Source pettaj 11/10/2009 ACT notes 2 BR Split the data into UserName, RequestType & Source table - as this information fairly static and the request is in Request table. When appending the Request table with the weekly data the Date & Number of Request, populates just fine, but the UserName, RequestType & Source ends up being blank (these are all lookup field to other tables). What do I need to do? Thanks! -- Krystal K. Peters |
#3
|
|||
|
|||
How to append query w/lookup fields....
Basically, you need to build a query linking the source table to the lookup
tables so you can get the values to store in the target table from the lookup tables. The easiest way to do this is to build a select query that returns the correct data and then use that as the source for your insert query. Example Query to get the data from just one of the lookup tables. INSERT INTO TargetTable (RequestType, TheDate, NumRequests) SELECT RequestTypes.RequestID , SourceTable.TimeStamp , SourceTable.[#OfRequests] FROM SourceTable INNER JOIN RequestTypes ON SourceTable.RequestType = RequestTypes.RequestDescription John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Krystal Peters wrote: I have a table that I need to append w/new records on a weekly basis. format of original info: UserName TimeStamp RequestType #OfRequests Source pettaj 11/10/2009 ACT notes 2 BR Split the data into UserName, RequestType & Source table - as this information fairly static and the request is in Request table. When appending the Request table with the weekly data the Date & Number of Request, populates just fine, but the UserName, RequestType & Source ends up being blank (these are all lookup field to other tables). What do I need to do? Thanks! |
Thread Tools | |
Display Modes | |
|
|