Spreadsheet import/update to normalised database.

[Geo]

Now it has quietened down a bit in here, a little question...

I took a spreadsheet and imported it to Access (2003), located the fields that
need to be split off e.g MemberType, MemberStatus and created separate tables
replacing the fields with MemberType_ID and MemberStatus_ID.
Every month, the spreadsheet is circulated again from Head Office and I can
import it to a separate table and show the user e.g. new members, unpaid subs
etc by using a query including all required tables.
My question is how do I update the database when the fields no longer match the
spreadsheet? Is it possible with some sort of query or do I have to use VBA to
locate e.g. MemberStatus_ID by searching the Status table?

 

[John W. Vinson]

Now it has quietened down a bit in here, a little question...

I took a spreadsheet and imported it to Access (2003), located the fields that
need to be split off e.g MemberType, MemberStatus and created separate tables
replacing the fields with MemberType_ID and MemberStatus_ID.
Every month, the spreadsheet is circulated again from Head Office and I can
import it to a separate table and show the user e.g. new members, unpaid subs
etc by using a query including all required tables.
My question is how do I update the database when the fields no longer match the
spreadsheet? Is it possible with some sort of query or do I have to use VBA to
locate e.g. MemberStatus_ID by searching the Status table?

An Update and/or Append query will do the job (update to replace existing
values, append to add new ones). The details will depend on the actual
structure of your tables; you'll almost certainly need more than one query
(one for each table you're updating), and you may need some additional
indexes.

One very real problem with a table of members is reliably identifying new
members. You CANNOT go by names, since people's names are not unique (I know
three guys named Fred Brown). You'll need some provision to identify such in
the spreadsheet, and it may be difficult!

 

[Geo]

Would you consider using a Text field for the MemberType_ID, instead of a
number?

There's nothing that says you have to use a hidden number field. Where there
is a simple, natural key (e.g. where a category name is unique, brief, and
required), why substitute a surrogate key? That just complicates things
unnecessarily.

Thanks for that - I was very surprised when I read it - obviously I have taken a
narrow view and could not see the wider picture.
Working on chnging to that method now...

Many thanks for taking the time to reply - sorry I did not get back sooner due
good weather and outside house maintenance required.

 

[Geo]

On Wed, 16 Jun 2010 10:50:20 -0600, John W. Vinson

Thanks for the reply - been busy with manual work.

An Update and/or Append query will do the job (update to replace existing
values, append to add new ones). The details will depend on the actual
structure of your tables; you'll almost certainly need more than one query
(one for each table you're updating), and you may need some additional
indexes.

Understood - update and append query works fine if I keep the same structure as
the spreadsheet but, as you say, it gets complicated when the structure is
different.

One very real problem with a table of members is reliably identifying new
members. You CANNOT go by names, since people's names are not unique (I know
three guys named Fred Brown). You'll need some provision to identify such in
the spreadsheet, and it may be difficult!

They /almost/ have that covered by using a unique membership number.
Unfortunately they have completely duplicated rows in the spreadsheets where one
member has (say) 4 email addresses - so another work-round required.

 

[pietlinden]

On Wed, 16 Jun 2010 10:50:20 -0600, John W. Vinson


Thanks for the reply - been busy with manual work.


Understood - update and append query works fine if I keep the same structure as
the spreadsheet but, as you say, it gets complicated when the structure is
different.


They /almost/ have that covered by using a unique membership number.
Unfortunately they have completely duplicated rows in the spreadsheets where one
member has (say) 4 email addresses - so another work-round required.

this is one case where a UNION query might help... provided you don't
have to do this very often... (or more than once)
UNION will remove duplicate values, so you could do something like

SELECT MemberID, e-mail1 As EMail
FROM mytable
WHERE e-mail1 IS NOT NULL
UNION
SELECT MemberID, e-mail2
FROM mytable
WHERE e-mail2 IS NOT NULL
UNION
....

then you could create an append query to write to a table of e-mail
addresses.