READ DESCRIPTION CAREFULLY. YOU MUST MSG ME SO THAT I CAN SEND LINK TO RRMILK.MDB FOR YOUR REVIEW. ASK ME QUESTIONS AS NEEDED BEFORE FINALIZING BIDS. I WANT TO COMMUNICATE BY SKYPE OR PHONE TO INSURE CLARITY PRIOR TO JOB BEING STARTED. FTL
I have a number of excel .dif files that need to be imported into a new table into an access db (I have access 2003) so that various reports can be created and I have an existing Access 2003 db that needs some formulas changed.
Note: Decision will be based on your programmer rating history, ability to directly communicate and price.
==== TOTALLY NEW PORTION ========
Specifics of job:
1. table name: tblEvents
2. Form name: frmImportEvents
3. Model form: frmImportHist
4. Add Field: Alpha (10 char) CowNo and populate with characters from file name prior to first “-”.
5. Add Field: Numeric (8 char) LactNo populate based on formula below.
6. This will link into the tblCombined by the cow number.
7. Field names
Excel into access
Date becomes Dt
Event becomes Event
Event Info stays Event Info
Comment becomes Event Comment
Additional fields:
CowNo comes from file name as described above
FileName is the file name for that imported record
ImportDt is the date this record was imported
8. Formula:
LactNo.
A. If exists, On Date where Event = Entry, LactNo = 1 (prior to this occurance LactNo is 0 and after follows rules below.)
B. LactNo remains 1 until, Event = Calving (after an Event = Dry) then increment by 1 (ie. becomes 2).
C. LactNo remains the same until, Event = Calving (after an Event = Dry) then increment by 1.
===== REVISING EXISTING ACCESS PROGRAMMING IN RRMILK.MDB =========
Fixes in RRMilk Access db: [Contact me via PMB to get download link]
[ Overall - (CowNo + Date) is Unique in tblDaily, tblHist & tblCombined and use this as the linking field ]
O – Rename RRMilk for development purposes to RRMilk-dev [to be renamed back when finished.]
1. Add into the Combined Form a date field for “Use Hist today/prior:” [field A-date]
2. Add into the Combined Form a date field for “Initial Study Date:” [field S-date]
3. Add into the Combined Form a date field for “No. Missing Daily Days:” [field-M-Days]
4. Add into the Combined, Daily & Hist Tables an indexed alpha-numeric field for CowNo + date called COWDT
5. Removed field error tag and any formulas related to it.
6. Set Up Easy way to choose between 1 of 2 default sorts:
A. Day/Cow sort = 1st Dt Descending, 2nd CowNo Ascending
B. Cow/Day sort = 1st CowNo Ascending, 2nd Dt Descending
In combined table (formulas trigger by the Combined Form),
1. Cause the DailyYld to be the Milk field from tblHist for all dates less than or equal to A-Date ELSE use the DailyYld from tblDaily
2. Pull the 1st occurrence of a new LactNo (1,2,etc) from the tblEvents
3. Calculate the DIM by incrementing 1 per day from the initial number pulled in #2 above.
4. Add date field, placed left of ID, populate with date when Event = Birth from tblEvent
5. Add text field (10 char), “DataSrc”, populate with ‘Daily’ when any data on that date for that cow (ea cow should have 1 unique row for Cowno + date) comes from tblDaily for that cowno + date row else populate with ‘Hist’.
6. Change EntryDt formula – currently not correct – to the following:
A. For a given cow, the 1st date occurrence of Grp = 2 or 4 after S-Date
7. Change ExitDt formula – currently not correct – to the following
A. Based on Sort, 1-CowNo, 2-Dt then If EntryDt greater than 0 and the preceeding [M-Days] of records DataSrc = “Hist” then ExitDt = 1st Day DataSrc changes from Daily to Hist.
Ask me for an excel example if needed.
Change field widths in Combined table:
1. CowNo – add 4
2. DailyYld add 2
3. GynStatus add 12
Conditions to cover:
1. Existing data – in combined table.
– Zap and Recreate Combined table each time the form is run.
2. Cows in tblHist or tblDaily but not in tblEvent – Pull LactNo & DIM data from tblDaily for those dates ELSE leave blank.
In Formula in the tblDaily form:
1. Daily import – change the date imported (drawn from the file name) from the date in the file name to (date in the file name minus 1).
. i have attached a .txt file with the 12 sites that i want you to review
