Flag a new product that is added to a table?

[KevinSlater]

Hi,

Is it possible to flag a new product that is added to a database table?

I have a Product table in access 2003 (thats linked to an oracel table). This table contains fields including
STK_PART_CODE, STK_DESCRIPTION, ORIGIN_COUNTRY

Sometimes new products are added into the table, when a new product is first added into the table
that originates from china (the ORIGIN_COUNTRY would =CH) then i would like to set a "New External Part" flag to Yes so that people are aware its a newly added part from china.

Is there a way to check when new part codes have been first added to the table and set a flag named "New External Part" to ticked/Yes?
within the table?, maybe via a query

Kevin

 

[namliam]

If you have a copy table with the PKs store somewhere you can check on that.
Or if the product table has a "date created" column you can query that...

Depending on the actually situation more 'tricks' are possible, but these two are the most common and the most simple

 

[KevinSlater]

The product table does not have a "date created" :-(

What method is best? - is using a copy table with the PKs store somewhere and checking that best? if so please could you give some more info on this method, I guess i need to create a query that looks at the original table and the copy table and compare the unique STK_PART_CODE field to see if any new ones exsist, but not sure how to do this, The check needs to be done regulary, ideally daily.

 

[namliam]

Yes...
Easiest may be....
Copy the primary keys to a table and add to that table a column "DateAdded", this is a date/time field with as default value Now()
Add a primary key to the key column, this will prevent adding duplicates and only add new ones.

After adding new ones run a query fetching all products where DateAdded > Date()

This should do the trick...

 

[KevinSlater]

To clarify do you mean copy just the field names (or structure) of the original table into a new table and add a new field: "DateAdded" with the date/time field with as default value Now() or do i need to copy all data into the new table also?. Do you mean add a new primary key to the new table such as create a filed called "Record_ID" set to autonumber?

in the original table (linked to Oracle) the only primary key is STK_PART_CODE

And do you mean create a new query that looks at both the original and the new table fetching all products where DateAdded > Date()?

 

[gemma-the-husky]

if you are trying to see what new data has been added to the oracle table, and there is no field in the oracle table which stores a time stamp then you have a probelm

so the easiest way is to copy the oracle table at any point in time

then whenever you compare the oracle table with your copy, you can identify new records in the oracle table - when you have done whatever you need, update the copy table to gice you the new starting point

 

[namliam]

To clarify do you mean copy just the field names (or structure) of the original table into a new table and add a new field: "DateAdded" with the date/time field with as default value Now() or do i need to copy all data into the new table also?.
<...>
in the original table (linked to Oracle) the only primary key is STK_PART_CODE

ONLY the PrimaryKey of the table + the DateAdded column... is all you need to find the new product.

i.e. STK_PART_CODE

And do you mean create a new query that looks at both the original and the new table fetching all products where DateAdded > Date()?

The idea is to find the primarykey in this "dummy"/"semi-temp" table. Where the date is "new" i.e. created today... then show that record as beeing new...

 

[KevinSlater]

Ok, I see what your saying, although there is a large amount of data in the oracle table to copy into another coparison table at any point in time easily.

I have found a "AUDIT_DATE" field that can be used in the Linked Oracle table, this field shows the date that a part code was last updated ie the last time any changes have been made to that particular record.

Sorry i didnt spot this "AUDIT_DATE" field ealier, but was thinking using this field as a check would be perhaps be better?, but the issue is some records may have originally been created months agao but may have been modified in the last few days and therefore would have the audit date of the last time it was modified, would a check still be possible to check the first time a new unique record has been added using the "Audit_Date" as a check or is it best to stick to the original option.

 

[namliam]

This audit date would trigger a "new" record everytime a change was made I guess.

Unless you have miltiple records with different audit dates (i.e. they do not over write eachother) yes possible.
If not, to bad... not usable.

If you copy over only the Primary key that will not be that much data even if you have billions of products.