one to many

[Dummy]

I have a Main Table recording Employee Info
Fields lets say: Name, Sirname,Address, ChangeDateID

i have second table to track changing address
fields lets say: ChangeDateID, changeDate, AddressChange

i created a subform of second table.
i created a Main form with RecordSource the Main Table(first table), then

loaded the subform to be viewed as Datasheet.

Now when i run the form, i add name Nick, Sirname, Address
now i add change date 1, address then changedate 2, address

then when i run back the form again i dont see under name NICK the 2 changing address that i added.

ChangeDateID in the Second Table is the primary key and its autonumber
and ChangeDateID in the first table is done relationship One to Many with the

second table.

Dummy Question: under each Name i want to see many changing addresses, and

when i change record and come back to the same name again i see the changing

addresses again , and i want to add new changing addresses from there.i know that it has something to do with the relationship types any clue?

sorry for this dummy question but can anyone help please?

Thank you

 

[ColinEssex]

are you changing the address on the main form?

Col

 

[Dummy]

Yes i am changing address in main form, what im thinking is to list more changing addresses by date in the Main form Under ONE Employee Name

 

[ColinEssex]

So if you are overwriting what is in the main table - how are you posting the "old" address to table2? prior to changing address1

Col

 

[Dummy]

Colin maybe u got me wrong
in main table there is name sirname and address current of employee

but in futer lets say the address of this employee changed so i am using second table now to fill...and in futer also maybe 3rd time changing address , so again second table adding records, its just that i want those records to appear in the main, i know that it has something to do with relationship..

its like
Table 1
name
Sirname
ChangeID

Table 2
ChangeID(autonumber)
Change date(many records for one Name)
Change address (many records for one Name)

am i doing the correct thing to pick Change ID as a primary Key, and one to many relationship between changeID's ?

 

[ColinEssex]

Ok, you want to record any change of address into table2 as a history of previous addresses.

Just thinking quickly, I would do it like this

Have the main table like this


MainTable
IDNo (autonumber)
Forename
Surname
AddressLine1
AddressLine2
etc

Table 2
IDNo (not autonumber)
Change date
AddressLine1
AddressLine2
etc

Then on the main form have the address fields locked so thay cannot be overwriten. Put a button on the main form called "Change Address". Have some code behind it to post the data to table2 and unlock the address fields so they can now be safely overwritten (the data is already in table2 now)

The Main / Child link is the IDNo which is used as the link for the SubForm to show the previous addresses for the correct employee.

There may be easier ways to do it that others may suggest.

Col