new Table based on Query Data

[Sprawl]

I have a complex dilemna, for me at least.

I need to create a table based on a query i have created.

the Query has the following information

UserID ----- Ballots
1 --------- 1
2 --------- 2
3 --------- 1
4 --------- 3
5 --------- 1


from that query it would output this directly into a new table that is as follows

BallotID ---- UserID
1 ---------- 1
2 ---------- 2
3 ---------- 2
4 ---------- 3
5 ---------- 4
6 ---------- 4
7 ---------- 4
8 ---------- 1

I for the life of me can't even imagine how i'm ready to do this.

any help would be greatly appeciated.

 

[John Big Booty]

Have you tired a make table query?

 

[Sprawl]

that would be assuming i even knew what that was



which apparently i dont

 

[ajetrumpet]

Create a table named "TBL" with these fields: Field1 = BallotID, Field2 = UserID. Make sure the BallotID is an incremented autonumber field.

then try running this function:

dim rsOLD as recordset, rsNEW as recordset, i as integer, pos as integer

set rsOLD = currentdb.openrecordset("query name", dbopendynaset)
set rsNEW = currentdb.openrecordset("TBL", dbopendynaset)

with rsOLD

.movefirst

  while not .eof

    i = !ballots
    pos = 1

      do until pos > i

        rsNEW.addnew
        rsNEW!userID = !userID
        rsNEW.update

          pos = pos + 1

      loop
        .movenext

  wend

    rsNEW.close
      .close

set rsOLD = nothing
set rsNEW = nothing

 

[John Big Booty]

that would be assuming i even knew what that was



which apparently i dont

Check the attached image.

 

[Sprawl]

thanks john,

that gets the data to a table. i just still havning the issue with spreading the data out to thenew format i want

and Aje, i'mhaving a hard time understanding where you want that run

 

[John Big Booty]

Without knowing the relationship between the BallotID and UserID, it's very hard to advise how you will achieve the second part of your question. You may need to run a number of append queries with different criteria to get the data into your new table as you require it.

 

[Sprawl]

there's no real relationship. the "ballotID" is arbitrarily assigned to each row.

Basically, on my forum which this is using, it takes different factors into account

first it calculates post count, time on the forum, and then lact activities. It then calculates how many ballots those members are to get for each.

so my query i have gives me a list of all the users and a number of ballots they get

I then need to create a table with an individual ballot for each. so if the member gets 8 ballots, they'll have 8 rows, with each different ballot Number.

than i will run a random generator that picks one of those ballot numbers


hope that makes sense.

 

[John Big Booty]

Sorry for the delay, I hope the attached DB will help you on your way.
I'm sure that there are others on this forum that could do it far more elegantly, but it will work and hopefully you can follow what I've done.