By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

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

P: 57
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:
Expand|Select|Wrap|Line Numbers
  1. RCDate     Trainer           Category   Starts WinPct  ROI
  2. 9/7/2007   McClure Diane L   Sprint        45   0      0
  3. 9/7/2007   McClure Diane L   Rte/Sprint     5   0      0
  4. 9/7/2007   McClure Diane L   MdnClm        29   0      0
  5. 9/7/2007   McClure Diane L   Dirt          46   0      0
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. RCDate     Trainer           Category   Starts WinPct  ROI
  2. 9/7/2007   McClure Diane L   Sprint        45   0      0
  3. 9/7/2007   McClure Diane L   Rte/Sprint     5   0      0
  4. 9/7/2007   McClure Diane L   MdnClm        29   0      0
  5. 9/8/2007   McClure Diane L   Dirt          47   0      0
  6. 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
Apr 18 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
This sounds as if it may be even easier than at first glance.

Are newer records always related to newer dates?
Apr 21 '08 #2

P: 57
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.
May 30 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.