Append to Year to date table

[Mike D]

I want to Append data from an existing table ( Safety supply transactions)
into a YTD table ( SUPPLIES USAGE YEAR TO DATE) including only new records
based new records added to Safety supply transactions based on the date.
Below is the SQL view of what I have set up.

INSERT INTO [SUPPLIES USAGE YEAR TO DATE] ( UPUPRF, [DATE], [Productpart#],
ProductDescription, Catagory, DEPT, UPTEXT1, UnitPrice, Quanity )
SELECT [Safety supply transactions].UPUPRF, [Safety supply
transactions].DATE, Products.[Productpart#], Products.ProductDescription,
Products.Catagory, [USERS QGPL].DEPT, [USERS QGPL].UPTEXT1,
Products.UnitPrice, [Safety supply transactions].Quanity
FROM ([Safety supply transactions] INNER JOIN Products ON [Safety supply
transactions].[Product Id #] = Products.[Product Id #]) INNER JOIN [USERS
QGPL] ON [Safety supply transactions].UPUPRF = [USERS QGPL].UPUPRF
ORDER BY [Safety supply transactions].DATE;

 

[Jeff Boyce]

Mike

Why? As in "why do you want to move records from one table to another?"

If you include a date/time field in your table, you can use a query to get a
YTD recordset.

Using separate tables for each YEAR (I'm only guessing here) would be how
you'd probably do it if you were limited to using a spreadsheet. Not
necessary (nor desirable) when using Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mike D]

I have a macro brings in a current users using ODBC Database from AS400. if
users are terminated from the system I loose the historical records for that
user.

I am open to other Suggestions.

Mike

Why? As in "why do you want to move records from one table to another?"

If you include a date/time field in your table, you can use a query to get a
YTD recordset.

Using separate tables for each YEAR (I'm only guessing here) would be how
you'd probably do it if you were limited to using a spreadsheet. Not
necessary (nor desirable) when using Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to Append data from an existing table ( Safety supply transactions)
into a YTD table ( SUPPLIES USAGE YEAR TO DATE) including only new records
based new records added to Safety supply transactions based on the date.
Below is the SQL view of what I have set up.

INSERT INTO [SUPPLIES USAGE YEAR TO DATE] ( UPUPRF, [DATE],
[Productpart#],
ProductDescription, Catagory, DEPT, UPTEXT1, UnitPrice, Quanity )
SELECT [Safety supply transactions].UPUPRF, [Safety supply
transactions].DATE, Products.[Productpart#], Products.ProductDescription,
Products.Catagory, [USERS QGPL].DEPT, [USERS QGPL].UPTEXT1,
Products.UnitPrice, [Safety supply transactions].Quanity
FROM ([Safety supply transactions] INNER JOIN Products ON [Safety supply
transactions].[Product Id #] = Products.[Product Id #]) INNER JOIN [USERS
QGPL] ON [Safety supply transactions].UPUPRF = [USERS QGPL].UPUPRF
ORDER BY [Safety supply transactions].DATE;

 

[Jeff Boyce]

If your incoming data includes a Primary Key field (unique value identifying
each row), do your append query to a table on which you have set Index, No
Duplicates for that field. That way, the append of "old" records fails (no
duplicates) but "new" records are appended.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a macro brings in a current users using ODBC Database from AS400.
if
users are terminated from the system I loose the historical records for
that
user.

I am open to other Suggestions.

Mike

Why? As in "why do you want to move records from one table to another?"

If you include a date/time field in your table, you can use a query to
get a
YTD recordset.

Using separate tables for each YEAR (I'm only guessing here) would be how
you'd probably do it if you were limited to using a spreadsheet. Not
necessary (nor desirable) when using Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want to Append data from an existing table ( Safety supply
transactions)
into a YTD table ( SUPPLIES USAGE YEAR TO DATE) including only new
records
based new records added to Safety supply transactions based on the
date.
Below is the SQL view of what I have set up.

INSERT INTO [SUPPLIES USAGE YEAR TO DATE] ( UPUPRF, [DATE],
[Productpart#],
ProductDescription, Catagory, DEPT, UPTEXT1, UnitPrice, Quanity )
SELECT [Safety supply transactions].UPUPRF, [Safety supply
transactions].DATE, Products.[Productpart#],
Products.ProductDescription,
Products.Catagory, [USERS QGPL].DEPT, [USERS QGPL].UPTEXT1,
Products.UnitPrice, [Safety supply transactions].Quanity
FROM ([Safety supply transactions] INNER JOIN Products ON [Safety
supply
transactions].[Product Id #] = Products.[Product Id #]) INNER JOIN
[USERS
QGPL] ON [Safety supply transactions].UPUPRF = [USERS QGPL].UPUPRF
ORDER BY [Safety supply transactions].DATE;