Cross-tab?

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
I have table "tDefinitionSpecific" and I want to make "tFieldData." Cross-tab query doesn't quite do it?

tDefinitionSpecific
============================
Issue_ID   Def_I   FieldName
------         --------       ----------
2                40        Sponsor
2                90        Sales
3                21        Sponsor
3                23        Sales
4                46        Sales
6                100       Sponsor

tFieldData
========================================
Issue_ID   Sponsor      Sales
--------          ----------       ----------
2               40             90
3               21             23
4                               46
6             100

This is for Access 2007.

What is the best way to accomplish this?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT tDefinitionSpecific.Issue_ID, Sum(IIf([FieldName]="SPONSOR",[Def_I],0)) AS Sponsor, Sum(IIf([FieldName]="SALES",[Def_I],0)) AS Sales
FROM tDefinitionSpecific
GROUP BY tDefinitionSpecific.Issue_ID;

The above would give you:
Issue_ID      Sponsor      Sales
   2                 40                  90
   3                 21                  23
   4                 0                    46
   6                 100                0
Mike EghtebasDatabase and Application Developer

Author

Commented:
IrogSinta,

For now I suppose I could use it this way, but it would be much better without specifying

Sum(IIf([FieldName]="SPONSOR"

just like cross-tab query.

Mike
Mike EghtebasDatabase and Application Developer

Author

Commented:
re:> The above would give you:

It doesn't. It sums because you have:

Sum(IIf([FieldName]="SPONSOR",[Def_I],0))

Have you tried witht the data?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
should be able to do a crosstab.

Set IssueID as Row Header
FieldName as the column Header
Def_I as the Value

But instead of selecting Count or sum for the Def_I column, select First
As usual, fyed is right on!
Mike EghtebasDatabase and Application Developer

Author

Commented:
Thank fyed,

If the original data was like:

Issue_ID   Def_I   FieldName
------         --------       ----------
2                40        Sponsor
2                90        Sales
2                91        Sales               <--  this rwo gets eliminated
3                21        Sponsor
3                23        Sales
4                46        Sales
6                100       Sponsor
Mike EghtebasDatabase and Application Developer

Author

Commented:
I suppose a union query should do this also.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
but that is not what the original data you provided looks like.  Given this new dataset, what would you want the output to look like?
Mike EghtebasDatabase and Application Developer

Author

Commented:
I had tried cross-tab before posting. But it failed hence I decided to get some opinion. I agree that I didn't give a good data set to start with. With the revise data above, It is expected to return:


Issue_ID      Sponsor      Sales
   2                 40                  90
   2                  0                   91
   3                 21                  23
   4                 0                    46
   6                 100                0
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Try something like this.

Start out with a query that looks something like:

SELECT T1.Issue_ID, T1.FieldName, T1.Def_I, Count(T2.*) as Level
FROM yourTable as T1
INNER JOIN yourTable as T2
ON T1.Issue_ID = T2.Issue_ID
AND T1.FieldName = T2.FieldName
AND T1.Def_I >= T2.Def_I
GROUP BY T1.Issue_ID, T1.FieldName, T1.Def_I

Then use this query as the source for your crosstab query, and use the Issue_ID, and Level fields as RowHeaders.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Using yor solution I have:

SELECT T1.Issue_ID, T1.FieldName, T1.Definition_ID, Count(T2.*) as Level
FROM tIssueSpecific as T1
INNER JOIN tIssueSpecific as T2
ON T1.Issue_ID = T2.Issue_ID
AND T1.FieldName = T2.FieldName
AND T1.Definition_ID >= T2.Definition_ID
GROUP BY T1.Issue_ID, T1.FieldName, T1.Definition_ID

And it give the attached error (no error number please see the image).

After I changed it to:

SELECT T1.Issue_ID, T1.FieldName, T1.Definition_ID, Count(*) as Level_A
FROM tIssueSpecific as T1
INNER JOIN tIssueSpecific as T2
ON T1.Issue_ID = T2.Issue_ID
AND T1.FieldName = T2.FieldName
AND T1.Definition_ID >= T2.Definition_ID
GROUP BY T1.Issue_ID, T1.FieldName, T1.Definition_ID

It runs but I have to check the output.

thx
QryError.bmp
Mike EghtebasDatabase and Application Developer

Author

Commented:
Perfect.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial