How to append query w/lookup fields....

[Krystal Peters]

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!

 

[KARL DEWEY]

You have to join those tables in your append query.

 

[John Spencer]

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