Crosstab Query Appropriate?

[blizkij]

Hi

I haven’t used Access for about 3 years so apologies, I’m very rusty. I have been reading the threads to deal with crosstab queries, however I just can’t get them to apply to my data.

I have a query [Seat Re-Sale Data] holding all the data of resold seats on a match by match basis. The data appears as follows

Client Ref Game Seat Location Reward
53456 Sydney X147 A6 $17.00
53456 Melbourne X147 A6 $17.00
14578 Sydney X202 B1 $13.00
14578 Sydney X202 B2 $13.00
14578 Brisbane X202 B1 $13.00

I would like to have the data appear as below:

Client Ref Game 1 Game2 Seat Loc Reward Total
53456 Sydney Melbourne X147 A6 $17.00 $34.00
14578 Sydney Brisbane X202 B1 $13.00 $26.00
14578 Sydney X202 B2 $13.00 $13.00


Any suggestions would be greatly appreciated.

Nikki

 

[PHV]

[tt]Client Ref Game Seat Location Reward
53456 Sydney X147 A6 $17.00
53456 Melbourne X147 A6 $17.00[/tt]
How do you know that Game1 should be Sydney instead of Melbourne ?

what is the SQL code of [Seat Re-Sale Data] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blizkij]

the SQL is:

SELECT [Seat Holder Data].[Client Ref], [Re-Sold Seat Data].[Game], [Re-Sold Seat Data].[Reference], [Re-Sold Seat Data].[Barcode], [Re-Sold Seat Data].[Area], [Re-Sold Seat Data].[X Sect], [Re-Sold Seat Data].[Row], [Re-Sold Seat Data].[Seat], [Re-Sold Seat Data].[Sold], [Re-Sold Seat Data].[Ticket Type], [Seat Data].[Member Type], [Seat Data].[Reward]
FROM [Seat Holder Data] INNER JOIN ([Re-Sold Seat Data] INNER JOIN [Seat Data] ON [Re-Sold Seat Data].[Barcode]=[Seat Data].[Barcode]) ON [Seat Holder Data].[Client Ref]=[Re-Sold Seat Data].[Client Ref];

As to knowing which game is which, I think that's the main problem I have. Unfortunately this is a database I've inherited, and the set up is appalling. The other aspect is that the game name doesn't really matter, I just need it as a text field to output to excel. The number of games and game names will be different for each client.

Hope this sort of clarifies it.

cheers

 

[PHV]

The number of games and game names will be different for each client
What is the maxinum number of games for a [Client Ref],[Seat Location] pair ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blizkij]

Maximum number of games will be 6. Obviously, it may be any number up to 6, but I just need to extract the names of the games they sold for.

thanks so much for your help

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blizkij]

Hi,

this is what I came up with (fully expecting to be incorrect...):

SELECT [Client Ref],
Concatenate("SELECT [Game] WHERE [Client Ref] =""" & [Client Ref] & """) as Event
FROM [Re-Sold Seat Rec Data]


Unfortunately it keeps coming up with a syntax error and I'm completely lost.

sorry to be dragging this one out.

cheers

 

[PHV]

You should have a FROM clause in the Concatenate's parameter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blizkij]

Thanks for your help PHV.

I still can't get it to work, it's so long since I've used any of this stuff I think I've gone right back to scratch knowledgewise. I think I'll just do a manual transcribe.

cheers

 

[blizkij]

ok. so after spending most of the day banging my head against the wall trying to transcribe all this data, I refuse to believe i can't do it in a query.

could anyone please post how I should write this?

thanks in advance

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

This isn't your exact desired output however would it work?

TRANSFORM Sum([Seat Re-Sale Data].Reward) AS SumOfReward
SELECT [Seat Re-Sale Data].[Client Ref], [Seat Re-Sale Data].[Seat Location]
FROM [Seat Re-Sale Data]
GROUP BY [Seat Re-Sale Data].[Client Ref], [Seat Re-Sale Data].[Seat Location]
PIVOT [Seat Re-Sale Data].Game;

Duane
Hook'D on Access
MS Access MVP