Update query or create new table query or ??

[JWCrosby]

I have two tables. One is a list with two fields, one a number (unique), the
other a description (also unique):
1 Smith
2 Jones
3 Miller
4 Cobb
Etc.

The other table has 5 fields; one an account number (primary key), the other
four hold codes related to the above table:

Acct # Code1 Code2 Code3 Code4 Code5
123 1 2 3 3 2
456 2 2 2 2 2
789 4 3 2 1

What I need to do is to “convert†the code fields to the corresponding name
fields, so the table would look like this:

Acct# Code1 Code2 Code3 Code4 Code5
123 Smith Jones Miller Miller Jones
456 Jones Jones Jones Jones Jones
789 Cobb Miller Jones Smith [blank]

I haven’t been able to figure out how to either do an update query, or
create a new table that will give me the results I need.

(The second table is inherited from a larger non-Access donor database
program and I cannot control how it gives me the fields.)

Thanks in advance!

Jerry

 

[Amy Blankenship]

First, whenever you see Field1, Field2, Field3, Field4, etc. in a table, you
know you have a data design problem. So what you should have is

AcctCodes
============
AcctCodeID
AcctNo
Code


Then the table contains this

AcctCodeID AcctNo Code
======================
1 123 1
2 123 2
3 123 3
4 123 3
5 123 2
6 456 2
7 456 2
8 456 2
9 456 2
10 456 2
11 789 4
12 789 3
13 789 2
14 789 1

Note we no longer have an extra empty space for account 789 just because it
has 4 codes instead of 5.

Once you have this table structure set up, go into the Relationship window
and drag the Code field from your PeopleCode table to your AcctCode table.

Then, open the Query builder and show both tables. Drag the AccountNo and
the PersonName field down to the grid. Beneath AcctNo, you should see a row
called "sort". Click on it, and you should see a popup. Select Ascending.

HTH;

Amy

 

[JWCrosby]

I'm not sure how to reply, Amy. Please re-read my post. I inherited the
second table from another program, so I can't go back and ask it to "give it
all to me in a totally different format." I cannot restructure that table
the way you seem to be suggesting.

Secondly, where did I use "Field1, Field2," etc.? Are you referring to my
use of "Code1" "Code2" etc.?

If you need to know further details, here they are: The second table in my
post is a listing of donor's history, giving the projects they designated
their last five donations to. Projects are given a number code, thus the use
of field names "Code1", "Code2" etc. Maybe I should have labeled it
"ProjectCodeForDesignationForFirstGift" and
"ProjectCodeForDesignationForSecondGift" etc. I apologize for trying to save
keystrokes.

Each donor has a unique account number. That's the Acct # listed in the
second table I described.

Some donors will have given to the same project for their last five
donations (donor #456 in my example). Some will have mixed them up (donor
123 in my example). Others may have only made 4 donations in the past (donor
789 in my example).

I know the names that correspond to the project codes, thus the first table
described in my post.

When I convert the project code to a name, I don't care if I have
duplicates. In the case of donor #456, it's helpful for us to know that
"this donor has given five times and always to the same project."

So, does that give you better information in order to help me. What more
would be helpful?

Jerry

First, whenever you see Field1, Field2, Field3, Field4, etc. in a table, you
know you have a data design problem. So what you should have is

AcctCodes
============
AcctCodeID
AcctNo
Code


Then the table contains this

AcctCodeID AcctNo Code
======================
1 123 1
2 123 2
3 123 3
4 123 3
5 123 2
6 456 2
7 456 2
8 456 2
9 456 2
10 456 2
11 789 4
12 789 3
13 789 2
14 789 1

Note we no longer have an extra empty space for account 789 just because it
has 4 codes instead of 5.

Once you have this table structure set up, go into the Relationship window
and drag the Code field from your PeopleCode table to your AcctCode table.

Then, open the Query builder and show both tables. Drag the AccountNo and
the PersonName field down to the grid. Beneath AcctNo, you should see a row
called "sort". Click on it, and you should see a popup. Select Ascending.

HTH;

Amy

I have two tables. One is a list with two fields, one a number (unique),
the
other a description (also unique):
1 Smith
2 Jones
3 Miller
4 Cobb
Etc.

The other table has 5 fields; one an account number (primary key), the
other
four hold codes related to the above table:

Acct # Code1 Code2 Code3 Code4 Code5
123 1 2 3 3 2
456 2 2 2 2 2
789 4 3 2 1

What I need to do is to "convert" the code fields to the corresponding
name
fields, so the table would look like this:

Acct# Code1 Code2 Code3 Code4 Code5
123 Smith Jones Miller Miller Jones
456 Jones Jones Jones Jones Jones
789 Cobb Miller Jones Smith [blank]

I haven't been able to figure out how to either do an update query, or
create a new table that will give me the results I need.

(The second table is inherited from a larger non-Access donor database
program and I cannot control how it gives me the fields.)

Thanks in advance!

Jerry

 

[Amy Blankenship]

I'm not sure how to reply, Amy. Please re-read my post. I inherited the
second table from another program, so I can't go back and ask it to "give
it
all to me in a totally different format." I cannot restructure that table
the way you seem to be suggesting.

Are you saying that you are pulling it in from a database you don't own?
That's a bummer. Still, you could choose to view it in a normalized view
with a UNION query.

Secondly, where did I use "Field1, Field2," etc.? Are you referring to my
use of "Code1" "Code2" etc.?
Yes.

If you need to know further details, here they are: The second table in my
post is a listing of donor's history, giving the projects they designated
their last five donations to. Projects are given a number code, thus the
use
of field names "Code1", "Code2" etc. Maybe I should have labeled it
"ProjectCodeForDesignationForFirstGift" and
"ProjectCodeForDesignationForSecondGift" etc. I apologize for trying to
save
keystrokes.

Whoever designed the database you're bringing in is applying a spreadsheet
mentality to a database. This is unfortunate. So really whenever someone
donates another gift, you have to move everything over so you can have space
for a new one? That completely sucks. With the table structure I gave you,
you could simply select the top 5 records and away you go. You have my
every sympathy being forced to work with such a crap structure and powerless
to fix it.

Each donor has a unique account number. That's the Acct # listed in the
second table I described.

Some donors will have given to the same project for their last five
donations (donor #456 in my example). Some will have mixed them up (donor
123 in my example). Others may have only made 4 donations in the past
(donor
789 in my example).

So
1 Smith
2 Jones
3 Miller
4 Cobb

Are projects, not donors?

I know the names that correspond to the project codes, thus the first
table
described in my post.
OK.

When I convert the project code to a name, I don't care if I have
duplicates. In the case of donor #456, it's helpful for us to know that
"this donor has given five times and always to the same project."

So, does that give you better information in order to help me. What more
would be helpful?

You can choose to do this one of two ways. First, you can follow a two-step
process where you "fix" the table design with a UNION query, then use that
query instead of the table as I described before. You can't use the QBE
directly to make UNION queries, so to do this you'll need to open it and
decline to show any tables. Switch to SQL view and delete the select
statement there. You'll need to type in something like:

(SELECT AcctNo, FirstProjectCode AS Project FROM AccountProjectCodes)
UNION ALL
(SELECT AcctNo, SecondProjectCode AS Project FROM AccountProjectCodes)
UNION ALL
(SELECT AcctNo, ThirdProjectCode AS Project FROM AccountProjectCodes)
UNION ALL
(SELECT AcctNo, FourthProjectCode AS Project FROM AccountProjectCodes)
UNION ALL
(SELECT AcctNo, FifthProjectCode AS Project FROM AccountProjectCodes);

Then save that query and use it as previously described.

Or, you can choose to have a ton of subselect queries, which won't perform
well but will allow you to maintain your spreadsheet mentality if that is
what you prefer. You'll again have to do it by hand, due to your
intractable data structure. The query will look something like this:

SELECT AcctNum, FirstGift.Project, SecondGift.Project, ThirdGift.Project,
FourthGift.Project, FifthGift.Project FROM AccountProjectCodes,
(SELECT Project FROM Projects WHERE Project.ProjectID =
AccountProjectCodes.FirstProjectCode) AS FirstGift,
(SELECT Project FROM Projects WHERE Project.ProjectID =
AccountProjectCodes.SecondProjectCode) AS SecondGift,
(SELECT Project FROM Projects WHERE Project.ProjectID =
AccountProjectCodes.ThirdProjectCode) AS ThirdGift,
(SELECT Project FROM Projects WHERE Project.ProjectID =
AccountProjectCodes.FourthProjectCode) AS FourthGift,
(SELECT Project FROM Projects WHERE Project.ProjectID =
AccountProjectCodes.FifthProjectCode) AS FifthGift;

Good luck with it;

Amy