How do I efficiently deal with an exception

[BP522]

I am trying to create a database of school information. I have tables for
the school name, campus address billing address... 95% of schools only have
1 campus and billing address but some do have more than 1 address. It seems
like a waste to make all of the address fields 1 to many relationships and
would require using forms and subforms to enter the data which can get
confusing. Is there a way that I can set up the database in a way that I
can take these exceptional cases into account? I thought about having a
seperate table for exceptions, but I am not sure how I could find the
exception address when I need it for an order.

Thank you

 

[John Vinson]

It seems
like a waste to make all of the address fields 1 to many relationships and
would require using forms and subforms to enter the data which can get
confusing.

If 5% of your data is one to many - then your data is one to many.
That's the reality which your database is attempting to model.

I'd go with the subforms. In a well designed form they shouldn't be at
all confusing! Sure, it's more work - but losing correspondance that
went to the wrong address, and then trying to clean up the resulting
problems, will be vastly *more* work!

John W. Vinson[MVP]

 

[BP522]

Thank you I will do this.

Is there a way that I can set up the subforms so that it looks like 1 big
form to the person who will enter the data?

If it can't be 1 big form can I hide the record numbers somehow?

 

[John Vinson]

Thank you I will do this.

Is there a way that I can set up the subforms so that it looks like 1 big
form to the person who will enter the data?

Sure. I'd suggest NOT using the form wizard (except as a starting
point) - it makes subforms as Datasheets, which can look confusing.
You can open the form you're using as the subform in design view and
change its Default View from Datasheet to either Single Form (if you
want to see one address at a time) or my preference, Continous Forms,
showing one address per row.

If it can't be 1 big form can I hide the record numbers somehow?

sure. Just don't include them on the form! Users should never need to
see Autonumber values at all.

Again, the form wizard gets you from A to B, and on a good day maybe
to C. If you want a good, friendly form you'll need to get into form
design mode yourself and improve things.

John W. Vinson[MVP]