Altering a table design and populating table from VB

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

Guest

Hi, I'm a novice at this so please help.
I'm trying to write a piece of code in Access VB to update a table with the
results of a query, and create new columns, rows for new datatypes that
results from the query.

E.G. If I had a table with columns
FieldTitle, FP1, FP2
and rows:
Y, 8532, 8428
N, 234, 325

next time I run the query it brings back values for the next fiscal period:
Y, 8654
N, 210
N/A, 25

How do I updated in code so that the table would be:

FieldTitle, FP1, FP2, FP3
Y, 8532, 8428, 8654
N, 234, 325, 210
N/A, 0, 0, 25

Thanks for your Help

Nuve
 
Hi, I'm a novice at this so please help.
I'm trying to write a piece of code in Access VB to update a table with the
results of a query, and create new columns, rows for new datatypes that
results from the query.

E.G. If I had a table with columns
FieldTitle, FP1, FP2
and rows:
Y, 8532, 8428
N, 234, 325

next time I run the query it brings back values for the next fiscal period:
Y, 8654
N, 210
N/A, 25

How do I updated in code so that the table would be:

FieldTitle, FP1, FP2, FP3
Y, 8532, 8428, 8654
N, 234, 325, 210
N/A, 0, 0, 25
Click to expand...

Ummm... No. You do NOT want to create such a non-normalized Table.
Storing data - fiscal periods - in fieldnames is VERY BAD DESIGN and
will cause *far* more hassle than benefit!

Instead, consider a normalized table structure:

FieldTitle; FiscalPeriod; Values
"Y"; 1; 8532
"Y"; 2; 8428
"Y"; 3; 8654
"N"; 1; 234
"N"; 2; 325
"N"; 3; 210

and so on.

If you wish to see the data in a grid with fieldtitle down the left
and FiscalPeriod across the top, run a Crosstab query on the tall-thin
table shown.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top