G
Guest
AccessXP with Win 2000
In a table (IMPORT) I have data that, for the most part, resides on two rows
based on two dates. Each record is related based on "ACCT". But not all
records have two corresponding rows. For example:
ACCT DESC BUD AMT TYPE DATE DATE_PER
102.407 Acct Rec 50.00 30.00 A Sep-03 1
102.407 Acct Rec 78.00 25.75 A Sep-04 2
102.408 Acct-2 6.00 5.00 R Sep-03 1
102.409 Allow 105.00 100.25 L Sep-03 1
102.409 Allow 135.00 125.86 L Sep-04 2
102.410 Allow-2 18.00 19.50 E Sep-04 2
I need a query that will combine this data into another table called MERGE
as follows:
ACCT DESC SEP-03 BUD SEP-04 TYPE
102.407 Acct Rec 30.00 78.00 25.75 A
102.408 Acct-2 6.00 0.00 0.00 R
102.409 Allow 100.25 135.00 125.86 L
102.410 Allow-2 0.00 18.00 19.50 E
Please note that the "BUD" amount always comes from the most current period
and that some "ACCTs" in "Sep-03" are in "Sep-04" and some are not and vice
versa. I need all records that match based on "ACCT" to line up and those
that don't match to be included on their own, as in the example above. Nulls
are populated with zeros.
1. Is this better done in different steps?
2. What would be the SQL to accomplish this?
3. I could conceivably recreate the starting table and create a separate
table for each date, then merge them from two tables, would this be better?
Desperate - please help! Thanks in advance.
In a table (IMPORT) I have data that, for the most part, resides on two rows
based on two dates. Each record is related based on "ACCT". But not all
records have two corresponding rows. For example:
ACCT DESC BUD AMT TYPE DATE DATE_PER
102.407 Acct Rec 50.00 30.00 A Sep-03 1
102.407 Acct Rec 78.00 25.75 A Sep-04 2
102.408 Acct-2 6.00 5.00 R Sep-03 1
102.409 Allow 105.00 100.25 L Sep-03 1
102.409 Allow 135.00 125.86 L Sep-04 2
102.410 Allow-2 18.00 19.50 E Sep-04 2
I need a query that will combine this data into another table called MERGE
as follows:
ACCT DESC SEP-03 BUD SEP-04 TYPE
102.407 Acct Rec 30.00 78.00 25.75 A
102.408 Acct-2 6.00 0.00 0.00 R
102.409 Allow 100.25 135.00 125.86 L
102.410 Allow-2 0.00 18.00 19.50 E
Please note that the "BUD" amount always comes from the most current period
and that some "ACCTs" in "Sep-03" are in "Sep-04" and some are not and vice
versa. I need all records that match based on "ACCT" to line up and those
that don't match to be included on their own, as in the example above. Nulls
are populated with zeros.
1. Is this better done in different steps?
2. What would be the SQL to accomplish this?
3. I could conceivably recreate the starting table and create a separate
table for each date, then merge them from two tables, would this be better?
Desperate - please help! Thanks in advance.