What am I missing please?

[arrudac30]

Hi all.

After many many years, I've decided to give access a go.

I have done a few things, on excel, with a lot of help from many members from various on-line sources.

Anyway, many years ago, when I first had a taste of access, I was told that I was replicating data, and that this was not good for a good functional database.

A good example, at the time, was post code field. I had a table for post code and a table for customer. In the customer table, I also had fields for post code.

From what I understood at the time, this was like 15 years ago, was that I didn't need to store these post codes in the customer table, as I already had a table for post code.

Can anyone, please, help clarify this?

Many thanks.

Regards,
Albert

[June7]

Something must be stored in Customers table - either the post code or an ID of the record from PostCodes table. Does PostCodes table also have City, State, Country fields?

[arrudac30]

Hi June7.

Customers Table only has the postcode field from the postcode table. Off course it has more fields of its own like address, town, etc..

The postcode table only has auto number (ID) and postcode.

Many thanks for helping.

Regards,
Albert

[arrudac30]

Another thing I have noticed, was that if relationships exist between tables, a field will not accept a value entered if that same value is not in it's related table.

I thought, I am new to this I know, the whole point to this was to, when entering data that does not exist in a related table, that access would add that data to it.

For example, say I entered Essex in the customers postcode field. It won't let me add the record, unless the table postcode has this record (Essex).

Apologies for my ignorance but I am egger to learn.

Kind regards,
Albert

[June7]

That restriction is in place if you set relationships to enforce referential integrity and/or set combobox/listbox LimitToList property to Yes.

Adding records to a 'lookup' table can be done 'on-the-fly' during data entry/edit but this requires code, commonly in a combobox NotInList event. Otherwise, the lookup record must first exist before it can be used.

[arrudac30]

Hi June7.

Many thanks for the time and help given and apologies for the late reply.

I must admit, I am confused with the way access works.

I shall go on reading more about it and youtubing about it.

Kind regards,
Albert

[June7]

The issue is not with the way Access works, it's the way a relational database works. You would encounter this paradigm with any database application.