Delivery Addresses

[David M C]

I have a database that handles purchase orders. All the forms etc work
exactly as designed, and the data is pretty well normalised. However,
usability is a bit of an issue.

Each PurchaseOrder has a DeliveryAddressID which is the fk to a
DeliveryAddress table with all the address information in. However, everytime
someone wants to have something sent to a different address, they have to
open another form, enter the new delivery address, give it a memorable
reference so it can be easily identified in the combobox, close that form,
then select the new delivery address from the combobox. I need a better way
to handle the entry of a delivery address, whilst also allowing common
delivery addresses to be stored.

The way I see it, I have three options.

1) Continue as is.

2) Somehow allow the entry of new delivery addresses on the main form whilst
maintaing the DeliveryAddressID 1:n relationship between tblOrder and
tblDeliveryAddresses.

3) Put the delivery address info in the main Order table, then have another
table for saved addresses and write a bit of code for loading/saving those
addresses to the main form (this would mean there is some data duplication,
but would allow new delivery addresses to be entered hassle free).

Ideas?

Thanks,

Dave

 

[TC]

I have a database that handles purchase orders. All the forms etc work
exactly as designed, and the data is pretty well normalised. However,
usability is a bit of an issue.

Each PurchaseOrder has a DeliveryAddressID which is the fk to a
DeliveryAddress table with all the address information in. However, everytime
someone wants to have something sent to a different address, they have to
open another form, enter the new delivery address, give it a memorable
reference so it can be easily identified in the combobox, close that form,
then select the new delivery address from the combobox. I need a better way
to handle the entry of a delivery address, whilst also allowing common
delivery addresses to be stored.

Ok, that is very clearly described.

The way I see it, I have three options.

1) Continue as is.

Nope. You can definitely do it better. (Mind you, that is not to say
that you would /remove/ the existing functionality.)

2) Somehow allow the entry of new delivery addresses on the main form whilst
maintaing the DeliveryAddressID 1:n relationship between tblOrder and
tblDeliveryAddresses.

That's where I'd be headed. You could maybe have some unbound fields
for entry of a new address. The user either (a) selects an existing
address id from the combo box, or (b) enters a new address into the
unbound fields. An appropriate event (proably Form_BeforeUpdate) copies
the new address from the unbound fields, into the address table,
assigns a unique key (somehow - easy if it's an autonumber), then
copies that new address key into the fk field of the current record
just before it is saved.

3) Put the delivery address info in the main Order table, then have another
table for saved addresses and write a bit of code for loading/saving those
addresses to the main form (this would mean there is some data duplication,
but would allow new delivery addresses to be entered hassle free).

I wouldn't. For the simple reason that there are other good solutions
(like the above) that do not require denormalizing.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Tim Ferguson]

The way I see it, I have three options.

4) Display the actual address lines in the combo box. Allow the user to
filter the combo box so that it only displays addresses that are
applicable to the current customer.

3) Put the delivery address info in the main Order table, then have
another table for saved addresses and write a bit of code for
loading/saving those addresses to the main form (this would mean there
is some data duplication, but would allow new delivery addresses to be
entered hassle free).

With a bit of imagination and creative coding, it's possible to create a
fuzzy look up and try to identify the user's "21 St John Ave" with the
record in the database "21 Saint John's Avenue" but it's not always easy!

Hope that helps


Tim F

 

[David M C]

I'm not sure how this would help with inputting new addresses.

When I say "save/load addresses" I mean that common addresses will be stored
in a seperate table still, but will be selectable from a popup and copied to
the Order table.

Dave

 

[David M C]

I think I'll give your idea a go. At least my table structure can stay the
same. However, the reason I tended to go toward idea number 3 is that very
few addresses get used more than once. I really only need to re-use the
address of our head office, and the site office of some of our big jobs.

I suppose a good way of limiting the addresses shown in the combobox would
be to have a yes/no field in the DeliveryAddresses table and another form so
the users can select the addresses they wish to see in the combobox and edit
any relevant information for those addresses. The yes/no field will have a
default vaue of no such that new addresses are never shown in the combobox.
The user could use the system without ever knowing that addresses can be
"saved" and recalled for use later on, but the functionality is there should
they need it.

Dave

Dave

 

[Joshua A. Booker]

David,

Option 4) Use the Not-In-List event to open a pop-up form for entry into the
addresses table. Used properly, this will add the address record to the
addr table and to the combo box.

HTH,
Josh

 

[TC]

Ok, good luck. Post back if you can't get a good method working.

TC (MVP Access)
http://tc2.atspace.com

 

[Tim Ferguson]

Trouble with top-posting is that it's never completely clear what bits of
a reply correspond to which bits of a post. If I have misunderstood,
please correct me:-

I'm not sure how this would help with inputting new addresses.

This was in your OP:

they have to open another form, enter the new delivery
address, give it a memorable reference so it can be
easily identified in the combobox, close that form,
then select the new delivery address from the combobox.

so if the users can see the actual addresses in the combo then they don't
have to remember anything.

When I say "save/load addresses" I mean that common addresses will be
stored in a seperate table still, but will be selectable from a popup
and copied to the Order table.

Okay -- I was imagining a proper related Addresses table. If you have a
simple copy-and-paste source table then life for the developer becomes
much easier. Separate form for maintaining the addresses table is
probably the way to go then.


B Wishes


Tim F