Append Non-Duplicate & Updated Records

G

Guest

Once a week I receive an Excel File with 300+ records and each record has 30
Fields. I then add a 31st field to hold the date I received the file. Each
record has a key called Portfolio ID.

I have built two tables with 32 fields (the 31 in the excel worksheet and an
autonumbered key). The first will house the "transactional" records
accumulated over time. The second will serve as a "staging" table to house
the records imported records from the current week's file.

I want to append the transactional table with records from the staging table
if the Portfolio ID does not exist in the transactional table OR if any of
the fields for an existing Portfolio ID have changed since the last record
for that Portfolio ID.

I created a select query to pull the most recent record for each Portfolio
ID in the transactional table:
SELECT [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance],
Max([data_BTPortfolioWeekly-Projectview].[BT Report Date]) AS [MaxOfBT Report
Date]
FROM [data_BTPortfolioWeekly-Projectview]
GROUP BY [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance];

Next I have created this append query:
INSERT INTO [data_BTPortfolioWeekly-Projectview] ( [BT Portfolio ID], [BT
Status], [BT Type], [BT Title], [BT Description], [BT BU Priority], [BT BU],
[BT BU Sponsor], [BT BU PM], [BT Financials], [BT BU in Plan], [BT BU Planned
Target], [BT Proposal Received], [BT Project Start], [BT Requirements
Sign-off], [BT Phase], [BT Phase Due], [BT Comitted Flag], [BT Target
Install], [BT Duration], [BT PSA Risk Category], [BT Health Indicator], [BT
Executive Status], [BT Manager], [BT Director], [BT Project Manager], [PF
BHP], [PF Capital], [PF BT Cross Functional], [PF Production Assurance], [BT
Report Date] )
SELECT [import_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[import_BTPortfolioWeekly-Projectview].[BT Status],
[import_BTPortfolioWeekly-Projectview].[BT Type],
[import_BTPortfolioWeekly-Projectview].[BT Title],
[import_BTPortfolioWeekly-Projectview].[BT Description],
[import_BTPortfolioWeekly-Projectview].[BT BU Priority],
[import_BTPortfolioWeekly-Projectview].[BT BU],
[import_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[import_BTPortfolioWeekly-Projectview].[BT BU PM],
[import_BTPortfolioWeekly-Projectview].[BT Financials],
[import_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[import_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[import_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[import_BTPortfolioWeekly-Projectview].[BT Project Start],
[import_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[import_BTPortfolioWeekly-Projectview].[BT Phase],
[import_BTPortfolioWeekly-Projectview].[BT Phase Due],
[import_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[import_BTPortfolioWeekly-Projectview].[BT Target Install],
[import_BTPortfolioWeekly-Projectview].[BT Duration],
[import_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[import_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[import_BTPortfolioWeekly-Projectview].[BT Executive Status],
[import_BTPortfolioWeekly-Projectview].[BT Manager],
[import_BTPortfolioWeekly-Projectview].[BT Director],
[import_BTPortfolioWeekly-Projectview].[BT Project Manager],
[import_BTPortfolioWeekly-Projectview].[PF BHP],
[import_BTPortfolioWeekly-Projectview].[PF Capital],
[import_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[import_BTPortfolioWeekly-Projectview].[PF Production Assurance],
[import_BTPortfolioWeekly-Projectview].[BT Report Date]
FROM [import_BTPortfolioWeekly-Projectview]
WHERE ((([import_BTPortfolioWeekly-Projectview]![BT Portfolio
ID])=[select_ProjectView-LastUpdate]![BT Portfolio ID])) OR
((([import_BTPortfolioWeekly-Projectview]![BT
Status])<>[select_ProjectView-LastUpdate]![BT Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Type])<>[select_ProjectView-LastUpdate]![BT Type]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Title])<>[select_ProjectView-LastUpdate]![BT Title]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Description])<>[select_ProjectView-LastUpdate]![BT Description]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Priority])<>[select_ProjectView-LastUpdate]![BT BU Priority]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
BU])<>[select_ProjectView-LastUpdate]![BT BU]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Sponsor])<>[select_ProjectView-LastUpdate]![BT BU Sponsor]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
PM])<>[select_ProjectView-LastUpdate]![BT BU PM]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Start])<>[select_ProjectView-LastUpdate]![BT Project Start]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Requirements
Sign-off])<>[select_ProjectView-LastUpdate]![BT Requirements Sign-off]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Phase])<>[select_ProjectView-LastUpdate]![BT Phase]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Phase
Due])<>[select_ProjectView-LastUpdate]![BT Phase Due]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Comitted
Flag])<>[select_ProjectView-LastUpdate]![BT Comitted Flag]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Target
Instal])<>[select_ProjectView-LastUpdate]![BT Target Instal]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Duration])<>[select_ProjectView-LastUpdate]![BT Duration]) AND
(([import_BTPortfolioWeekly-Projectview]![BT PSA Risk
Category])<>[select_ProjectView-LastUpdate]![BT PSA Risk Category]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Health
Indicator])<>[select_ProjectView-LastUpdate]![BT Health Indicator]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Executive
Status])<>[select_ProjectView-LastUpdate]![BT Executive Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Manager])<>[select_ProjectView-LastUpdate]![BT Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Director])<>[select_ProjectView-LastUpdate]![BT Director]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Manager])<>[select_ProjectView-LastUpdate]![BT Project Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
BHP])<>[select_ProjectView-LastUpdate]![PF BHP]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
Capital])<>[select_ProjectView-LastUpdate]![PF Capital]) AND
(([import_BTPortfolioWeekly-Projectview]![PF BT Cross
Functional])<>[select_ProjectView-LastUpdate]![PF BT Cross Functional]) AND
(([import_BTPortfolioWeekly-Projectview]![PF Production
Assurance])<>[select_ProjectView-LastUpdate]![PF Production Assurance]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Report
Date])>[select_ProjectView-LastUpdate]![MaxOfBT Report Date]));

When I run the append query it asks me to Enter A Parameter Value
select_ProjectView-LastUpdate!BT Portfolio ID

What did I do wrong and how do I fix it?
Or is there an easier/better way to reach my goal of tracking changes over
time?

Thanks,
 
G

Guest

Prompts like this appear when either a table, field, or controlname cannot be
found by Access. Verify that the table name is correct, and that the field
name is correct and exists in that table.
--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



owp^3 said:
Once a week I receive an Excel File with 300+ records and each record has 30
Fields. I then add a 31st field to hold the date I received the file. Each
record has a key called Portfolio ID.

I have built two tables with 32 fields (the 31 in the excel worksheet and an
autonumbered key). The first will house the "transactional" records
accumulated over time. The second will serve as a "staging" table to house
the records imported records from the current week's file.

I want to append the transactional table with records from the staging table
if the Portfolio ID does not exist in the transactional table OR if any of
the fields for an existing Portfolio ID have changed since the last record
for that Portfolio ID.

I created a select query to pull the most recent record for each Portfolio
ID in the transactional table:
SELECT [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance],
Max([data_BTPortfolioWeekly-Projectview].[BT Report Date]) AS [MaxOfBT Report
Date]
FROM [data_BTPortfolioWeekly-Projectview]
GROUP BY [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance];

Next I have created this append query:
INSERT INTO [data_BTPortfolioWeekly-Projectview] ( [BT Portfolio ID], [BT
Status], [BT Type], [BT Title], [BT Description], [BT BU Priority], [BT BU],
[BT BU Sponsor], [BT BU PM], [BT Financials], [BT BU in Plan], [BT BU Planned
Target], [BT Proposal Received], [BT Project Start], [BT Requirements
Sign-off], [BT Phase], [BT Phase Due], [BT Comitted Flag], [BT Target
Install], [BT Duration], [BT PSA Risk Category], [BT Health Indicator], [BT
Executive Status], [BT Manager], [BT Director], [BT Project Manager], [PF
BHP], [PF Capital], [PF BT Cross Functional], [PF Production Assurance], [BT
Report Date] )
SELECT [import_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[import_BTPortfolioWeekly-Projectview].[BT Status],
[import_BTPortfolioWeekly-Projectview].[BT Type],
[import_BTPortfolioWeekly-Projectview].[BT Title],
[import_BTPortfolioWeekly-Projectview].[BT Description],
[import_BTPortfolioWeekly-Projectview].[BT BU Priority],
[import_BTPortfolioWeekly-Projectview].[BT BU],
[import_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[import_BTPortfolioWeekly-Projectview].[BT BU PM],
[import_BTPortfolioWeekly-Projectview].[BT Financials],
[import_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[import_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[import_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[import_BTPortfolioWeekly-Projectview].[BT Project Start],
[import_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[import_BTPortfolioWeekly-Projectview].[BT Phase],
[import_BTPortfolioWeekly-Projectview].[BT Phase Due],
[import_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[import_BTPortfolioWeekly-Projectview].[BT Target Install],
[import_BTPortfolioWeekly-Projectview].[BT Duration],
[import_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[import_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[import_BTPortfolioWeekly-Projectview].[BT Executive Status],
[import_BTPortfolioWeekly-Projectview].[BT Manager],
[import_BTPortfolioWeekly-Projectview].[BT Director],
[import_BTPortfolioWeekly-Projectview].[BT Project Manager],
[import_BTPortfolioWeekly-Projectview].[PF BHP],
[import_BTPortfolioWeekly-Projectview].[PF Capital],
[import_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[import_BTPortfolioWeekly-Projectview].[PF Production Assurance],
[import_BTPortfolioWeekly-Projectview].[BT Report Date]
FROM [import_BTPortfolioWeekly-Projectview]
WHERE ((([import_BTPortfolioWeekly-Projectview]![BT Portfolio
ID])=[select_ProjectView-LastUpdate]![BT Portfolio ID])) OR
((([import_BTPortfolioWeekly-Projectview]![BT
Status])<>[select_ProjectView-LastUpdate]![BT Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Type])<>[select_ProjectView-LastUpdate]![BT Type]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Title])<>[select_ProjectView-LastUpdate]![BT Title]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Description])<>[select_ProjectView-LastUpdate]![BT Description]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Priority])<>[select_ProjectView-LastUpdate]![BT BU Priority]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
BU])<>[select_ProjectView-LastUpdate]![BT BU]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Sponsor])<>[select_ProjectView-LastUpdate]![BT BU Sponsor]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
PM])<>[select_ProjectView-LastUpdate]![BT BU PM]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Start])<>[select_ProjectView-LastUpdate]![BT Project Start]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Requirements
Sign-off])<>[select_ProjectView-LastUpdate]![BT Requirements Sign-off]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Phase])<>[select_ProjectView-LastUpdate]![BT Phase]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Phase
Due])<>[select_ProjectView-LastUpdate]![BT Phase Due]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Comitted
Flag])<>[select_ProjectView-LastUpdate]![BT Comitted Flag]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Target
Instal])<>[select_ProjectView-LastUpdate]![BT Target Instal]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Duration])<>[select_ProjectView-LastUpdate]![BT Duration]) AND
(([import_BTPortfolioWeekly-Projectview]![BT PSA Risk
Category])<>[select_ProjectView-LastUpdate]![BT PSA Risk Category]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Health
Indicator])<>[select_ProjectView-LastUpdate]![BT Health Indicator]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Executive
Status])<>[select_ProjectView-LastUpdate]![BT Executive Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Manager])<>[select_ProjectView-LastUpdate]![BT Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Director])<>[select_ProjectView-LastUpdate]![BT Director]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Manager])<>[select_ProjectView-LastUpdate]![BT Project Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
BHP])<>[select_ProjectView-LastUpdate]![PF BHP]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
Capital])<>[select_ProjectView-LastUpdate]![PF Capital]) AND
(([import_BTPortfolioWeekly-Projectview]![PF BT Cross
Functional])<>[select_ProjectView-LastUpdate]![PF BT Cross Functional]) AND
(([import_BTPortfolioWeekly-Projectview]![PF Production
Assurance])<>[select_ProjectView-LastUpdate]![PF Production Assurance]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Report
Date])>[select_ProjectView-LastUpdate]![MaxOfBT Report Date]));

When I run the append query it asks me to Enter A Parameter Value
select_ProjectView-LastUpdate!BT Portfolio ID

What did I do wrong and how do I fix it?
Or is there an easier/better way to reach my goal of tracking changes over
time?

Thanks,
Click to expand...
 
G

Guest

Thanks Steve. The query is too big for me to figure out where the syntax
error is.
Besides I am not sure I am taking the simplest approach.

S.Clark said:
Prompts like this appear when either a table, field, or controlname cannot be
found by Access. Verify that the table name is correct, and that the field
name is correct and exists in that table.
--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



owp^3 said:
Once a week I receive an Excel File with 300+ records and each record has 30
Fields. I then add a 31st field to hold the date I received the file. Each
record has a key called Portfolio ID.

I have built two tables with 32 fields (the 31 in the excel worksheet and an
autonumbered key). The first will house the "transactional" records
accumulated over time. The second will serve as a "staging" table to house
the records imported records from the current week's file.

I want to append the transactional table with records from the staging table
if the Portfolio ID does not exist in the transactional table OR if any of
the fields for an existing Portfolio ID have changed since the last record
for that Portfolio ID.

I created a select query to pull the most recent record for each Portfolio
ID in the transactional table:
SELECT [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance],
Max([data_BTPortfolioWeekly-Projectview].[BT Report Date]) AS [MaxOfBT Report
Date]
FROM [data_BTPortfolioWeekly-Projectview]
GROUP BY [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance];

Next I have created this append query:
INSERT INTO [data_BTPortfolioWeekly-Projectview] ( [BT Portfolio ID], [BT
Status], [BT Type], [BT Title], [BT Description], [BT BU Priority], [BT BU],
[BT BU Sponsor], [BT BU PM], [BT Financials], [BT BU in Plan], [BT BU Planned
Target], [BT Proposal Received], [BT Project Start], [BT Requirements
Sign-off], [BT Phase], [BT Phase Due], [BT Comitted Flag], [BT Target
Install], [BT Duration], [BT PSA Risk Category], [BT Health Indicator], [BT
Executive Status], [BT Manager], [BT Director], [BT Project Manager], [PF
BHP], [PF Capital], [PF BT Cross Functional], [PF Production Assurance], [BT
Report Date] )
SELECT [import_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[import_BTPortfolioWeekly-Projectview].[BT Status],
[import_BTPortfolioWeekly-Projectview].[BT Type],
[import_BTPortfolioWeekly-Projectview].[BT Title],
[import_BTPortfolioWeekly-Projectview].[BT Description],
[import_BTPortfolioWeekly-Projectview].[BT BU Priority],
[import_BTPortfolioWeekly-Projectview].[BT BU],
[import_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[import_BTPortfolioWeekly-Projectview].[BT BU PM],
[import_BTPortfolioWeekly-Projectview].[BT Financials],
[import_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[import_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[import_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[import_BTPortfolioWeekly-Projectview].[BT Project Start],
[import_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[import_BTPortfolioWeekly-Projectview].[BT Phase],
[import_BTPortfolioWeekly-Projectview].[BT Phase Due],
[import_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[import_BTPortfolioWeekly-Projectview].[BT Target Install],
[import_BTPortfolioWeekly-Projectview].[BT Duration],
[import_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[import_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[import_BTPortfolioWeekly-Projectview].[BT Executive Status],
[import_BTPortfolioWeekly-Projectview].[BT Manager],
[import_BTPortfolioWeekly-Projectview].[BT Director],
[import_BTPortfolioWeekly-Projectview].[BT Project Manager],
[import_BTPortfolioWeekly-Projectview].[PF BHP],
[import_BTPortfolioWeekly-Projectview].[PF Capital],
[import_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[import_BTPortfolioWeekly-Projectview].[PF Production Assurance],
[import_BTPortfolioWeekly-Projectview].[BT Report Date]
FROM [import_BTPortfolioWeekly-Projectview]
WHERE ((([import_BTPortfolioWeekly-Projectview]![BT Portfolio
ID])=[select_ProjectView-LastUpdate]![BT Portfolio ID])) OR
((([import_BTPortfolioWeekly-Projectview]![BT
Status])<>[select_ProjectView-LastUpdate]![BT Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Type])<>[select_ProjectView-LastUpdate]![BT Type]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Title])<>[select_ProjectView-LastUpdate]![BT Title]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Description])<>[select_ProjectView-LastUpdate]![BT Description]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Priority])<>[select_ProjectView-LastUpdate]![BT BU Priority]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
BU])<>[select_ProjectView-LastUpdate]![BT BU]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Sponsor])<>[select_ProjectView-LastUpdate]![BT BU Sponsor]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
PM])<>[select_ProjectView-LastUpdate]![BT BU PM]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Start])<>[select_ProjectView-LastUpdate]![BT Project Start]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Requirements
Sign-off])<>[select_ProjectView-LastUpdate]![BT Requirements Sign-off]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Phase])<>[select_ProjectView-LastUpdate]![BT Phase]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Phase
Due])<>[select_ProjectView-LastUpdate]![BT Phase Due]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Comitted
Flag])<>[select_ProjectView-LastUpdate]![BT Comitted Flag]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Target
Instal])<>[select_ProjectView-LastUpdate]![BT Target Instal]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Duration])<>[select_ProjectView-LastUpdate]![BT Duration]) AND
(([import_BTPortfolioWeekly-Projectview]![BT PSA Risk
Category])<>[select_ProjectView-LastUpdate]![BT PSA Risk Category]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Health
Indicator])<>[select_ProjectView-LastUpdate]![BT Health Indicator]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Executive
Status])<>[select_ProjectView-LastUpdate]![BT Executive Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Manager])<>[select_ProjectView-LastUpdate]![BT Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Director])<>[select_ProjectView-LastUpdate]![BT Director]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Manager])<>[select_ProjectView-LastUpdate]![BT Project Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
BHP])<>[select_ProjectView-LastUpdate]![PF BHP]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
Capital])<>[select_ProjectView-LastUpdate]![PF Capital]) AND
(([import_BTPortfolioWeekly-Projectview]![PF BT Cross
Functional])<>[select_ProjectView-LastUpdate]![PF BT Cross Functional]) AND
(([import_BTPortfolioWeekly-Projectview]![PF Production
Assurance])<>[select_ProjectView-LastUpdate]![PF Production Assurance]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Report
Date])>[select_ProjectView-LastUpdate]![MaxOfBT Report Date]));

When I run the append query it asks me to Enter A Parameter Value
select_ProjectView-LastUpdate!BT Portfolio ID

What did I do wrong and how do I fix it?
Or is there an easier/better way to reach my goal of tracking changes over
time?

Thanks,
Click to expand...
Click to expand...
 
G

Guest

Here is a simplified scenario to make the discussion easier:

Field Names for TransactionTBL and StagingTBL are RecordID, ObjectID,
Value1, Value2, Memo1, and UpdatedOn

Transaction Table (Start)
1, ID001, 10, 1, Comment01, 1/1/2007
2, ID002, 10, 1, Comment01, 1/1/2007
3, ID003, 10, 1, Comment01, 1/1/2007
4, ID004, 10, 1, Comment01, 1/1/2007
5, ID005, 10, 1, Comment01, 1/1/2007
6, ID001, 20, 2, Comment02, 2/1/2007
7, ID002, 20, 2, Comment02, 2/1/2007
8, ID003, 20, 2, Comment02, 2/1/2007
9, ID004, 20, 2, Comment02, 2/1/2007
10, ID005, 20, 2, Comment02, 2/1/2007
11, ID001, 30, 2, Comment02, 3/1/2007
12, ID002, 20, 3, Comment02, 3/1/2007
13, ID003, 20, 2, Comment03, 3/1/2007

Staging Table
1, ID001, 30, 2, Comment02, 4/1/2007
2, ID002, 30, 3, Comment02, 4/1/2007
3, ID003, 20, 3, Comment03, 4/1/2007
4, ID004, 20, 2, Comment03, 4/1/2007
5, ID005, 20, 2, Comment02, 4/1/2007
6, ID006, 40, 4, Comment01, 4/1/2007

Transaction Table (End)
1, ID001, 10, 1, Comment01, 1/1/2007
…
13, ID003, 20, 2, Comment03, 3/1/2007
14, ID002, 30, 3, Comment02, 4/1/2007
15, ID003, 20, 3, Comment03, 4/1/2007
16, ID004, 20, 2, Comment03, 4/1/2007
17, ID006, 40, 4, Comment01, 4/1/2007

StagingTBL 2,3, and 4 have at least one field that is different from
TransactionTBL 9,12, and 13 (MAX UpdatedOn) and StagingTBL 6 is brand new.

What is the easiest way to append, after record 13, the TransactionTBL with
records 2,3,4, and 6 from the StagingTBL?

Would it be easier to do it all in one append query with sub-queries?
Or
Would it be easier to break it up into steps with seperate queries?

What would those queries look like?
Will comparing Memo fields with alot of text cause a problem?

Thanks,

owp^3 said:
Once a week I receive an Excel File with 300+ records and each record has 30
Fields. I then add a 31st field to hold the date I received the file. Each
record has a key called Portfolio ID.

I have built two tables with 32 fields (the 31 in the excel worksheet and an
autonumbered key). The first will house the "transactional" records
accumulated over time. The second will serve as a "staging" table to house
the records imported records from the current week's file.

I want to append the transactional table with records from the staging table
if the Portfolio ID does not exist in the transactional table OR if any of
the fields for an existing Portfolio ID have changed since the last record
for that Portfolio ID.

I created a select query to pull the most recent record for each Portfolio
ID in the transactional table:
SELECT [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance],
Max([data_BTPortfolioWeekly-Projectview].[BT Report Date]) AS [MaxOfBT Report
Date]
FROM [data_BTPortfolioWeekly-Projectview]
GROUP BY [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance];

Next I have created this append query:
INSERT INTO [data_BTPortfolioWeekly-Projectview] ( [BT Portfolio ID], [BT
Status], [BT Type], [BT Title], [BT Description], [BT BU Priority], [BT BU],
[BT BU Sponsor], [BT BU PM], [BT Financials], [BT BU in Plan], [BT BU Planned
Target], [BT Proposal Received], [BT Project Start], [BT Requirements
Sign-off], [BT Phase], [BT Phase Due], [BT Comitted Flag], [BT Target
Install], [BT Duration], [BT PSA Risk Category], [BT Health Indicator], [BT
Executive Status], [BT Manager], [BT Director], [BT Project Manager], [PF
BHP], [PF Capital], [PF BT Cross Functional], [PF Production Assurance], [BT
Report Date] )
SELECT [import_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[import_BTPortfolioWeekly-Projectview].[BT Status],
[import_BTPortfolioWeekly-Projectview].[BT Type],
[import_BTPortfolioWeekly-Projectview].[BT Title],
[import_BTPortfolioWeekly-Projectview].[BT Description],
[import_BTPortfolioWeekly-Projectview].[BT BU Priority],
[import_BTPortfolioWeekly-Projectview].[BT BU],
[import_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[import_BTPortfolioWeekly-Projectview].[BT BU PM],
[import_BTPortfolioWeekly-Projectview].[BT Financials],
[import_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[import_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[import_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[import_BTPortfolioWeekly-Projectview].[BT Project Start],
[import_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[import_BTPortfolioWeekly-Projectview].[BT Phase],
[import_BTPortfolioWeekly-Projectview].[BT Phase Due],
[import_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[import_BTPortfolioWeekly-Projectview].[BT Target Install],
[import_BTPortfolioWeekly-Projectview].[BT Duration],
[import_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[import_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[import_BTPortfolioWeekly-Projectview].[BT Executive Status],
[import_BTPortfolioWeekly-Projectview].[BT Manager],
[import_BTPortfolioWeekly-Projectview].[BT Director],
[import_BTPortfolioWeekly-Projectview].[BT Project Manager],
[import_BTPortfolioWeekly-Projectview].[PF BHP],
[import_BTPortfolioWeekly-Projectview].[PF Capital],
[import_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[import_BTPortfolioWeekly-Projectview].[PF Production Assurance],
[import_BTPortfolioWeekly-Projectview].[BT Report Date]
FROM [import_BTPortfolioWeekly-Projectview]
WHERE ((([import_BTPortfolioWeekly-Projectview]![BT Portfolio
ID])=[select_ProjectView-LastUpdate]![BT Portfolio ID])) OR
((([import_BTPortfolioWeekly-Projectview]![BT
Status])<>[select_ProjectView-LastUpdate]![BT Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Type])<>[select_ProjectView-LastUpdate]![BT Type]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Title])<>[select_ProjectView-LastUpdate]![BT Title]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Description])<>[select_ProjectView-LastUpdate]![BT Description]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Priority])<>[select_ProjectView-LastUpdate]![BT BU Priority]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
BU])<>[select_ProjectView-LastUpdate]![BT BU]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Sponsor])<>[select_ProjectView-LastUpdate]![BT BU Sponsor]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
PM])<>[select_ProjectView-LastUpdate]![BT BU PM]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Start])<>[select_ProjectView-LastUpdate]![BT Project Start]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Requirements
Sign-off])<>[select_ProjectView-LastUpdate]![BT Requirements Sign-off]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Phase])<>[select_ProjectView-LastUpdate]![BT Phase]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Phase
Due])<>[select_ProjectView-LastUpdate]![BT Phase Due]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Comitted
Flag])<>[select_ProjectView-LastUpdate]![BT Comitted Flag]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Target
Instal])<>[select_ProjectView-LastUpdate]![BT Target Instal]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Duration])<>[select_ProjectView-LastUpdate]![BT Duration]) AND
(([import_BTPortfolioWeekly-Projectview]![BT PSA Risk
Category])<>[select_ProjectView-LastUpdate]![BT PSA Risk Category]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Health
Indicator])<>[select_ProjectView-LastUpdate]![BT Health Indicator]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Executive
Status])<>[select_ProjectView-LastUpdate]![BT Executive Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Manager])<>[select_ProjectView-LastUpdate]![BT Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Director])<>[select_ProjectView-LastUpdate]![BT Director]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Manager])<>[select_ProjectView-LastUpdate]![BT Project Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
BHP])<>[select_ProjectView-LastUpdate]![PF BHP]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
Capital])<>[select_ProjectView-LastUpdate]![PF Capital]) AND
(([import_BTPortfolioWeekly-Projectview]![PF BT Cross
Functional])<>[select_ProjectView-LastUpdate]![PF BT Cross Functional]) AND
(([import_BTPortfolioWeekly-Projectview]![PF Production
Assurance])<>[select_ProjectView-LastUpdate]![PF Production Assurance]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Report
Date])>[select_ProjectView-LastUpdate]![MaxOfBT Report Date]));

When I run the append query it asks me to Enter A Parameter Value
select_ProjectView-LastUpdate!BT Portfolio ID

What did I do wrong and how do I fix it?
Or is there an easier/better way to reach my goal of tracking changes over
time?

Thanks,
Click to expand...
 
G

Guest

Ok I think I am close...

I have a select query called sample_LastUpdated to identify the records in
the TransactionTBL to be compared.

SELECT sample_Transactions.ObjectID, sample_Transactions.Value1,
sample_Transactions.Value2, sample_Transactions.Memo1,
sample_Transactions.UpdatedOn
FROM sample_Transactions
WHERE (((sample_Transactions.UpdatedOn)=(Select Max(X.UpdatedOn) FROM
sample_Transactions AS X WHERE X.ObjectID = sample_Transactions.ObjectID)));

Then I have a select query, which will be changed to an append query once i
am certain it works. It is called sample_AppendQuery.

SELECT sample_Import.*, sample_LastUpdated.ObjectID
FROM sample_Import LEFT JOIN sample_LastUpdated ON (sample_Import.Memo1 =
sample_LastUpdated.Memo1) AND (sample_Import.ObjectID =
sample_LastUpdated.ObjectID) AND (sample_Import.Value1 =
sample_LastUpdated.Value1) AND (sample_Import.Value2 =
sample_LastUpdated.Value2)
WHERE (((sample_LastUpdated.ObjectID) Is Null));

It works fine and pulls StagingTBL records 2,3,and 6 if I don't include
[(sample_Import.Memo1 = sample_LastUpdated.Memo1) AND].
If I include it the query errors out.

I saw a possible solution on this forum where you compare the first 255 and
then the last 255 with criteria and this approach uses joins. I am not sure
how I would go about mixing the two joins and criteria.

Thanks again!

owp^3 said:
Here is a simplified scenario to make the discussion easier:

Field Names for TransactionTBL and StagingTBL are RecordID, ObjectID,
Value1, Value2, Memo1, and UpdatedOn

Transaction Table (Start)
1, ID001, 10, 1, Comment01, 1/1/2007
2, ID002, 10, 1, Comment01, 1/1/2007
3, ID003, 10, 1, Comment01, 1/1/2007
4, ID004, 10, 1, Comment01, 1/1/2007
5, ID005, 10, 1, Comment01, 1/1/2007
6, ID001, 20, 2, Comment02, 2/1/2007
7, ID002, 20, 2, Comment02, 2/1/2007
8, ID003, 20, 2, Comment02, 2/1/2007
9, ID004, 20, 2, Comment02, 2/1/2007
10, ID005, 20, 2, Comment02, 2/1/2007
11, ID001, 30, 2, Comment02, 3/1/2007
12, ID002, 20, 3, Comment02, 3/1/2007
13, ID003, 20, 2, Comment03, 3/1/2007

Staging Table
1, ID001, 30, 2, Comment02, 4/1/2007
2, ID002, 30, 3, Comment02, 4/1/2007
3, ID003, 20, 3, Comment03, 4/1/2007
4, ID004, 20, 2, Comment03, 4/1/2007
5, ID005, 20, 2, Comment02, 4/1/2007
6, ID006, 40, 4, Comment01, 4/1/2007

Transaction Table (End)
1, ID001, 10, 1, Comment01, 1/1/2007
…
13, ID003, 20, 2, Comment03, 3/1/2007
14, ID002, 30, 3, Comment02, 4/1/2007
15, ID003, 20, 3, Comment03, 4/1/2007
16, ID004, 20, 2, Comment03, 4/1/2007
17, ID006, 40, 4, Comment01, 4/1/2007

StagingTBL 2,3, and 4 have at least one field that is different from
TransactionTBL 9,12, and 13 (MAX UpdatedOn) and StagingTBL 6 is brand new.

What is the easiest way to append, after record 13, the TransactionTBL with
records 2,3,4, and 6 from the StagingTBL?

Would it be easier to do it all in one append query with sub-queries?
Or
Would it be easier to break it up into steps with seperate queries?

What would those queries look like?
Will comparing Memo fields with alot of text cause a problem?

Thanks,

owp^3 said:
Once a week I receive an Excel File with 300+ records and each record has 30
Fields. I then add a 31st field to hold the date I received the file. Each
record has a key called Portfolio ID.

I have built two tables with 32 fields (the 31 in the excel worksheet and an
autonumbered key). The first will house the "transactional" records
accumulated over time. The second will serve as a "staging" table to house
the records imported records from the current week's file.

I want to append the transactional table with records from the staging table
if the Portfolio ID does not exist in the transactional table OR if any of
the fields for an existing Portfolio ID have changed since the last record
for that Portfolio ID.

I created a select query to pull the most recent record for each Portfolio
ID in the transactional table:
SELECT [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance],
Max([data_BTPortfolioWeekly-Projectview].[BT Report Date]) AS [MaxOfBT Report
Date]
FROM [data_BTPortfolioWeekly-Projectview]
GROUP BY [data_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[data_BTPortfolioWeekly-Projectview].[BT Status],
[data_BTPortfolioWeekly-Projectview].[BT Type],
[data_BTPortfolioWeekly-Projectview].[BT Title],
[data_BTPortfolioWeekly-Projectview].[BT Description],
[data_BTPortfolioWeekly-Projectview].[BT BU Priority],
[data_BTPortfolioWeekly-Projectview].[BT BU],
[data_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[data_BTPortfolioWeekly-Projectview].[BT BU PM],
[data_BTPortfolioWeekly-Projectview].[BT Financials],
[data_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[data_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[data_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[data_BTPortfolioWeekly-Projectview].[BT Project Start],
[data_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[data_BTPortfolioWeekly-Projectview].[BT Phase],
[data_BTPortfolioWeekly-Projectview].[BT Phase Due],
[data_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[data_BTPortfolioWeekly-Projectview].[BT Target Install],
[data_BTPortfolioWeekly-Projectview].[BT Duration],
[data_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[data_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[data_BTPortfolioWeekly-Projectview].[BT Executive Status],
[data_BTPortfolioWeekly-Projectview].[BT Manager],
[data_BTPortfolioWeekly-Projectview].[BT Director],
[data_BTPortfolioWeekly-Projectview].[BT Project Manager],
[data_BTPortfolioWeekly-Projectview].[PF BHP],
[data_BTPortfolioWeekly-Projectview].[PF Capital],
[data_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[data_BTPortfolioWeekly-Projectview].[PF Production Assurance];

Next I have created this append query:
INSERT INTO [data_BTPortfolioWeekly-Projectview] ( [BT Portfolio ID], [BT
Status], [BT Type], [BT Title], [BT Description], [BT BU Priority], [BT BU],
[BT BU Sponsor], [BT BU PM], [BT Financials], [BT BU in Plan], [BT BU Planned
Target], [BT Proposal Received], [BT Project Start], [BT Requirements
Sign-off], [BT Phase], [BT Phase Due], [BT Comitted Flag], [BT Target
Install], [BT Duration], [BT PSA Risk Category], [BT Health Indicator], [BT
Executive Status], [BT Manager], [BT Director], [BT Project Manager], [PF
BHP], [PF Capital], [PF BT Cross Functional], [PF Production Assurance], [BT
Report Date] )
SELECT [import_BTPortfolioWeekly-Projectview].[BT Portfolio ID],
[import_BTPortfolioWeekly-Projectview].[BT Status],
[import_BTPortfolioWeekly-Projectview].[BT Type],
[import_BTPortfolioWeekly-Projectview].[BT Title],
[import_BTPortfolioWeekly-Projectview].[BT Description],
[import_BTPortfolioWeekly-Projectview].[BT BU Priority],
[import_BTPortfolioWeekly-Projectview].[BT BU],
[import_BTPortfolioWeekly-Projectview].[BT BU Sponsor],
[import_BTPortfolioWeekly-Projectview].[BT BU PM],
[import_BTPortfolioWeekly-Projectview].[BT Financials],
[import_BTPortfolioWeekly-Projectview].[BT BU in Plan],
[import_BTPortfolioWeekly-Projectview].[BT BU Planned Target],
[import_BTPortfolioWeekly-Projectview].[BT Proposal Received],
[import_BTPortfolioWeekly-Projectview].[BT Project Start],
[import_BTPortfolioWeekly-Projectview].[BT Requirements Sign-off],
[import_BTPortfolioWeekly-Projectview].[BT Phase],
[import_BTPortfolioWeekly-Projectview].[BT Phase Due],
[import_BTPortfolioWeekly-Projectview].[BT Comitted Flag],
[import_BTPortfolioWeekly-Projectview].[BT Target Install],
[import_BTPortfolioWeekly-Projectview].[BT Duration],
[import_BTPortfolioWeekly-Projectview].[BT PSA Risk Category],
[import_BTPortfolioWeekly-Projectview].[BT Health Indicator],
[import_BTPortfolioWeekly-Projectview].[BT Executive Status],
[import_BTPortfolioWeekly-Projectview].[BT Manager],
[import_BTPortfolioWeekly-Projectview].[BT Director],
[import_BTPortfolioWeekly-Projectview].[BT Project Manager],
[import_BTPortfolioWeekly-Projectview].[PF BHP],
[import_BTPortfolioWeekly-Projectview].[PF Capital],
[import_BTPortfolioWeekly-Projectview].[PF BT Cross Functional],
[import_BTPortfolioWeekly-Projectview].[PF Production Assurance],
[import_BTPortfolioWeekly-Projectview].[BT Report Date]
FROM [import_BTPortfolioWeekly-Projectview]
WHERE ((([import_BTPortfolioWeekly-Projectview]![BT Portfolio
ID])=[select_ProjectView-LastUpdate]![BT Portfolio ID])) OR
((([import_BTPortfolioWeekly-Projectview]![BT
Status])<>[select_ProjectView-LastUpdate]![BT Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Type])<>[select_ProjectView-LastUpdate]![BT Type]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Title])<>[select_ProjectView-LastUpdate]![BT Title]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Description])<>[select_ProjectView-LastUpdate]![BT Description]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Priority])<>[select_ProjectView-LastUpdate]![BT BU Priority]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
BU])<>[select_ProjectView-LastUpdate]![BT BU]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
Sponsor])<>[select_ProjectView-LastUpdate]![BT BU Sponsor]) AND
(([import_BTPortfolioWeekly-Projectview]![BT BU
PM])<>[select_ProjectView-LastUpdate]![BT BU PM]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Start])<>[select_ProjectView-LastUpdate]![BT Project Start]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Requirements
Sign-off])<>[select_ProjectView-LastUpdate]![BT Requirements Sign-off]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Phase])<>[select_ProjectView-LastUpdate]![BT Phase]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Phase
Due])<>[select_ProjectView-LastUpdate]![BT Phase Due]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Comitted
Flag])<>[select_ProjectView-LastUpdate]![BT Comitted Flag]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Target
Instal])<>[select_ProjectView-LastUpdate]![BT Target Instal]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Duration])<>[select_ProjectView-LastUpdate]![BT Duration]) AND
(([import_BTPortfolioWeekly-Projectview]![BT PSA Risk
Category])<>[select_ProjectView-LastUpdate]![BT PSA Risk Category]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Health
Indicator])<>[select_ProjectView-LastUpdate]![BT Health Indicator]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Executive
Status])<>[select_ProjectView-LastUpdate]![BT Executive Status]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Manager])<>[select_ProjectView-LastUpdate]![BT Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![BT
Director])<>[select_ProjectView-LastUpdate]![BT Director]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Project
Manager])<>[select_ProjectView-LastUpdate]![BT Project Manager]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
BHP])<>[select_ProjectView-LastUpdate]![PF BHP]) AND
(([import_BTPortfolioWeekly-Projectview]![PF
Capital])<>[select_ProjectView-LastUpdate]![PF Capital]) AND
(([import_BTPortfolioWeekly-Projectview]![PF BT Cross
Functional])<>[select_ProjectView-LastUpdate]![PF BT Cross Functional]) AND
(([import_BTPortfolioWeekly-Projectview]![PF Production
Assurance])<>[select_ProjectView-LastUpdate]![PF Production Assurance]) AND
(([import_BTPortfolioWeekly-Projectview]![BT Report
Date])>[select_ProjectView-LastUpdate]![MaxOfBT Report Date]));

When I run the append query it asks me to Enter A Parameter Value
select_ProjectView-LastUpdate!BT Portfolio ID

What did I do wrong and how do I fix it?
Or is there an easier/better way to reach my goal of tracking changes over
time?

Thanks,
Click to expand...
Click to expand...
 
M

Marshall Barton

owp^3 said:
Ok I think I am close...

I have a select query called sample_LastUpdated to identify the records in
the TransactionTBL to be compared.

SELECT sample_Transactions.ObjectID, sample_Transactions.Value1,
sample_Transactions.Value2, sample_Transactions.Memo1,
sample_Transactions.UpdatedOn
FROM sample_Transactions
WHERE (((sample_Transactions.UpdatedOn)=(Select Max(X.UpdatedOn) FROM
sample_Transactions AS X WHERE X.ObjectID = sample_Transactions.ObjectID)));

Then I have a select query, which will be changed to an append query once i
am certain it works. It is called sample_AppendQuery.

SELECT sample_Import.*, sample_LastUpdated.ObjectID
FROM sample_Import LEFT JOIN sample_LastUpdated ON (sample_Import.Memo1 =
sample_LastUpdated.Memo1) AND (sample_Import.ObjectID =
sample_LastUpdated.ObjectID) AND (sample_Import.Value1 =
sample_LastUpdated.Value1) AND (sample_Import.Value2 =
sample_LastUpdated.Value2)
WHERE (((sample_LastUpdated.ObjectID) Is Null));

It works fine and pulls StagingTBL records 2,3,and 6 if I don't include
[(sample_Import.Memo1 = sample_LastUpdated.Memo1) AND].
If I include it the query errors out.

I saw a possible solution on this forum where you compare the first 255 and
then the last 255 with criteria and this approach uses joins. I am not sure
how I would go about mixing the two joins and criteria.
Click to expand...


That looks like the right approach, except you can not join
on a memo field. The idea of joining on the first and last
255 characters should be pretty reliable:

SELECT SI.*, LU.ObjectID
FROM sample_Import As SI
LEFT JOIN sample_LastUpdated As LU
ON Left(SI.Memo1, 255) = Left(LU.Memo1, 255)
AND Right(SI.Memo1, 255) = Right(LU.Memo1, 255)
AND (SI.ObjectID = LU.ObjectID)
AND (SI.Value1 = LU.Value1)
AND (SI.Value2 = LU.Value2)
WHERE LU.ObjectID) Is Null

I think you probably want to get rid of LU.ObjectID in the
query's field list.
 
G

Guest

Thanks Marshall!

It worked fine as a select query and pulled the 4 records.
So the next step is to turn it into an append query.

DesignView doesn't work with the SQL for a Left/Right Memo Joins.
So I can either change it to an append with the SQLview or,
since I didn't know the correct syntax, take the Left/Right Memo Joins out
switch to DesignView, change it to an append query, switch to SQLview
add the joins back in.

OK! Got that done and it looks like this:
INSERT INTO sample_Transactions ( ObjectID )
SELECT SI.*, SLU.ObjectID AS ObjectID
FROM sample_Import AS SI LEFT JOIN sample_LastUpdated AS SLU
ON (SI.Value2=SLU.Value2)
AND (SI.Value1=SLU.Value1)
AND (SI.ObjectID=SLU.ObjectID)
AND (Right(SI.Memo1,255)=Right(SLU.Memo1,255))
AND (Left(SI.Memo1,255)=Left(SLU.Memo1,255))
WHERE (((SLU.ObjectID) Is Null));

BUT I get an error message:
"Duplicate output destination 'ObjectID'."

What did I do wrong?

Thanks,

owp^3
 
G

Guest

OK, I kept Playing with it and got the right results for my sample files with
these queries:

sample_LastUpdated
SELECT sample_Transactions.ObjectID, sample_Transactions.Value1,
sample_Transactions.Value2, sample_Transactions.Memo1,
sample_Transactions.UpdatedOn
FROM sample_Transactions
WHERE (((sample_Transactions.UpdatedOn)=(Select Max(X.UpdatedOn) FROM
sample_Transactions AS X WHERE X.ObjectID = sample_Transactions.ObjectID)));

sample_AppendQuery
INSERT INTO sample_Transactions ( ObjectID, Value1, Value2, Memo1,UpdatedOn )
SELECT SI.ObjectID, SI.Value1,SI.Value2,SI.Memo1,SI.UpdatedOn
FROM sample_Import AS SI LEFT JOIN sample_LastUpdated AS SLU ON
(SI.Value2=SLU.Value2) AND (SI.Value1=SLU.Value1) AND
(SI.ObjectID=SLU.ObjectID) AND (Right(SI.Memo1,255)=Right(SLU.Memo1,255)) AND
(Left(SI.Memo1,255)=Left(SLU.Memo1,255))
WHERE (((SLU.ObjectID) Is Null));

Now I get to start all over with the real tables and test changing all
30+fields.

Thanks everyone for the help!

owp^3
 
M

Marshall Barton

Design view only works for the basic fa=fb type comparisons
in the ON clause, so you'll have to work in SQL view from
now on.

The field list in the INSERT INTO clause must match up with
the field list in the SELECT clause. The way you wrote it,
it appears that you only want the new records to have the
ObjectID field. I thought you wanted all the fields in
import table??

I think the error message might be because there is an
ObjectID field in both of the source tables. Clean up the
field lists and this error should go away.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report not displaying records that don't have children 4
OpenOffice.Org Newsletter - Vol. 01 Issue 9 3

Top