PLEASE HELP

  • Thread starter Thread starter CLAIRE
  • Start date Start date
C

CLAIRE

I'VE A LIST OF INVOICE NUMBERS WITH DISTRIBUTION VALUES
LISTED IN COLUMNS FOR EACH DEPARTMENT, LIKE THIS

INV NUMBER FB FT FC FD FG FE
FD002115 203.55 1808.00
FE0300C002 51894.00

I WOULD LIKE IT TO LOOK LIKE THIS

INV NUMBER BU CODE DISTRIBUTION VALUE
FD002115 FT 203.55
FD002115 FD 1808.00
FE0300C002 FE 51894.00
 
Feel free to turn your caps lock OFF.

You have a crosstab, and you would like the data to be normalized. This
will require either creating a new table structure, or implementing several
make table queries to get the data as desired.

It is not a hard process, just time consuming.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I'VE A LIST OF INVOICE NUMBERS WITH DISTRIBUTION VALUES
LISTED IN COLUMNS FOR EACH DEPARTMENT, LIKE THIS
Click to expand...

Please don't post in all caps. It's hard to read and it looks like
you're SHOUTING AT US. All lower case is better if you don't like to
shift.
INV NUMBER FB FT FC FD FG FE
FD002115 203.55 1808.00
FE0300C002 51894.00

I WOULD LIKE IT TO LOOK LIKE THIS

INV NUMBER BU CODE DISTRIBUTION VALUE
FD002115 FT 203.55
FD002115 FD 1808.00
FE0300C002 FE 51894.00
Click to expand...

A "Normalizing Union Query" will do the trick here. Create a new
Query, don't add ANY tables, and go to the SQL window (using the SQL
icon at the left of the toolbar. Type in a query like this (using your
own table and fieldnames of course):

SELECT [Inv Number], "FB" AS [BU Code], [FB] AS [Distribution Value]
FROM yourtablename
WHERE [FB] IS NOT NULL
UNION
SELECT [Inv Number], "FT", [FT]
FROM yourtablename
WHERE [FT] IS NOT NULL
UNION
SELECT [Inv Number], "FC", [FC]
FROM yourtablename
WHERE [FC] IS NOT NULL
UNION
SELECT [Inv Number], "FD", [FD]
FROM yourtablename
WHERE [FD] IS NOT NULL
UNION

<etc. etc.>

Save this query and then base an Append query on it to populate the
new properly normalized table.
 
Another way to do this, is to use a union query. As long as your list
of department codes is reasonable in length, this should work.

SELECT [Inv Number], 'FB' as [BU_CODE], [FB] as [Distribution Value]
FROM yourTable
WHERE IsNull([FB]) = 0
UNION
SELECT [Inv Number], 'FT' as [BU_CODE], [FT] as [Distribution Value]
FROM yourTable
WHERE IsNull([FT]) = 0
UNION
SELECT [Inv Number], 'FC' as [BU_CODE], [FC] as [Distribution Value]
FROM yourTable
WHERE IsNull([FC]) = 0

....


--
HTH

Dale Fye


I'VE A LIST OF INVOICE NUMBERS WITH DISTRIBUTION VALUES
LISTED IN COLUMNS FOR EACH DEPARTMENT, LIKE THIS

INV NUMBER FB FT FC FD FG FE
FD002115 203.55 1808.00
FE0300C002 51894.00

I WOULD LIKE IT TO LOOK LIKE THIS

INV NUMBER BU CODE DISTRIBUTION VALUE
FD002115 FT 203.55
FD002115 FD 1808.00
FE0300C002 FE 51894.00
 
Back
Top