How do I normalize after monthly update of transaction data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Import your Excel spreadsheet into a temporary denormalized table, then use
a series of queries to take that data and populate the normalized tables.
 
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.
 
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/
 
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?
 
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/
 
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.

Jeff Boyce said:
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 said:
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?
Click to expand...
Click to expand...
 
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/


variance1 said:
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.

Jeff Boyce said:
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 said:
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
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top