creating two tables - old entries and weekly new entries

[jameskhan]

Dear All,

Need some urgent help.

I have a table in access which is updated weekly; I need to create two tables from this updated table.

1^st table will consist of all the new entries for the current week

2^nd table will consist of all the entries from the previous week – an amalgamation of all the entries which are not from the ‘current week’ (table)
For example; the table below shows the two entries from last week.

ID	Name
1	Adam
2	Ben

This week I have three new entries New entries

ID	Name
3	Charles
4	Richard

So when I run the same query next week I will get something like this.


Old Entries

ID	Name
1	Adam
2	Ben
3	Charles
4	Richard

New Entries

ID	Name
5	James

How do I get a query /queries which divides up the weeks new entries and also all the old entries
Hope someone can come to my aid.

[JoeM]

You should only have one table, and then a date field that just indicates when each record was added.
Then it is easy to do a query to pull records from any point in time.

[jameskhan]

Thanks Joe, how do I go about putting in a date field that tells me when each record was added?

[JayRab]

how are you updating the table? Manually entry in datasheet views, or are you using a form of some sort?

[jameskhan]

my table is updated through a 'make table' query. the data source is a vast amount of data which is manually inserted from various locations

[JayRab]

I am assuming it is an import. Do any of this locations contain any information regarding DATE of entries?

[JoeM]

In an Action Query (like a Make Table, Append Query, Update Query), it is pretty easy to add a DateAdded field.

In a Make Table Query, just add a calculated field to your query like this:
DateAdded: Date()

Then when you run it, it will add a field named "DateAdded" with the current date in it.

Note, however if you follow my advice and use a single table, I think you may want to use an Append Query to append to an existing table instead of creating a Make Table Query (unless it is a temporary table, as you have to use criteria to determine which records should be added to your final table).

[jameskhan]

I have added the dateadded filed to the query. so if I run the same query tomorrow and I get a new entry, only that new entry will have tomorrow's date and the entries from today will have today's date?

[JayRab]

yes the DATE() function pulls in todays date, so if you run tomorrow it will pull in tomorrows date. I agree with Joe you should probably switch to 1 table and use APPEND query instead of make table.

[jameskhan]

no it doesn't

[jameskhan]

thanks Joe,

sorry im a total novice on access,

I will do an append query, but if everytime i run the query and get todays date date() for all my entries, how can I distinguish between the new ones and old ones?

[JoeM]

OK, if you are going to have one table, and do an Append Query, you will need to do the following:

1. Add a new field to your table and call it DateAdded.

2. Update the DateAdded record for the existing records in your table. You can do this with an Update Query (or manually).
If using an Update Query, just use Criteria to select which records you want to update with what date (if using a date other than today, just enter that date in the DateAdded calculated field in your query).

3. If the new file you want to import contains both old and new records, and you only want to import the new records (since the old ones are already in your table), first import your records into a temporary table, then create an Unmatched Query between your temporary table and final table, so it only returns the new records you want to import (there is an Unmatched Query Wizard that will walk you through how to do that).

4. Add the DateAdded calculated field to the Unmatched Query and change it to an Append Query and run it to populate the new records.

One final note regarding your temporary table. You can either create a new one each time, or just clear the records out each time before importing your new file. You can even create a Macro to clean out those records with a click of the button. You would just use the RunSQL command with this line of code:

DELETE [TemporaryTableName].* FROM [TemporaryTableName];