Results 1 to 2 of 2
2009-07-22, 12:30 #1
- Join Date
- Feb 2004
- Jacksonville, Florida
- Thanked 0 Times in 0 Posts
I'm trying to create a database that has a link to an ODBC file, & where I can input my variance information against the ODBC data.
The ODBC data gives me (on a wkly basis) what last week's cost/schedule performance was, and the cumulative performance (ie. weekending, dept, aircraft, type, group, wkly plan, wkly actuals, wkly earns, cumulative plan, cumulative actuals, cumulative earns). Each week, the data is updated with the previous week's data...I can not pull up the ODBC connection and see the data for weekending 7/10/09--it is currently only showing 7/17/09...and Monday it will switch to 7/24/09.
I already have a table created in the database that is linked to the ODBC, & I've created a querry that groups the line items by department & sum's up all the wkly/cum cost/schedule performance data.
What I need help with is understanding how to take the data from the querry & place it in a form so that the user can see his department & what his cost/schedule performance was for last week...then from that form, click a button that will take you to another screen that allows you to fill in explanations against your wkly variances & hours associated. (I know how to do the forms & such---it's the table linking part I'm having an issue with). Then this will populate some sort of history table..so that even though the ODBC connection changes, the history of the variances against each particular week will be retrievable (with that weeks data still connected). I've made a makeshift database w/ a series of powerpoint slides to hopefully try to explain better what I need.
I already have the ODBC link established & I have querries from the odbc file, & I have forms...it's the manual input to odbc data connection that I don't know how to do. Thank you!!!!
2009-07-22, 13:11 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
1. Create a table in your Access database with the fields that make up the primary key in the linked table (WeekEnding, Network, Activity, Dept, CoQ and MESST) plus fields for the data to be entered manually. Set the primary key for this table to the combination of the first 6 fields (i.e. the same primary key as the linked table.)
2. Create a form based on the linked table.
3. Create a form based on the new table.
4. Place the from from 3. as a subform on the from from 2. Access should automatically set the Link Master Fields and Link Child Fields properties to the list of fields in the primary key.
When the user starts entering data in the subform, Access will automatically populate the primary key fields with those from the main form, i.e. those from the linked table.
The main form/subform combo will only display subform records for the current main form records. Subform records filled in during previous weeks will be invisible. But if you open the subform by itself, you can view ALL records.
(Of course, it would be easier if you appended the data from the linked table to a table in the Access database. That way, the data will remain available within Access even when the linked table has been updated to show newer data.)