Simple Database, 2 tables

[bratby1980]

Hi,

I am trying to create a basic database using access but I'm still learning at the minute.

I have a text file which contains: customer account code (contains duplicates), customer address type, customer name, and telephone number.

I have created a table by linking to this source file. (If I refresh this file in the future will I lose any data that is linked to it?, if that makes sense). I have created another table for CustomerRegNo and EmailAddress. I don't know how to link these tables together?

I want a form which the user can find a certain record by customer account code, then add a Customer Registration Number and an Email Address.

Sorry I have tried to do this myself but haven't got the foggiest. Can't seem to do anything with the linked table.



Thanks in advance,

Dave

[June7]

If the text file is replaced with another file using same name, the link should remain intact.

Will not be able to edit the text file through link. Same for linked spreadsheet.

If these datasets do not have common field(s), there is no way to associate records from each. So unless CustomerAccountCode and CustomerRegNo are the same data, these datasets have no relationship. Need a field in table for saving CustomerAccountCode.

[bratby1980]

Hi,

Thanks for the reply.

So, in the same table as the CustomerRegNo and EmailAddress I need to put another field for CustomerAccountCode?

Dave

[June7]

Seems so. Then have a form bound to table with subform for the linked text file. Have UNBOUND combobox in main form for searching CustomerAccountCode. If entered code not found, create new record on main form. This can be handled programmatically from the combobox NotInList event.

[bratby1980]

Thanks again,

I'll have a look in to subforms. I'm still stuck on how to link the 2 tables together.

Dave

[June7]

Already described what you need in order to link the datasets.

[bratby1980]

Brill! I got it working.

Thanks again for your help.

Dave

[bratby1980]

Hi,

I have just come across another problem. I want the user to be able to update the phone number and show the latest phone number.

I have added a PhoneNumber field to the table with the EmailAddress & CompanyRegNo & CustomerAccountCode and linked it in the subform. When I change the phone number in the sub form it just reverts back to the one from the text file. How do I keep the latest phone number on display?

Thanks again,

Dave

[June7]

My suggestion was main form bound to table and subform bound to linked text file. The subform can display the associated records from the linked text file but cannot edit that data. So why are you trying to change the phone number from the linked text file? Change data on the main form.