Assistance re: Appending and Updating with most time-effective data.

[HSXWillH]

I've looked through the site and not found what I'm looking for here. I am not code-versed or anything like that so my skills are rudimentary at best. I'm using Access 03 on a Windows Vista environment.

My question is about how to minimize data fields from large data-dumps in order to give myself the most recent recordset and to eliminate all older, out of date records.

I have 7 bulk tables of data imported daily from a horse-racing text dump; of those 7 tables, the data is parsed and distributed into about 12 actual data tables, upon which I clear the Bulk tables out and begin fresh the next entry.

The one in particular involving my question is BulkTrainer, consisting of the following fields: RCDate, Trainer, Category, Starts, WinPct, and ROI.

Each day, a trainer may have several records entered in this type of format:

RCDate     Trainer           Category   Starts WinPct  ROI
9/7/2007   McClure Diane L   Sprint        45   0      0
9/7/2007   McClure Diane L   Rte/Sprint     5   0      0
9/7/2007   McClure Diane L   MdnClm        29   0      0
9/7/2007   McClure Diane L   Dirt          46   0      0
9/7/2007   Smith Jon         Dirt          12   8      2.00

The 2 fields that are key identifiers (I don't have a Primary Key) to each record are the Trainer & Category, the rest are ever-changing whenever they happen to be in use again.

I run an append query entitled TrainerAppend that exports all the daily information to the master data table, MstrTrainer.

What I want is a method of appending new records and updating existing records on the basis of newest date for each trainer/category combination along with the relevant Starts/WinPct/ROI data for that respective date.

Example: the above data already exists in the MstrTrainer table, and in BulkTrainer, I add:

RCDate Trainer Category Starts WinPct ROI
9/8/2007 McClure Diane L Dirt 47 0 0

I want the MstrTrainer table to have the following information upon conclusion:

RCDate     Trainer           Category   Starts WinPct  ROI
9/7/2007   McClure Diane L   Sprint        45   0      0
9/7/2007   McClure Diane L   Rte/Sprint     5   0      0
9/7/2007   McClure Diane L   MdnClm        29   0      0
9/8/2007   McClure Diane L   Dirt          47   0      0
9/7/2007   Smith Jon         Dirt          12   8      2.00

How would I accomplish this with my limited skills in coding and such? Thank you in advance and I hope I posted properly for the forum.

Joseph

[NeoPa]

This sounds as if it may be even easier than at first glance.

Are newer records always related to newer dates?

[HSXWillH]

This sounds as if it may be even easier than at first glance.

Are newer records always related to newer dates?

Yes, I actually found something a few days later on this; it was much easier than I was making it. Using an update/append query, it was easily solved. Sorry for not noticing your question until now.

[NeoPa]

No worries Joseph.

I'm glad you found your solution anyway :)

I suspect it was on lines similar to :

1. Produce a query (qryA) which selects only the latest record from the bulk table for each unique record (Trainer / Category) in the main table.
2. Run query (qryB) which links qryA to your main table (INNER JOIN on Trainer / Category) and updates the date and other data to reflect the record from qryA (bulk table).
3. Run query (qryC) which links qryA to your main table (LEFT JOIN on Trainer / Category WHERE [main table].Trainer is null) and appends the qryA record into the main table.