Linking Tables/Relationships Question

[Pluszero]

I am a pretty new user to microsoft access.

Here is the situation:
I have two tables.

Table 1:
- Primary Key = membershipID
- other personal info: name, address, etc.

Table 2:
- Primary Key = membershipID
- payment info

I want an insertion in the Table 1 to automatically create an insertion in Table 2. I have a one-to-one relationship defined between the tables and when I insert in Table 1, the record fails to appear in table 2.

I hope this is possible but I am not sure.

If you could also explain the difference between linking and relationships that would be great.

Any light sheding information on these questions would be greatly appreciated.

Thanks.

 

[zrzr]

Hi !

You have to create a view countaining all the columns of the two tables. As your tables are linked, if you insert a record in the view,in a column from table 1, a record will automatically created in table 2, with the same key.

It worked with my base, but I don't remember if it was a one-to-one relationship...

Sorry for my bad english,I hope I am clear enough !!!

 

[BLROBER2]

I have problem with matching mutiple tables with multiple matches. The problem is an ID # will have 2 rows with different dates for for each row. The goal is to get the cycle time between the two dates. Problem is that that ID # could have two passes each with different dates.

START FINISH
Ex. ID13 5/20/03 5/21/03
ID13 5/22/03 5/24/03

The above is what I want but this is what I get.

Ex. Start Finish
ID13 5/20/03 5/24/03
ID13 5/22/03 5/24/03
ID13 5/20/03 5/21/03
ID13 5/22/03 5/21/03


Does anyone have any ideas on how to get it to match up correctly?

 

[JeremyNYC]

Pluszero,

Access is designed to let you interact with your data through forms and reports. It won't be possible to make it react like that to inserting data directly into a table.

I think you'll probably want to change your relationship to a one-to-many, as I'm assuming each member can make more than one payment. I would put in an autonumber field for PaymentID, but keep the MemberID field in table 2.

Then, build a member form that displays the info from table 1. Then build a payment form that displays the info from table 2. Set it up so it defaults to datasheet view. Put it on the first form, as a subform. Make sure the link child fields and link master fields of the subform control both say MemberID. Then you'll be in business.

You might want to check out the "Fundamentals of Relational Database Design" article on my website. It's got a bunch of great info about how to set up your tables (I didn't write it, so I can say that!).

Jeremy

PS: BLROBER2, it's best to start your own thread, so that there's only one conversation to keep track of in each thread.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.