Table Conversion

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

Guest

I have a table with fields as follows: SSN, MonthDate, Category, Hours. There are 10 Categories with repeating SSN and MonthDate. Can anyone suggest a way to convert it to SSN, MonthDate, CatHours1, CatHours2...CatHours10? The file has 167,000 records in it

Thank You, Jim
 
Hi,

It is not very clear what you want to accomplish.

Can you try to describe it more in depth?

What MonthDate, Category and Hours fields means (are used for)?

And what should go to CatHours1, catHours2, etc fileds?

Maybe you can give some sample of your data, and how you want it to be in
the end.

It sounds to me you might need to normalize your data, using two tables,
linked by a common field.

But I might be wrong.

Cheers,
Bogdan
________________________
Independent consultant

Jim said:
I have a table with fields as follows: SSN, MonthDate, Category, Hours.
Click to expand...
There are 10 Categories with repeating SSN and MonthDate. Can anyone
suggest a way to convert it to SSN, MonthDate, CatHours1,
CatHours2...CatHours10? The file has 167,000 records in it.
 
Hi Bogdan Zamfir, Thanks for your respons
Perhaps an example
Current Table Record
SSN MonthDate Category Hour
538-22-1729 January 2003 1 36.
538-22-1729 January 2003 2 15.
538-22-1729 January 2003 3 12.
Convert to new Tabl
SSN MonthDate CAT1Hours CAT2Hours CAT3Hour
538-22-1729 January 2003 36.2 15.8 12.5
 
Hi,

You could set-up this using a VBA procedure.

As below (no error hadling amnd I made the assumption MonthDate is date
type):

din DAO.db as database, rs1 as DAO.recordset, rs2 as DAO.recordset, DestRS
as DAO.recordset
set db = currentdb

set DestRS = db.OpenRecordset("DestTable")
set rs1= db.openrecordset("select distinct SSN, MonthDate from
CurrentTable")
if rs1.recordcount >0 then ' there are records
do while not rs1.eof
'(*)
set rs2 = db.openrecordset("select * from CurrentTable where ssn='"
& rs1!SSN & "' and MonthDate =#" & rs1!MonthDate & "# order by Category")
if rs2.recordcount >0 then
DestRS.AddNew
do while not rs2.eof
DestRS.Fields("Cat" & rs2!Category & "Hours") =rs2!Hours
rs2.MoveNext
Loop
DestRS.Update
endif
rs1.MoveNext
Loop
endif

If you need any more help, you can contact me.

HTH,
Bogdan Zamfir
_________________________
Independent consultant


Jim said:
Hi Bogdan Zamfir, Thanks for your response
Perhaps an example;
Current Table Records
SSN MonthDate Category Hours
538-22-1729 January 2003 1 36.2
538-22-1729 January 2003 2 15.8
538-22-1729 January 2003 3 12.5
Convert to new Table
SSN MonthDate CAT1Hours CAT2Hours CAT3Hours
538-22-1729 January 2003 36.2 15.8
Click to expand...
12.5
 
Hi again Bogdan
This is the first VBA procedure I've done.
I entered your code as a Module and when I "F5" it i'm getting "Compile Error, Invalid Outside Procedure". The Table I wish to convert is "HOURS1".

Thanks for all your help. Jim
 
Back
Top