asked on

Cross-tab?

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.

IrogSinta

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 Eghtebas

ASKER

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 Eghtebas

ASKER

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?

Dale Fye

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

IrogSinta

As usual, fyed is right on!

Mike Eghtebas

ASKER

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 Eghtebas

ASKER

I suppose a union query should do this also.

Dale Fye

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 Eghtebas

ASKER

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

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 Eghtebas

ASKER

Perfect.

Thank you.