VBA - Not sure if possible

[Waylen]

I need to breakout a bunch of records. My table has 20 fields, for example...
Acct, value1, value2, value3, value4, etc...

I need to create a new record with the acct field and value1, then I need to create a new record from the same acct and then value2. I have to do this is vba which I've just started using. Please show me how to do this with a loop and I should be able to do the rest from that example...

Example: acct, value1, value2, value3, etc...

Need: acct, value1
acct, value2
acct, value3
etc.....

Thank you in advance!

 

[boblarson]

Sorry to break the bad news to you, but you have a spreadsheet in Access there. This is a very bad thing to do. Access is a relational database, not a spreadsheet. I think you need to take a look at NORMALIZING your data which is EXTREMELY important for an Access database (or any Relational Database). The way your data is now, is very hard to get meaningful data back out.

 

[georgedwilkinson]

And why do in VBA what you can do with SQL? (I concur with Bob's sentiments)

 

[Waylen]

We have dozens of tables like this that need to be broken out from an old flat table design. We are trying to go to a relational database system but need to break out the data to do so. SQL or any other suggestions would be appreciated.

Once again, is this possible?

 

[RossWindows]

You may have luck with a UNION query
Just remember to terminate the end of the query with a semicolon.

SELECT acct, value1 FROM YourTable
UNION SELECT acct, value2 FROM YourTable
UNION SELECT acct, value3 FROM YourTable
UNION SELECT acct, value4 FROM YourTable;

After that, I would follow Bob's advise and copy the returned data into a normalized table structure for future use.
This simple example would equate to two tables or more depending on what other information is in the spreadsheets.

tblAccounts
- ActNumber

tblAccountValues
- ActNumber
- ActValue

 

[MarkK]

You can open a recordset and then traverse it's fields collection. Code will look a little like...

dim dbs as dao.database
dim rst as dao.recordset
dim fld as dao.field

set dbs = currentdb
set rst = dbs.openrecordset( _
  "SELECT Acct, Ignore, v1, v2, v3, v4 FROM FlatFile")
with rst
  do while not .eof
    for each fld in .fields
      select case fld.name
        case "Acct", "Ignore"
[COLOR="Green"]          'ignore these fields[/COLOR]
        case else
[COLOR="Green"]          'save each field to a single record in a new table
[/COLOR]          dbs.execute _
            "INSERT INTO NewTable " & _
              "( Acct, Value ) " & _
            "VALUES " & _
              "( " & !Acct & ", " & fld.value & ")"
      end select
    next
    .movenext
  loop
  .close
end with