Append query. letter code to description

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

Guest

I am (trying to) migrate data from one table to another. In the old table
"type" was a letter (D,R, etc.). In the new table I want "Deposit", "Refund"
etc. How do I do this in an append query? How do I convert the letters to
text/description.

tia
 
In one column choose the Type and set critera (D, R), add another column with
Expr: "Deposit" or "Refund" with the column to be updated.
 
I am (trying to) migrate data from one table to another. In the old table
"type" was a letter (D,R, etc.). In the new table I want "Deposit", "Refund"
etc. How do I do this in an append query? How do I convert the letters to
text/description.

tia
Click to expand...

An Append query will migrate data from one table to another, by
"appending" new records into the target table. Why not keep the
one-letter code, though? You could add a new little tiny lookup table
with fields [Type] and [TranType] with rows like

D Deposit
R Refund
C Correction

You could then create a Query joining this "lookup" table to your
transaction table (don't use Microsoft's Lookup Field misfeature
though!)

If you really do want to do this, you can put a calculated field in
your Append query:

NewType: Switch([Type] = "D", "Deposit", [Type] = "R", "Refund",
[Type] = "C", "Correction", <all the valid values>, True, "ERROR")

This will put ERROR into the target field if the type code isn't
recognized.

John W. Vinson[MVP]
 
Thanks for responding.
I don't want to use codes anymore, words are better. And not using a "type"
table, using Value List and hard coded values.

I don't understand what you wrote re: switch.

Part of what makes this difficult is that I am migrating a flat file to a
relational table. The flat file is "Transaction" with 30 columns A#, T#, D#
where A = amount, T = Type and D= Date. I need to put this into rows. ugh!

So, I am writing an append query for each group (ATD), setting criteria for
the various types, (D,R, etc.) and then another column as an expression to
put the textual description in.

Clear as mud?

John Vinson said:
I am (trying to) migrate data from one table to another. In the old table
"type" was a letter (D,R, etc.). In the new table I want "Deposit", "Refund"
etc. How do I do this in an append query? How do I convert the letters to
text/description.

tia
Click to expand...

An Append query will migrate data from one table to another, by
"appending" new records into the target table. Why not keep the
one-letter code, though? You could add a new little tiny lookup table
with fields [Type] and [TranType] with rows like

D Deposit
R Refund
C Correction

You could then create a Query joining this "lookup" table to your
transaction table (don't use Microsoft's Lookup Field misfeature
though!)

If you really do want to do this, you can put a calculated field in
your Append query:

NewType: Switch([Type] = "D", "Deposit", [Type] = "R", "Refund",
[Type] = "C", "Correction", <all the valid values>, True, "ERROR")

This will put ERROR into the target field if the type code isn't
recognized.

John W. Vinson[MVP]
Click to expand...
 
Thanks for responding.
I don't want to use codes anymore, words are better. And not using a "type"
table, using Value List and hard coded values.
Click to expand...

Well, a Value List will work, but if you'll ever be changing them, you
then have a maintenance hassle. A small Table makes a good rowsource
for a combo box as well, and is easier to maintain. Your choice
though!
I don't understand what you wrote re: switch.
Click to expand...

Switch() is a builtin function in Access VBA. Type Ctrl-G to open the
VBA editor (just to get connected to the correct Help file) and select
Help, and search for Switch() for details. Basically it takes
arguments in pairs; goes left to right; and when the first member of a
pair is True it returns the second.
Part of what makes this difficult is that I am migrating a flat file to a
relational table. The flat file is "Transaction" with 30 columns A#, T#, D#
where A = amount, T = Type and D= Date. I need to put this into rows. ugh!

So, I am writing an append query for each group (ATD), setting criteria for
the various types, (D,R, etc.) and then another column as an expression to
put the textual description in.
Click to expand...

A "Normalizing Union Query" is a very slick way to do this all in one
swell foop, without needing to set criteria at all.

Let's say Transaction has a field TransID and fields A1 through A30,
T1 through T30 and so on; and you want to append this data to a
normalized table with fields TransID, SeqNo (1 to 30), Amount,
TransType, and TransDate. You can create a Query in the SQL window:

SELECT Transaction.TransID, (1) AS SeqNo, [A1] AS Amount,
Switch([T1] = "D", "Deposit", [T1] = "W", "Withdrawal",
[T1] = "P", "Payment", [T1] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D1] AS TransDate)
WHERE [A1] IS NOT NULL
UNION
SELECT Transaction.TransID, (2) AS SeqNo, [A2] AS Amount,
Switch([T2] = "D", "Deposit", [T2] = "W", "Withdrawal",
[T2] = "P", "Payment", [T2] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D2] AS TransDate)
WHERE [A2] IS NOT NULL
UNION
<etcetera, all 30 sets of fields>

Save this Query and then base a UNION query upon it.

John W. Vinson[MVP]
 
I hope my previous reply was recorded, but I'm going to use that switch
statement....thanks again

John Vinson said:
Thanks for responding.
I don't want to use codes anymore, words are better. And not using a "type"
table, using Value List and hard coded values.
Click to expand...

Well, a Value List will work, but if you'll ever be changing them, you
then have a maintenance hassle. A small Table makes a good rowsource
for a combo box as well, and is easier to maintain. Your choice
though!
I don't understand what you wrote re: switch.
Click to expand...

Switch() is a builtin function in Access VBA. Type Ctrl-G to open the
VBA editor (just to get connected to the correct Help file) and select
Help, and search for Switch() for details. Basically it takes
arguments in pairs; goes left to right; and when the first member of a
pair is True it returns the second.
Part of what makes this difficult is that I am migrating a flat file to a
relational table. The flat file is "Transaction" with 30 columns A#, T#, D#
where A = amount, T = Type and D= Date. I need to put this into rows. ugh!

So, I am writing an append query for each group (ATD), setting criteria for
the various types, (D,R, etc.) and then another column as an expression to
put the textual description in.
Click to expand...

A "Normalizing Union Query" is a very slick way to do this all in one
swell foop, without needing to set criteria at all.

Let's say Transaction has a field TransID and fields A1 through A30,
T1 through T30 and so on; and you want to append this data to a
normalized table with fields TransID, SeqNo (1 to 30), Amount,
TransType, and TransDate. You can create a Query in the SQL window:

SELECT Transaction.TransID, (1) AS SeqNo, [A1] AS Amount,
Switch([T1] = "D", "Deposit", [T1] = "W", "Withdrawal",
[T1] = "P", "Payment", [T1] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D1] AS TransDate)
WHERE [A1] IS NOT NULL
UNION
SELECT Transaction.TransID, (2) AS SeqNo, [A2] AS Amount,
Switch([T2] = "D", "Deposit", [T2] = "W", "Withdrawal",
[T2] = "P", "Payment", [T2] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D2] AS TransDate)
WHERE [A2] IS NOT NULL
UNION
<etcetera, all 30 sets of fields>

Save this Query and then base a UNION query upon it.

John W. Vinson[MVP]
Click to expand...
 
John,

I'm sorry my first reponse to previous problem was un-recorded. The Union
clause, while most elegant, is too sophisticated for me, but the Switch was a
big help.

I have a new similar problem, need to turn a field wtih multiple codes (as
opposed to just one code) to multiple rows. Eg: Skills = CO;AG;FB, etc. I
need three rows from that. I started with 3 append queries each with a switch
for each type but it didn't work. It only gave me responses where the skills
field had one code, not multiple.

Thoughts?

I also complemented you on giving such "knowledgable" and elegant advice for
free. You're quite the generous one.

HB

John Vinson said:
Thanks for responding.
I don't want to use codes anymore, words are better. And not using a "type"
table, using Value List and hard coded values.
Click to expand...

Well, a Value List will work, but if you'll ever be changing them, you
then have a maintenance hassle. A small Table makes a good rowsource
for a combo box as well, and is easier to maintain. Your choice
though!
I don't understand what you wrote re: switch.
Click to expand...

Switch() is a builtin function in Access VBA. Type Ctrl-G to open the
VBA editor (just to get connected to the correct Help file) and select
Help, and search for Switch() for details. Basically it takes
arguments in pairs; goes left to right; and when the first member of a
pair is True it returns the second.
Part of what makes this difficult is that I am migrating a flat file to a
relational table. The flat file is "Transaction" with 30 columns A#, T#, D#
where A = amount, T = Type and D= Date. I need to put this into rows. ugh!

So, I am writing an append query for each group (ATD), setting criteria for
the various types, (D,R, etc.) and then another column as an expression to
put the textual description in.
Click to expand...

A "Normalizing Union Query" is a very slick way to do this all in one
swell foop, without needing to set criteria at all.

Let's say Transaction has a field TransID and fields A1 through A30,
T1 through T30 and so on; and you want to append this data to a
normalized table with fields TransID, SeqNo (1 to 30), Amount,
TransType, and TransDate. You can create a Query in the SQL window:

SELECT Transaction.TransID, (1) AS SeqNo, [A1] AS Amount,
Switch([T1] = "D", "Deposit", [T1] = "W", "Withdrawal",
[T1] = "P", "Payment", [T1] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D1] AS TransDate)
WHERE [A1] IS NOT NULL
UNION
SELECT Transaction.TransID, (2) AS SeqNo, [A2] AS Amount,
Switch([T2] = "D", "Deposit", [T2] = "W", "Withdrawal",
[T2] = "P", "Payment", [T2] = "C", "Correction",
<etc as many pairs as needed>) AS TranType,
[D2] AS TransDate)
WHERE [A2] IS NOT NULL
UNION
<etcetera, all 30 sets of fields>

Save this Query and then base a UNION query upon it.

John W. Vinson[MVP]
Click to expand...
 
Back
Top