1 field become 2 fields in newtable

[ping99]

Hi,

I am a novice in VBA - Access coding :

I have an oldtable in Access

oldtable ( 1 field ) called amount with 2 records.

amount

10000 (1st record)
20000 (2nd record)

How to get a Newtable with 2 fields ( amount1 amount2 )
already created ?

newtable ( 1 record with 2 fields )
amount1 amount2
10000 20000 ( only 1 record )

Can someone assists me for coding in VBA or using SQL ?

TIA

 

[Golom]

You now have a normalized table. What you are wanting to do is create an un-normalized one. With only two records its fairly simple

Select A.Amount As [Amount1], B.Amount As [Amount2]

From tbl A INNER JOIN tbl B
     ON A.Amount < B.Amount

But note that this doesn't extend to multiple records (i.e. more than 2) very well. In fact the SQL expression explodes into an unmanagable mess.

 

[ping99]

Thanks for your prompt reply,

Sorry for not explaining clearly, in fact a second table (TBL B)is empty, this means is to transfert 2 records from an oldtable (TBL A)to become 1 record with 2 fields in Newtable (TBL B) instead.

That's no relation between 2 tables

 

[CautionMP]

ping99,
This should work for a VBA route. It uses a simple boolean (True/False) flag that flip flops to determine if your on record on or two.

Make sure you have a reference to the DAO object library in your VBA project.

Sub Unnormalize()
Dim rstoldtable As DAO.Recordset
Dim rstnewtable As DAO.Recordset
Dim blnNew As Boolean
Set rstoldtable = CurrentDb.OpenRecordset("oldtable")
Set rstnewtable = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)
Do
  If Not blnNew Then
    rstnewtable.AddNew
    rstnewtable.Fields(0) = rstoldtable.Fields(0)
    blnNew = Not blnNew
  Else
    rstnewtable.Fields(1) = rstoldtable.Fields(0)
    rstnewtable.Update
    blnNew = Not blnNew
  End If
  rstoldtable.MoveNext
Loop Until rstoldtable.EOF
rstoldtable.Close
Set rstoldtable = Nothing
rstnewtable.Close
Set rstnewtable = Nothing
End Sub

Hope this helps,
CMP

P.S. I agree with Golom hence the name of the routine.

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[ping99]

Thanks a million CMP,

It worked fine your code, I need your help again, what happens if I have more than 2 records in an oldtable

oldtable with 6 records
amount
10000
20000
30000
40000
50000
60000

 

[Golom]

Try it and see but I think it would produce
[tt]
1000 2000
3000 4000
5000 6000
[/tt]

 

[ping99]

Hi Golom,

You are right with the result you get with 3 records but I want to have,it is always in the single record,means 1 line

1000 2000 3000 4000 5000 6000

TIA

 

[CautionMP]

ping99,
In [tt]oldtable[/tt] what is the field that ties the records together?

Instead of using the flip/flop code you will need to check this field to determine when to [tt]Update[/tt] the exisiting record and [tt]AddNew[/tt] to start a new one.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Golom]

That would mean that you could not have a source table with more than 255 rows because Access is limited to 255 columns in a table ... less actually because index fields must be deducted from that total.

If you must however

Sub Unnormalize()
Dim rstoldtable As DAO.Recordset
Dim rstnewtable As DAO.Recordset
Dim tdf         As DAO.TableDef
Dim n           As Integer

On Error Resume Next
CurrentDb.Execute "DROP TABLE newTable"

Set tdf = db.CreateTableDef("newtable")
tdf.CreateField "KeyField", dbText, 50
[COLOR=black cyan]' 253 allows you to add a key field and an index[/color]
For n = 1 To 253
    tdf.CreateField "Amount" & n, dbLong
Next

Set rstoldtable = CurrentDb.OpenRecordset("oldtable")
Set rstnewtable = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)

n = 0
Do Until rstoldtable.EOF
  n = n + 1
  If n = 1 Then  
     rstnewtable.AddNew
  ElseIf n > 253 then
     MsgBox "More than 253 records in input table."
     Exit Do
  End If
  rstnewtable.Fields(n-1) = rstoldtable.Fields(0)
  
  rstoldtable.MoveNext
Loop 
rstoldtable.Close
Set rstoldtable = Nothing

rstnewtable.Update
rstnewtable.Close
Set rstnewtable = Nothing
End Sub

 

[ping99]

CMP,

I have no idea how to modify your code to get the result
in a single record in newtable:

amount1 amount2 amount3 amount4 amount5 amount6
10000 20000 30000 40000 50000 60000

TIA if you can help me again with your new codes.

 

[CautionMP]

ping99,

In [tt]oldtable[/tt] what is the field that ties the records together?

 

[CautionMP]

ping99,
I don't remember which member I heard this from but you appear to be guilty of committing spreadsheet.

You can re-format the infromation in [tt]oldtable[/tt] by using a combination of queries (sample below). The text in black is a crosstab query, the [red]red[/red] text is a select query that calculates the amount number.

You will need to update [tt][KeyField][/tt] to the actual field name.

[tt]TRANSFORM Max([Amount])
SELECT [KeyField]
FROM [red](SELECT x.[KeyField], x.Amount, "Amount"&Count(y.Amount) AS AmountIndex
[tab]FROM oldtable AS x, oldtable AS y
[tab]WHERE (((y.Amount)<=[x].[Amount]) AND ((y.[KeyField])=[x].[[KeyField]]))
[tab]GROUP BY x.[KeyField], x.Amount)[/red]
GROUP BY [[KeyField]]
PIVOT [AmountIndex];[/tt]

If you really need to you could use this query to make a new table or append to an existing table.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[ping99]

Thanks a million Golom and CMP,

Golom,
I try this code, it works fine but I don't know why
the part to create a newtable did not work so I comment out.


Sub Unnormalize2()
Dim rstoldtable As DAO.Recordset
Dim rstnewtable As DAO.Recordset
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim n As Integer

On Error Resume Next
'CurrentDb.Execute "DROP TABLE newTable"

'Set db = CurrentDb()

'Set tdf = db.CreateTableDef("Newtable")
'tdf.CreateField "keyfield", dbText, 50
'' 253 allows you to add a key field and an index
'For n = 1 To 253
' tdf.CreateField "Amount" & n, dbLong
'Next

Set rstoldtable = CurrentDb.OpenRecordset("oldtable")
Set rstnewtable = CurrentDb.OpenRecordset("newtable", dbOpenDynaset)

n = 0
Do Until rstoldtable.EOF
n = n + 1
If n = 1 Then
rstnewtable.AddNew
ElseIf n > 253 Then
MsgBox "More than 253 records in input table."
Exit Do
End If
rstnewtable.Fields(n - 1) = rstoldtable.Fields(0)

rstoldtable.MoveNext
Loop
rstoldtable.Close
Set rstoldtable = Nothing

rstnewtable.Update
rstnewtable.Close
Set rstnewtable = Nothing
End Sub

CMP,
A great idea with crosstab query suggestion.