Create multiple records/fields simultaneously

[sraza90]

Good Afternoon



As noob the question sounds, I am extremely handicapped in Access 2010. I am currently trying to create a database for a small company I am interning in. One of the things I would like to do is to update or create multiple fields in different tables simultaneously.

To explain, I have two tables, Production Activity in CY 2012 and Production Activity in $ 2012. Both tables have a field "Date of Activity." In addition to some other fields, Production Activity in CY has a field "Actual Production in CY" and Production Activity in $ has a field "Actual Production in $".

Now I had initially created two forms, each dedicated to one table, however my supervisor wants to use only one form to update both fields.

I also want to know how can I connect/link both of the Date of Activity so that if one changes, the other one changes too. And when I run queries, both "Date of Activity" fields should appear as one (when I want to run query of let's say the maximum or minimum production each month)

Now being a noob and having failed to understand/implement forums having similar problems, I would like to again request some IT messiah to please help me in this issue in the most simplest and noob procedure.

I would be grateful!

Thanks
Abbas

[John_G]

Hi -

Is there a relationship between the two tables, based on some common field (presumably the PK)? This relationship, if I understand correctly, would be one-to-one. If so, you should be able to do what you want to do here.

John

[sraza90]

I do not know how to create a relationship. As in, I can join type through edit relationship tab, but that's all I know. What to do further is a mystery. If creating a relationship can help, then I would really appreciate if you can provide me hot to create this one on one relationship and what steps to take it to advance further.

[John_G]

Hi -

Sorry, I confused you there. I didn't mean have you actually defined a relationship; I meant it more from a data point of view.

Are the data records in the two tables related in some way, i.e. is there data in common between them?

In other words, for a given record in [Production Activity in CY 2012], is there a record in [Production Activity in $ 2012] which relates to the same data, and what is the field which identifies the corresponding records in each of the two tables?

What I'm getting at here is that if you update [Actual Production in CY] in [Production Activity in CY 2012], and you want to update [Actual Production in $] to match, how do you know which record in [Production Activity in $ 2012] to change, i.e. what data do you use to identify that record?

John

[sraza90]

Hey there again.

My apologies if I had confused you.

Well, no I haven't made any relationships between them as I don't know how to properly execute them after I create one. (Another proof of being a noob).

I want the tables to be related, esp in terms of the field Date of Activity. Basically what I want is that both tables are to be recognized with Date of Activity as it is the single factor which is unique.

Again, sorry if I am confusing you.

So here is a little detailed situation.

Two tables with their fields.

Production in CY 2012

-Date of Activity (Date/Time)
-Production Capacity in CY (Calculated)
-Production Target CY (Calculated)
-Actual Production CY (Number)
-Opportunity Lost (Calculated)
-Deviation from Target CY (Calculated)


Production in $ 2012

-Date of Activity (Date/Time)
-Production Capacity in $ (Calculated)
-Production Target $ (Calculated)
-Actual Production $ (Number)
-Opportunity Lost (Calculated)
-Deviation from Target $ (Calculated)


Now I have two things in mind.

A) Somehow connect both Date of Activities. So that when one is updated, the second one automatically updates. (nothing is linked or in relationship right now). So when I run queries, for both tables...let's say for the maximum or minimum value, the database should reference the data of both tables only from one data souce (if you're getting what i mean).

B) A form which helps us fill both tables...I.e One form which has a date (for both fields) and can update Actual Production CY and Actual Production in $ in both tables.

Thanks

Abbas