Please help - Need a query that filters results but shows differences in columns

[weston1980]

Hi,

I am finding it hard to explain what I need so here is a simplified example of what I need to get from to:



Original Table

Make/Model	Colour	ID
Ford Fiesta	Red	ANA SNS
Ford Transit	Green	FND FBY
VW Golf	Blue	CBC KJLS
VW Beetle	Red	SDK LDD
Ferrari F40	Green	DLD WDV
Ferrari F40	Blue	LXD SLE
Ford Transit	Green	DLSSDSD
VW Golf	Red	12323

Queried Table (Result)

Make/Model	Red	Green	Blue
Ford Fiesta	ANA SNS	DLSSDSD
Ford Transit		FND FBY
VW Golf	12323		CBC KJLS
VW Beetle	SDK LDD
Ferrari F40		DLD WDV	LXD SLE

[Micron]

Try a cross tab query where Make is the row, Color is the column and ID is the value.
However, I don't think you've specified the correct results for Transit and Fiesta.

EDIT: never mind. I can see now that won't work as long as 2 or more makes have more than one record. No doubt a brighter mind will chime in before I get back.
You may have to create a separate query for each color and combine them in to one query. If your data isn't really that simple I think you'd better 'fess up now.

[orange]

Weston,

I am finding it hard to explain

Try harder, if you can't describe what you are trying to do, do you really think someone else will be able to guess(correctly)??

Pretend we are a bunch of 8 year olds who know nothing about database. Using simple, plain English try gain to tell us what you are trying to accomplish. No jargon.

Good luck and welcome to the forum.

[isladogs]

Micron
I was surprised by your edit so decided to try it
You're too clever for your own good - that's exactly the correct solution:

TRANSFORM First(Table1.[ID]) AS FirstOfID
SELECT Table1.[MakeModel]
FROM Table1
GROUP BY Table1.[MakeModel]
PIVOT Table1.[Colour];

Result

MakeModel	Blue	Green	Red
Ferrari F40	LXD SLE	DLD WDV
Ford Fiesta			ANA SNS
Ford Transit		FND FBY
VW Beetle			SDK LDD
VW Golf	CBC KJLS		12323

NOTE:
Replace Table1 with your table name
You shouldn't use special characters like / in field names
I've changed it to MakeModel but it would be better to have 2 separate fields for Make & Model

TRANSFORM First(Table2.[ID]) AS FirstOfID
SELECT Table2.[Make], Table2.[Model]
FROM Table2
GROUP BY Table2.[Make], Table2.[Model]
PIVOT Table2.[Colour];

Make	Model	Blue	Green	Red
Ferrari	F40	LXD SLE	DLD WDV
Ford	Fiesta			ANA SNS
Ford	Transit		FND FBY
VW	Beetle			SDK LDD
VW	Golf	CBC KJLS		12323

Both versions included in the attached

[Micron]

Your Ford Fiesta is missing a record, unless as noted, either the sample data given or the results desired are incorrect. The fact that there's multiple color values for a make means there's no aggregate function you can use that will work, and as you probably know, such a function is required for a Value column. Min and Max will provide different results, as would First and Last. That's why I also said the sample data better be realistic, otherwise all of the numeric aggregate functions can't be used with text. So we might as well know exactly what we're dealing with.
Hey, is there a genius smilie?

[orange]

You're too clever for your own good - that's exactly the correct solution:

Ah ha! Micron is clairvoyant, and adept with Access, SQL Transform/Pivot. And a really good guesser....Well done.

[isladogs]

I am a dummy....
There were two green transits .....

[weston1980]

Micron
I was surprised by your edit so decided to try it
You're too clever for your own good - that's exactly the correct solution:

TRANSFORM First(Table1.[ID]) AS FirstOfID
SELECT Table1.[MakeModel]
FROM Table1
GROUP BY Table1.[MakeModel]
PIVOT Table1.[Colour];

Result

MakeModel	Blue	Green	Red
Ferrari F40	LXD SLE	DLD WDV
Ford Fiesta			ANA SNS
Ford Transit		FND FBY
VW Beetle			SDK LDD
VW Golf	CBC KJLS		12323

NOTE:
Replace Table1 with your table name
You shouldn't use special characters like / in field names
I've changed it to MakeModel but it would be better to have 2 separate fields for Make & Model

TRANSFORM First(Table2.[ID]) AS FirstOfID
SELECT Table2.[Make], Table2.[Model]
FROM Table2
GROUP BY Table2.[Make], Table2.[Model]
PIVOT Table2.[Colour];

Make	Model	Blue	Green	Red
Ferrari	F40	LXD SLE	DLD WDV
Ford	Fiesta			ANA SNS
Ford	Transit		FND FBY
VW	Beetle			SDK LDD
VW	Golf	CBC KJLS		12323

Both versions included in the attached

These are the perfect examples! Really can't thank you enough! - I have applied the logic to my database and it works perfectly!

Thanks

Dan

[isladogs]

Are you absolutely sure?
As micron pointed out, if you do have two green ford transits or two of any other make/model with the same colour it will only list the first one.

If that is your situation, you need a modified solution possibly involving two crosstabs with a union (NOT TESTED)

[weston1980]

Are you absolutely sure?
As micron pointed out, if you do have two green ford transits or two of any other make/model with the same colour it will only list the first one.

If that is your situation, you need a modified solution possibly involving two crosstabs with a union (NOT TESTED)

Yeah this is fine - The example I gave to get my question across should not have had duplicates, as my database has no duplicates, I have a query to check this!

Basically my issue was solved and not I have found CrossTab that I can use in future!

Thanks Again