Normalizing a table with VBA
| Author |
Message |
|
Richar
#1 / 4
|
 Normalizing a table with VBA I have a table that is structured as so with Revenues/expenses noted by pk:
[pk],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec]
How can I convert this table to be normalized as such
[pk],[revenue/expense],[date]
then, the [CPK] would be pk(a general ledger account) and [date]
the date attribute would show the month/year instead of each month/year
having its own field.
This would have to VBA because the data is in the above format and is
updated monthly...
Rich
Atlanta
ACCESS 97
|
| Sun, 26 Nov 2000 03:00:00 GMT |
|
 |
|
<Vandergh..
#2 / 4
|
Normalizing a table with VBA Hi Richard,
A solution is to use an UNION query:
SELECT [pk],[revenue/expense],[jan] FROM YourTable
UNION ALL
....... continue with cut and paste, for each month.......
If you need to run this query many time, can use:
CurrentDb().Execute strQueryName
or
CurrentDb().Execute strSQLstatement
Hope it may help,
Vanderghast, Access MVP
Quote:
>I have a table that is structured as so with Revenues/expenses noted by pk: >[pk],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec
]
>How can I convert this table to be normalized as such
>[pk],[revenue/expense],[date]
>then, the [CPK] would be pk(a general ledger account) and [date]
>the date attribute would show the month/year instead of each month/year
>having its own field.
>This would have to VBA because the data is in the above format and is
>updated monthly...
>Rich
>Atlanta
>ACCESS 97
|
| Mon, 27 Nov 2000 03:00:00 GMT |
|
|
|
Richar
#3 / 4
|
Normalizing a table with VBA But How do I put the date(month/year) in the next field...
Quote: > Hi Richard, > A solution is to use an UNION query:
> SELECT [pk],[revenue/expense],[jan] FROM YourTable
> UNION ALL
> ....... continue with cut and paste, for each month.......
> If you need to run this query many time, can use:
> CurrentDb().Execute strQueryName
> or
> CurrentDb().Execute strSQLstatement
> Hope it may help,
> Vanderghast, Access MVP
> >I have a table that is structured as so with Revenues/expenses noted by
pk:
>[pk],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec
> ]
> >How can I convert this table to be normalized as such
> >[pk],[revenue/expense],[date]
> >then, the [CPK] would be pk(a general ledger account) and [date]
> >the date attribute would show the month/year instead of each month/year
> >having its own field.
> >This would have to VBA because the data is in the above format and is
> >updated monthly...
> >Rich
> >Atlanta
> >ACCESS 97
|
| Mon, 27 Nov 2000 03:00:00 GMT |
|
|
|
Graham Manden
#4 / 4
|
Normalizing a table with VBA Rich
You don't mention in your original post that there is a year anywhere in the
data. I assume the table you are importing contains data for only one year
and the user will be required to specify which year it is. I would
recommend you store the year and month as two separate fields (integer and
byte respectively) to facilitate comparisons between the same month of
different years.
So, your UNION query would be:
Insert into YourOtherTable (Account, Year, Month, Amount)
Select [pk], [Which year?] as Year, 1 as Month, [jan] as Amount FROM
YourTable
UNION
Select [pk], [Which year?] as Year, 2 as Month, [feb] as Amount FROM
YourTable
UNION
Select [pk], [Which year?] as Year, 3 as Month, [mar] as Amount FROM
YourTable
UNION
... and so on
The [Which year?] would be unresoved and therefore interpreted as a
parameter.
If you are importing monthly, as you suggest, then you don't need the UNION
at all - just create an SQL string based on the month and year provided by
the user.
Good Luck!
Graham Mandeno [Access MVP]
Alpha Solutions Limited
Auckland, New Zealand
Return mail address is invalid in a vain attempt to reduce spam.
Please post new questions to newsgroups.
Quote:
>But How do I put the date(month/year) in the next field...
>> Hi Richard,
>> A solution is to use an UNION query:
>> SELECT [pk],[revenue/expense],[jan] FROM YourTable
>> UNION ALL
>> ....... continue with cut and paste, for each month.......
>> If you need to run this query many time, can use:
>> CurrentDb().Execute strQueryName
>> or
>> CurrentDb().Execute strSQLstatement
>> Hope it may help,
>> Vanderghast, Access MVP
>> >I have a table that is structured as so with Revenues/expenses noted by
>pk:
>>[pk],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[de
c
>> ]
>> >How can I convert this table to be normalized as such
>> >[pk],[revenue/expense],[date]
>> >then, the [CPK] would be pk(a general ledger account) and [date]
>> >the date attribute would show the month/year instead of each month/year
>> >having its own field.
>> >This would have to VBA because the data is in the above format and is
>> >updated monthly...
>> >Rich
>> >Atlanta
>> >ACCESS 97
|
| Tue, 28 Nov 2000 03:00:00 GMT |
|
| |
|
