How do I normalize after monthly update of transaction data

[variance1]

Each month I import transaction data from an XLS file - into Access. How do
I normalize this database when I will be updating with an unnormalized import
monthly?

 

[Douglas J. Steele]

Import your Excel spreadsheet into a temporary denormalized table, then use
a series of queries to take that data and populate the normalized tables.

 

[Jeff Boyce]

I'm not sure you and I use the same definition of "normalize". What do you
mean by it?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[variance1]

Normalized after Access table analyzer + my corrections. So imported data
table has lookups of repeated information. Row example unnormalized =
Yr,Month, Co#, Dept, Expenseacct,expensecateg,Line of Biz,Employee,EmplID,
Trans amt,transdate,postdate,description,transid. so now I have a lookup to
a Month table ID, Month name. How do I keep from having to table analyze
each month? Or am I already doing it wrong? Thanks for the help.

 

[Jeff Boyce]

OK, it sounds like we share some notion of "normalize". Now I need to
understand what you're asking "How do I keep from having to table analyze
each month?"

You provided the fieldnames ... can you provide an example of what kind of
data goes in the fields?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[variance1]

From import:
Table = Trans data
Fields: Yr(2006),Month(1), Co#(1000), Dept(3131),
Expenseacct(56A),expensecateg(Labor), Line of Biz(Consulting),Employee(Doe,
John), EmplID(####),Trans amt(
$100),transdate(1/11/06),postdate(1/15/06),description(comment
field),transid(like autonumber unique to every
record),projectid(alphanumeric), project descrip(alpha), project
category(alpha).

As you can imagine a lot repeats (labor will be a common expense categ as
would travel). So what is the way to correctly update with nonnormalized
data set into Access? What happens if a new Expense category appears in 2
mos?

 

[Jeff Boyce]

Were I given the set of input you described, I would:
1. import into a "temp" table
2. create queries to "parse" the data into my permanent tables

Your "Labor" ([expensecateg]), for example, would be checked against a
lookup table. If that table already held a row for "Labor", I wouldn't want
another. To do this, I'd use an append query and a unique index on the
field that holds "Labor". And when a new category shows up in 2 months, it
wouldn't already be in the lookup table, so the append query would be adding
that new one.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[variance1]

Thanks for the help Jeff,

I think I understand - I will give it a shot - it just seems that lookup
tables are taboo around here.

Were I given the set of input you described, I would:
1. import into a "temp" table
2. create queries to "parse" the data into my permanent tables

Your "Labor" ([expensecateg]), for example, would be checked against a
lookup table. If that table already held a row for "Labor", I wouldn't want
another. To do this, I'd use an append query and a unique index on the
field that holds "Labor". And when a new category shows up in 2 months, it
wouldn't already be in the lookup table, so the append query would be adding
that new one.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

From import:
Table = Trans data
Fields: Yr(2006),Month(1), Co#(1000), Dept(3131),
Expenseacct(56A),expensecateg(Labor), Line of Biz(Consulting),Employee(Doe,
John), EmplID(####),Trans amt(
$100),transdate(1/11/06),postdate(1/15/06),description(comment
field),transid(like autonumber unique to every
record),projectid(alphanumeric), project descrip(alpha), project
category(alpha).

As you can imagine a lot repeats (labor will be a common expense categ as
would travel). So what is the way to correctly update with nonnormalized
data set into Access? What happens if a new Expense category appears in 2
mos?

 

[Jeff Boyce]

ABSOLUTELY NOT! Lookup tables are critical to a smooth running relational
database. Lookup FIELDS, on the other hand, are objects of considerable
derision.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks for the help Jeff,

I think I understand - I will give it a shot - it just seems that lookup
tables are taboo around here.

Were I given the set of input you described, I would:
1. import into a "temp" table
2. create queries to "parse" the data into my permanent tables

Your "Labor" ([expensecateg]), for example, would be checked against a
lookup table. If that table already held a row for "Labor", I wouldn't want
another. To do this, I'd use an append query and a unique index on the
field that holds "Labor". And when a new category shows up in 2 months, it
wouldn't already be in the lookup table, so the append query would be adding
that new one.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

From import:
Table = Trans data
Fields: Yr(2006),Month(1), Co#(1000), Dept(3131),
Expenseacct(56A),expensecateg(Labor), Line of Biz(Consulting),Employee(Doe,
John), EmplID(####),Trans amt(
$100),transdate(1/11/06),postdate(1/15/06),description(comment
field),transid(like autonumber unique to every
record),projectid(alphanumeric), project descrip(alpha), project
category(alpha).

As you can imagine a lot repeats (labor will be a common expense categ as
would travel). So what is the way to correctly update with nonnormalized
data set into Access? What happens if a new Expense category appears in 2
mos?

:

OK, it sounds like we share some notion of "normalize". Now I need to
understand what you're asking "How do I keep from having to table analyze
each month?"

You provided the fieldnames ... can you provide an example of what

kind
of

data goes in the fields?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Normalized after Access table analyzer + my corrections. So

imported
data

table has lookups of repeated information. Row example unnormalized =
Yr,Month, Co#, Dept, Expenseacct,expensecateg,Line of Biz,Employee,EmplID,
Trans amt,transdate,postdate,description,transid. so now I have a lookup
to
a Month table ID, Month name. How do I keep from having to table analyze
each month? Or am I already doing it wrong? Thanks for the help.