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?

--
Geo

On Wed, 16 Jun 2010 13:50:19 +0100, Geo <hw9j-(E-Mail Removed)> wrote:

>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!
--

John W. Vinson [MVP]

On Wed, 16 Jun 2010 22:12:54 +0800, "Allen Browne" <(E-Mail Removed)>
wrote:

>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
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

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.

--
Geo

On Jun 18, 4:56*am, Geo <hw9j-s...@dea.spamcon.org> wrote:
> On Wed, 16 Jun 2010 10:50:20 -0600, John W. Vinson
>
> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>
> 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.
>
> --
> Geo

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.