Adding Records in Relational Table

[Kicker]

Sorry if you read this on MrExcel, but I haven't gotten any response so I thought I would try here

This one could be for the guru's.

Situation:
I have access to a set of Oracle databases Due to a lot of company restrictions, I do not have "write" priviledges through Access and I have absolutely no way of getting NIS to modify the table structure.

I managed to get Access authorized on my work computer and am easily reading the data I need. Therefore, I have 5 "Linked" tables from which I have several queries to present the data I need for some special analysis and reports.

I do have the ability to create and maintain local and personal tables. Therefore, If I have OracleTable from the network and an AccessTable locally, I can create a relationship between them and locally add some fields I need. I understand that the "local" table will grow and grow and grow but will only show the records related to the data records from the OracleTable.

Is there a way to "automatically" add records to the AccessTable? For example:

One or more records are added to the OracleTable. When I run my sql, there will be several OracleTable records without matching AccessTable records. Some of the fields I want to add would be the days of the week.

Ttfn
Kicker

 

[Pat Hartman]

I have OracleTable from the network and an AccessTable locally, I can create a relationship between them

Creating relationships between tables in different databases is meaningless since you can't enforce RI.

I understand that the "local" table will grow and grow and grow but will only show the records related to the data records from the OracleTable.

??? The local table will contain whatever rows you add to it. It will not be constrained by the contents of the Oracle table since RI is not enforced between the tables.

Is there a way to "automatically" add records to the AccessTable?

No, but the way you would handle a "sparse" relationship (where the many-side, in your case the local Access table may not contain a matching value) is to use a Left Join rather than an Inner Join. Let the Oracle table be the "left" table. The Left join will return all rows from the Oracle table even when there is no matching row in the local Access table.

Some of the fields I want to add would be the days of the week

If this is because you want to store calculated values, find another way. A query is better because you won't have to worry about update anomolies.

 

[Kicker]

The OracleTable has these fields among other.
WorkOrderNumber
BeginningDate
BeginningTime
EndingDate
EndingTime
Days1
Days2
Swing1
Swing2
Grave1
Grave2

If the day is broken into 6 4-hour periods, I can use the Day1 etc. fields to specifically indicate which 4-hour period each day will require work.

What I can't do is determine which DAYS of a period are valid work days. For example: The WorkOrder begins on Jan 10 and ends on Feb 20. They are only working the job on Tue, Wed and Fri each week.

I know I can create a LocalTable with WorkOrderNum, Mon, Tue, etc. and manually enter True's in the appropriate field. Using a SubForm, I can move through the OracleTable and use a find to get the appropriate Localtable record. If not found, I can always add a record at this time.

I'm thinking there might (hopefully) be a way to automatically add the record and use a query to match them up.

A person can always home.

ttfn
Kicker