Append across

[murray83]

Hi folks local nut case loose with access again and brain full of ideas, anyways

Have this append query which works just dandy

INSERT INTO PlayerOneTable ( PlayerOneLetters )
SELECT TOP 7 ScrabbleLettersToUse.Letter
FROM ScrabbleLettersToUse
WHERE (((ScrabbleLettersToUse.LetterUsed)=False))
ORDER BY Rnd([ID]);

and looks like so

i how ever would like it to append across wise so it would all be on one record, so my question is how

have tried google and even ai but nothing helps

cheers all

 

[MajP]

Can you show what you mean? Can you possibly use a crosstab to display it?

 

[murray83]

Can you show what you mean? Can you possibly use a crosstab to display it?

cross tab

 

[murray83]

you mean

 

[MajP]

Sorry. I am trying to understand what that would look like as a single record. Can you type it out. Unfortunately I cannot get the above table feature to work in the forum. This was a nice feature because you could show what the results would look like.

 

[murray83]

PlayerOneName	PlayerOneLetter1	PlayerOneLetter2	PlayerOneLetter3	PlayerOneLetter4	PlayerOneLetter5	PlayerOneLetter6
Kevin	A	C	C	E	S	P

I would like the table to look like this when the query has ran the query in question called FirstRandomLetters

Please see attached version reuploaded as had a light bulb the tables had been split

 

[DHookom]

This looks like a crosstab query using the Rank of the random value to create Column Headings 1 - 7. I expect this solution would require storing the value of the random value in the target table.

It might be easier to use VBA to append with a ranking value.

 

[murray83]

could you give me some pointer for cross tabs or is it good ol google to rescue ?

 

[murray83]

with a bit of AI help i have figured it out using temp table see attached for updates

 

[Gasman]

could you give me some pointer for cross tabs or is it good ol google to rescue ?

You could also just use the query wizard?

 

[DHookom]

How does your TempLetters table resemble what you want from post #6?

 

[Pat Hartman]

Are you trying to make a scrabble game? What are you going to do with this data once it is pivoted? A cross tab will sum the data so you can't end up with two "t" columns or two "c" columns.

 

[murray83]

Are you trying to make a scrabble game? What are you going to do with this data once it is pivoted? A cross tab will sum the data so you can't end up with two "t" columns or two "c" columns.

Yes, yes i am and have so far got this ( see attached )

It will give you 7 letters at the start

will count the score ( not quite right though at mo )

Check if your word is a word ( think might be missing TLW )

but im quite happy with it so far

edit 13:32 sorry forgot to also say will give you letters if you have used some from yuor selection

 

[murray83]

How does your TempLetters table resemble what you want from post #6?

i got rid of that way of doing it see post 13 for current format