Calculated field programing help needed

[WILYWAYNE11]

I have a table that lists the customers address (site address) and mailing address separately. We have included a data base field called "Mailing address same", If the customers address and mailing address are the same (Yes) than the mailing address field is populated with the customers address, thus saving time. We are using a calculated field to address this procedure and it works fine...until the Mail Address Same field is "No". We cannot enter any data into the field at all. I have included the calculated field programing. If there is a way to allow entering data please let me know.
=@IIF ([mail address same]="yes', [site address], " ")
The " " is preventing entry of any data. I can't find a way around this. Excel is much easier that Access

 

[raziel3]

Calculated fields are Read Only. Also, I don't recommend data entry directly to the table. To achieve what you want you may need to use forms.

 

[theDBguy]

This is untested; but instead of using a calculated field, try using that expression in the Default Value property of the mailing address field.

Otherwise, I agree it's best to control this situation using forms.

 

[WILYWAYNE11]

Calculated fields are Read Only. Also, I don't recommend data entry directly to the table. To achieve what you want you may need to use forms.

Thanks for the input....

 

[CJ_London]

A better structure would be a separate table of addresses, linked to customer and with a flag to indicate type of address - as a customer to a company I can have multiple delivery addresses for example.

The flag can be multi purpose - simplistically for example

1=billing
2=delivery

so a customer where the two are the same you could use a flag value of 12

 

[Pat Hartman]

so a customer where the two are the same you could use a flag value of 12

That violates first normal form. An alternate solution is a separate table with as many rows as needed. This can be difficult to maintain since there is no easy way to implement certain business rules using declarative RI. For example, only ONE address can be tagged as the physical address and only one can be tagged as the billing address. But any address can be used for shipping although, one may be the default. A lot of address management revolves around how you need to work with the addresses and how many addresses the typical customer has.

One solution I have used has an address table with multiple addresses per customer. Then in the customer table, i have a column with the ID for the billing address and another column with the ID for the physical address. Then for an order, the user can pick any address from the list as the shipping address. This seems to also violate FNF and technically, it does. But it violates for a reason and the reason is simplicity. It also doesn't have the issue of duplicating data because it doesn't actually duplicate the addresses. The addresses are only stored once. Instead of using code to manage the address types that can only occur once like a billing address, it simplifies it by creating a 2 or maybe 3 instance repeating group to be used to point to billing (type 1), physical (type 2), primary shipping (type 3) - all of which can point to the same row in the address table.

It gets more complicated if you need to support shipping addresses at runtime. Looking at various web applications, you will see that there are also at least two ways to support this. In some cases, they only support a single ship to address. But for large companies like Amazon and Swiss Colony and Omaha Steaks where people frequently order gifts, they allow you to keep an address book and pick multiple addresses from their as you order gifts. From that, you can imagine how their BE databases are organized.

 

[CJ_London]

That violates first normal form

I don't disagree, but rules are made to be broken occasionally and I was trying to keep it simple

 

[Pat Hartman]

Some rules shouldn't be broken. My solution may also break 1NF but differently. Keeping columns Atomic is most important. Granted, mushing individual values together seems easier and more expandable but without delimiters, nothing is ever easy. Also, Not all things actually need to be infinitely expandable. If it weren't for the "One only" rules, I would always just use a many-side table and leave it at that. The problem with the one-only rules is that they are complex to program and you can't implement them using declarative RI and I'm not sure even triggers work. Since you have to limit Billing to ONE address, the issue becomes, how to switch billing to a different address. It takes at least two steps and that is the simple version where the "new" billing address already exists. You may also need to add a new billing address record if you can't just change the address of the old one. That is why rather than attach uses to each address, I added a repeating group that includes a named column for each "one only" address. I'm not sure there are other "one only" addresses besides Billing and Physical. Most apps don't care about physical. But Mailing address is another potential "one only" address. So, my position is that if I'm going to break 1NF, I will break it in the most obvious and straightforward way so that subsequent users won't have to figure out how to handle a non-atomic field with no delimiters.

 

[gemma-the-husky]

Can you not have the process just use the customer address unless there is a separate delivery address?

That way, if the customer changes his address you don't have to update the changed "default" delivery address, which could get overlooked. I am not sure I would even bother with a "same" address flag.

 

[Pat Hartman]

I'm not sure who you are talking to. There are lots of ways to handle this. The simplest is to have both billing address (from the customer record) and a shipping address on the order. The billing address is automatically copied to the shipping address or uses a join to the Customer Record to save space. The Billing address is locked since that can only be changed on the Customer form but the shipping address on the order form (which starts out as a copy of the billing address) can be changed any time PRIOR to the order being shipped.

 

[Cotswold]

To me calculated fields are one of those features that sound like a good idea but are often more trouble than they are worth.
If there was just one instance of a calculated field falling over at a customer site, you'd never prove how or why. So why go there in the first place?