Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: Query SQL Statement Help!!!

    Click Link and Read Below
    http://web.mac.com/yont24/iWeb/test.html

    Hello all any help would be greatly appreciated. Below is an example and solution of my tables. Lets see if i can explain this. OK... Basically I need to create a query SQL statement that restructers my table from 16 records into 4 records . ID has primary key info and each record steps every 4 rows. So ID 1-4 to 5-8 to 9-12 to 13- 16 and so on to End of File. So ID 1-4 I want to make record#1... ID 5-8 record#2... ID 9-12 record#3... ID 13-16 record#4 and so on to End of File. Solution table below. Really all I need to know is how to take my address column where it steps every 4 records and turn it it into 1 record with 6 cells. (Address, City, State, Zip, County and HW#) I'm able to handle the rest with the empty cells.

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    provided that you have Data Access Objects 3.x loaded in rerferences before any ADO reference and provided that the order of the rows coincides with my dim statement (address, city, state ,zip (you said every 4 rows - not six as you have exampled)) Also I will assume there is an additional field for the update and I will call it myfield

    on error resume next 'I love that snippett
    dim db as database
    dim rst as recordset
    set db = currentdb
    set rst = db.openrecordset("mytable")
    dim myaddr,mycity,myzip,mycounty
    dim myCompleteaddress

    rst.movefirst
    Dale Houston, TX

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    provided that you have Data Access Objects 3.x loaded in rerferences before any ADO reference and provided that the order of the rows coincides with my dim statement (address, city, state ,zip (you said every 4 rows - not six as you have exampled)) Also I will assume there is an additional field for the update and I will call it myfield

    on error resume next 'I love that snippett
    dim db as database
    dim rst as recordset
    set db = currentdb
    set rst = db.openrecordset("mytable")
    dim myaddr,mycity,myzip,mycounty

    dim myrecordcount

    rst.movefirst
    do until rst.eof
    myaddr = rst!addressfield
    rst.movenext
    mycity = rst!cityfield
    rst.movenext
    myzip = rst!zipfield
    rst.movenext
    mystate = rst!statefield
    rst.edit
    rst!myfield = myaddr & " " & mycity & " " & mystate & ", " & myzip
    rst.update
    rst.movenext
    loop
    myrecordcount = rst.recordcount
    msgbox mycordcount / 4 & " actual updates were made"
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •