Many Subforms Verses One Large Subform

  • Thread starter Thread starter RedRider
  • Start date Start date
R

RedRider

I’m creating a database application for billing clients. The Main
form has the Client information, customer #, address, etc. The
subform, with a one to many relationship, is the monthly billing
details. This application creates many different types of invoices
from many different fields. The problem is I can’t fit all the
different fields on one form so I’ve created a Tab Control that fits
on one subform. There are about 100 fields total on this subform but,
for example, Invoice # 1 only uses 15 of these fields (tab 1) and
Invoice # 2 (tab 2) uses 5 common fields (used on all invoices) and 10
different fields than Invoice # 1. This goes on and on like this and
like I said only 5 fields are common. So when I create Invoice # 1
only 15 fields are used out of the 100. All the other 25 invoices are
the same only using a few common fields. There are lots of blank
fields in each reacord.

My question is would it be better to create 25 separate subforms (a
different subform on each tab), with 25 different tables to hold the
data or keep doing what I'm doing and create one large subform (100
fields, maybe more later)? I will also have to create many queries to
create reports that span all subform data. I thought I could simplify
building queries by having one large subform but maybe not.

Any suggestions? Any help would be appreciated.
 
Hi Red,

to add to what Bonnie said ...

if you have 100 fields in your table, it is probably not normalized. It
is rare for a normalized data structure to have a table with more than
30 fields.

Once you normalize your data structure, then you can use a different
subform for each table.

When you say you have different invoice types, what is some of the
information that varies?

~~~

as for reports, don't worry about them until your data structure is
right -- then you will find that they aren't as complicated as you may
have thought <smile>

for basic information on Access, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training


*
(: have an awesome day :)
*
 
Hi Red,

to add to what Bonnie said ...

if you have 100 fields in your table, it is probably not normalized.  It
is rare for a normalized data structure to have a table with more than
30 fields.

Once you normalize your data structure, then you can use a different
subform for each table.

When you say you have different invoice types, what is some of the
information that varies?

~~~

as for reports, don't worry about them until your data structure is
right -- then you will find that they aren't as complicated as you may
have thought <smile>

for basic information on Access, read this:

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

Warm Regards,
Crystal

remote programming and training

  *
    (: have an awesome day :)
  *
Click to expand...

Like any form there is only 5 or 6 different data types but one form
may ask for 6 different types of charges for a service, where another
invoice on needs two types, different than the first 6. Each invoice
has types of service and hourly rate for that service and additional
charges and fees according to client needs.

I like Bonnie's idea of using the same field for different charge
types but Crystal I also know the tables are not normalized so I'll
have to see how everything fits.

Thanks to both of you for the help.
 
Hi Red,

you are just asking for huge headaches down the road if you do not
structure your data well now -- before you invest a lot of development time.

If an invoice can be multiple types, then you need a related table to
specify each type. If an invoice is only one type, then make the
following table:

InvoiceTypes
- InvTypID, autonumber
- InvType, text

and store this in the Invoices table:

- InvTypID, long integer, FK (foreign key) to InvoiceTypes

If the invoice type is actually a charge type, then do not call it
InvoiceType, call it ChargeType. InvoiceCharges should be a separate
table so you can have one or many charges.

As much as possible, design your tables to be specific yet flexible
enough to accommodate various types of data. For instance, if the
charge is for electricity, you have different units than if it is for
water -- but the basic information can have the same structure.

Please tell us more about your data and we can give you more ideas


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Hi Red,

you are just asking for huge headaches down the road if you do not
structure your data well now -- before you invest a lot of development time.

If an invoice can be multiple types, then you need a related table to
specify each type.  If an invoice is only one type, then make the
following table:

InvoiceTypes
- InvTypID, autonumber
- InvType, text

and store this in the Invoices table:

- InvTypID, long integer, FK (foreign key) to InvoiceTypes

If the invoice type is actually a charge type, then do not call it
InvoiceType, call it ChargeType.  InvoiceCharges should be a separate
table so you can have one ormanycharges.

As much as possible, design your tables to be specific yet flexible
enough to accommodate various types of data.  For instance, if the
charge is for electricity, you have different units than if it is for
water -- but the basic information can have the same structure.

Please tell us more about your data and we can give you more ideas

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    (: have an awesome day :)
  *







- Show quoted text -
Click to expand...

Thanks Crystal. I'm jumping back in to this project so I may ask for
more advice later.
 
Hi Red,

you're welcome ;)

Since you need to get your feet wet with Access again, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal
remote programming and training

*
(: have an awesome day :)
*
 
Back
Top