Query Question - Crosstab? 2

[TCARPENTER]

Hi All,

I've searched this over and I'm sure I'm not asking the right question. Ultimately, I want this be in a report but I thnk it belongs here...?

For each file, there are 4 different flavors: Grid1x2-XY, Grid1x2-X-notY, Grid1x2-notX-Y, and Grid1x2-notX-notY. I want to place them in a format like this:

XY X-notY notX-Y notX-notY
Grid1x2-XY Grid1x2-X-notY Grid1x2-notX-Y Grid1x2-notX-notY
Grid1x3-XY Grid1x3-X-notY Grid1x3-notX-Y ...

I thought a crosstab query was what I wanted but I don't think so. Any help would be appreciated.

TIA
Todd

 

[lespaul]

what/where is the raw data? Are all four of these fields in the same table? What is the actual data?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[TCARPENTER]

Hi Leslie,

Data is all from the same table and coming from the same field "Name".

Todd

 

[lespaul]

ok, can you provide some sample data from that table, your expected results from that sample and any relevant calculations required to produce those results?

 

[TCARPENTER]

Hi Leslie,

tblFileNames
[ul]
[li]Grid1x2-XY.ipt[/li]
[li]Grid1x2-X-notY.ipt[/li]
[li]Grid1x2-notX-Y.ipt[/li]
[li]Grid1x2-notX-notY.ipt[/li]
[li]Grid1x3-XY.ipt[/li]
[li]Grid1x3-X-notY.ipt[/li]
[li]Grid1x3-notX-Y.ipt[/li]
[li]...[/li]
[/ul]

And here's the SQL statement I've got so far, which works, it just doesn't keep it all on the same line, which is what I'm trying to figure out:

SELECT tblFileNames.Names, IIf(Mid([Names],8)='-XY.ipt',[Names],"N/A") AS XY, IIf(Mid([Names],8)='-X-notY.ipt',[Names],"N/A") AS [X-notY], IIf(Mid([Names],8)='-notX-Y.ipt',[Names],"N/A") AS [notX-Y], IIf(Mid([Names],8)='-notX-notY.ipt',[Names],"N/A") AS [notX-notY]
FROM tblFileNames
ORDER BY tblFileNames.Names;

Thanks
Todd

 

[lespaul]

So you want a single record output and now it shows:
[tt]
Names XY X-notY notX-Y notX-notY
xxx xx
xxx xx xx
[/tt]

what do you want it to display? (again some sample data will make this much easier for me to "see" what you're doing)


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[TCARPENTER]

That's correct. I want it to display just as I've got it in the first post. The post above with the SQL query has actual sample data in it.

Thanks
Todd

 

[lespaul]

I have to say that i would do something like:

1. Create an "extension" table:
[tt]
tblExtension
Extension Location
-XY.ipt XY
-X-notY.ipt X-notY
-notX-Y.ipt notX-Y
-notX-notY.ipt notX-notY
[/tt]

Then join your original table on the Extension field:

SELECT Names, Location
FROM tblFileNames INNER JOIN tblExtension ON tblFileNames.Mid([Names],8) = tblExtension.Extension

Then you'll get:
[tt]Names Location
Grid1x2-XY.ipt XY
Grid1x2-X-notY.ipt X-notY
Grid1x2-notX-Y.ipt notX-Y
Grid1x2-notX-notY.ipt notX-notY
Grid1x3-XY.ipt XY
Grid1x3-X-notY.ipt X-notY
Grid1x3-notX-Y.ipt notX-Y[/tt]

and then cross-tab these results.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

Typed, untested:

SELECT Left(Names,InStr(Names,'-')-1) AS Prefix
,Max(IIf(Names Like '*-XY.ipt',Names,'') AS XY
,Max(IIf(Names Like '*-X-notY.ipt',Names,'') AS [X-notY]
,Max(IIf(Names Like '*-notX-Y.ipt',Names,'') AS [X-notY]
,Max(IIf(Names Like '*-notX-notY.ipt',Names,'') AS [notX-notY]
FROM tblFileNames
GROUP BY Left(Names,InStr(Names,'-')-1)
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TCARPENTER]

Great - thanks to both of you for your help - have some stars!

Thanks again
Todd

 

[TCARPENTER]

Hmm...

I need to revisit this, I have used a combination of Leslie's solution and PHV's. The first query works:

SELECT IIf(InStr(1,[Name],'-',1)>0,Left([Name],InStr(1,[Name],'-',1)-1),'') AS Prefix, Model.Name, Mid([Name],InStr(1,[Name],"-X",1)) AS Location
FROM Model
WHERE (((IIf(InStr(1,[Name],'-',1)>0,Left([Name],InStr(1,[Name],'-',1)-1),''))<>'') AND ((Model.GroupID)=6));

My crosstab query howevever:

TRANSFORM First(Query4.Name) AS FirstOfName
SELECT Query4.Prefix
FROM Query4
GROUP BY Query4.Prefix
PIVOT Query4.Location;

generates this error:

Invalid procedure call

What am I missing?

Thanks
Todd