calculated address

[ClaraBarton]

I have a nursery... Lanes (Rows) of trees, spaces (holes) for each tree. Each Tree has an address 34:125 or 17:62 (Lane:Space).. This has worked well for many years. Suddenly we asked the daughter to do data entry. She added duplicate addresses. My fault so I'm reworking the back end. Should the address be a calculated field within the table? We never want duplicate addresses but we do have many duplicate lanes and spaces; 14:125, 14:126 etc. Or should I just limit the additions on the form that we use to add addresses?

 

[Gasman]

Why not a Lane field and a Spaces field, with unique combined index from both?

 

[ClaraBarton]

that's a good idea. I have to research combined field indexes.

 

[Gasman]

that's a good idea. I have to research combined field indexes.

Start at 7:40

 

[ClaraBarton]

got it! Thanks.

 

[Ken Sheridan]

Be sure to have a referenced Lanes table, with a single column Lane as its primary key. Then create a relationship between this and the trees table and enforce referential integrity. This will protect the integrity of the database by ensuring that only valid Lane values can be entered into the trees table.

 

[Isaac]

I have a nursery... Lanes (Rows) of trees, spaces (holes) for each tree. Each Tree has an address 34:125 or 17:62 (Lane:Space).. This has worked well for many years. Suddenly we asked the daughter to do data entry. She added duplicate addresses. My fault so I'm reworking the back end. Should the address be a calculated field within the table? We never want duplicate addresses but we do have many duplicate lanes and spaces; 14:125, 14:126 etc. Or should I just limit the additions on the form that we use to add addresses?

I'd solve it on the FE + the BE. Strict database theory would definitely suggest you solve it on the back end, as Gasman and you have concluded. But on the FE you should also trap that error and display something graceful to the user and as Pat Harman would probably say, the Before_Update event is the gold standard for a place to do this.