Trouble with normalization

[rocketD]

Hi All,

I've been asked to build what seems like a very simple database, but
trying to figure out how to normalize it and do what I want is driving
me nuts. It's to track invoices, and these are the variables of
interest:

entryDate (autopop with Now())
invoiceNum (unique Invoice ID)
invoiceDate (date invoice generated)
areaID (Geographical area of work)
companyID (company generating invoice)
engineerID (engineer associated with the invoice)
constrRepID (construction rep associated with the invoice)
chargeCode (project identifier for which work was completed)
wbsID (cost center which invoice should be charged to
orderNum (work order identifier)
chargeamt (total cost on invoice)
manhours (total man hours listed on invoice)
projectdesc (description of work done for this invoice)

The relationships are as follows: (where "have" = "be associated
with")
Each invoice can only have one of each of the above variables;
Each company, engineer, construction rep, chargecode, wbs, orderNum
and area can have many invoices;
Each chargecode can have many wbs, engineers, construction reps, work
orders and vice versa;
Each chargecode can have only one area.

Work orders (orderNum) are funds allocated for a series of projects
(chargeCode) and broken down by project categories (wbsID). Work
orders can have several projects for which funds are allocated, and a
project can have several separate work orders.

So the 7 variables I'd break out into normalized "lookup" tables
(e.g., areaID + areaName) would be Area, Company, Engineer,
Construction Rep, Charge Code, OrderNum, and WBS. I would then have 3
bridge tables, to link area and ChargeCode; chargeCode, WBS, and
OrderNum; and then link the two bridge tables together?

The user wants a seamless form that allows data entry for each
invoice, BUT, for each of these 7 variables he wants (a) a drop down
box and (b) to be able to add values to the drop down box if they
aren't in the list. Currently, all of his data is on stacks of hard
copy invoices, and he's got some minions who will be entering it for
weeks.

My problems are:
(1) Using subforms in my experience isn't seamless for data entry,
you have to do funky things like Shift+Tab to advance to them;
(2) I don't know how to make new values entered on the form show up
in BOTH the relevant lookup table AND the invoice table from one form,
and he doesn't want to have to enter information into different forms.

My Questions are:
(1) Am I looking at normalizing these tables wrong - are bridge tables
even required?
(2) Without putting all of the information into one big table (where
it would be just a non-normalized spreadsheet), how do I allow them to
enter new info that adds to drop down boxes as they go?

Any answers, or direction to resources to find answers, would be
highly appreciated. Thanks for taking the time.

 

[Armen Stein]

My Questions are:
(1) Am I looking at normalizing these tables wrong - are bridge tables
even required?
(2) Without putting all of the information into one big table (where
it would be just a non-normalized spreadsheet), how do I allow them to
enter new info that adds to drop down boxes as they go?

1. I don't have time right now to analyze your whole database design,
but it looks like you're at least thinking about it in the right way.
Perhaps someone else can suggest a normalized database design that
meets your requirements.

2. To do "add on the fly" for comboboxes, look at the NotInList
event. You can use a bit of VBA code to prompt the user to ask if
they want to add a new value, then update the lookup table so that the
value is included.

Something like:
http://www.developerbarn.com/access-database-samples/251-combo-box-add-entry-not-list.html

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Cory Dove]

Hi All,

I've been asked to build what seems like a very simple database, but
trying to figure out how to normalize it and do what I want is driving
me nuts. It's to track invoices, and these are the variables of
interest:

entryDate (autopop with Now())
invoiceNum (unique Invoice ID)
invoiceDate (date invoice generated)
areaID (Geographical area of work)
companyID (company generating invoice)
engineerID (engineer associated with the invoice)
constrRepID (construction rep associated with the invoice)
chargeCode (project identifier for which work was completed)
wbsID (cost center which invoice should be charged to
orderNum (work order identifier)
chargeamt (total cost on invoice)
manhours (total man hours listed on invoice)
projectdesc (description of work done for this invoice)

The relationships are as follows: (where "have" = "be associated
with")
Each invoice can only have one of each of the above variables;
Each company, engineer, construction rep, chargecode, wbs, orderNum
and area can have many invoices;
Each chargecode can have many wbs, engineers, construction reps, work
orders and vice versa;
Each chargecode can have only one area.

Work orders (orderNum) are funds allocated for a series of projects
(chargeCode) and broken down by project categories (wbsID). Work
orders can have several projects for which funds are allocated, and a
project can have several separate work orders.

So the 7 variables I'd break out into normalized "lookup" tables
(e.g., areaID + areaName) would be Area, Company, Engineer,
Construction Rep, Charge Code, OrderNum, and WBS. I would then have 3
bridge tables, to link area and ChargeCode; chargeCode, WBS, and
OrderNum; and then link the two bridge tables together?

The user wants a seamless form that allows data entry for each
invoice, BUT, for each of these 7 variables he wants (a) a drop down
box and (b) to be able to add values to the drop down box if they
aren't in the list. Currently, all of his data is on stacks of hard
copy invoices, and he's got some minions who will be entering it for
weeks.

My problems are:
(1) Using subforms in my experience isn't seamless for data entry,
you have to do funky things like Shift+Tab to advance to them;
(2) I don't know how to make new values entered on the form show up
in BOTH the relevant lookup table AND the invoice table from one form,
and he doesn't want to have to enter information into different forms.

My Questions are:
(1) Am I looking at normalizing these tables wrong - are bridge tables
even required?
(2) Without putting all of the information into one big table (where
it would be just a non-normalized spreadsheet), how do I allow them to
enter new info that adds to drop down boxes as they go?

Any answers, or direction to resources to find answers, would be
highly appreciated. Thanks for taking the time.

 

[rocketD]

1.  I don't have time right now to analyze your whole database design,
but it looks like you're at least thinking about it in the right way.
Perhaps someone else can suggest a normalized database design that
meets your requirements.

2.  To do "add on the fly" for comboboxes, look at the NotInList
event.  You can use a bit of VBA code to prompt the user to ask if
they want to add a new value, then update the lookup table so that the
value is included.

Something like:http://www.developerbarn.com/access-database-samples/251-combo-box-ad...

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

Thanks for your advice. The NotInList Event gives me a starting point
and hopefully I'll be able to figure it out from there.

Dara