Re: Append query. letter code to description new but simila
From: HB (HB_at_discussions.microsoft.com)
Date: 01/18/05
- Next message: HB: "Re: Getting a count query to return zero."
- Previous message: Kerry: "Re: Multiple Field Search Query"
- In reply to: John Vinson: "Re: Append query. letter code to description"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 17 Jan 2005 16:09:03 -0800
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" wrote:
> On Sun, 16 Jan 2005 20:15:03 -0800, "HB"
> <HB@discussions.microsoft.com> wrote:
>
> >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.
>
> 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.
>
> 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.
>
> 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]
>
- Next message: HB: "Re: Getting a count query to return zero."
- Previous message: Kerry: "Re: Multiple Field Search Query"
- In reply to: John Vinson: "Re: Append query. letter code to description"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
