Cross Tab Queries

R

RPM_AUS

Hi

I am using cross tab query to summarize the results from a query which is
pulling information from our SQL database the information that it is
summarizing is related to marketing list that clients have agreed to receive
electronically,

Using the Total function "Where" on the field that contains either the value
yes or the value no in regards to what marketing lists they have agreed to
receive. In the criteria option I input "no" and also "yes" this works fine
returns all the correct results in table view as needed. The problem is that
it assigning a value of 1 to both the yes and no. Is there a way that I can
assign the value of 2 to any field that equals no, which would then allow me
to tell the difference between a yes as it will equal 1 and a no will equal
2.

Sorry if I have not explained this well.

Thanks

Michael.
 
R

RPM_AUS

This first statement is of the combined queries

SELECT dbo_Contact.*, [Copy of Cross Tab Indivduals With marketing Items
NO].AUDIT, [Copy of Cross Tab Indivduals With marketing Items NO].[BUS_
DIGES], [Copy of Cross Tab Indivduals With marketing Items NO].CNIS, [Copy of
Cross Tab Indivduals With marketing Items NO].[Dental Wealth], [Copy of Cross
Tab Indivduals With marketing Items NO].PARACET, [Copy of Cross Tab
Indivduals With marketing Items NO].[Super Sleuth], [Copy of Cross Tab
Indivduals With marketing Items NO].[Tax Audit], [Copy of Cross Tab
Indivduals With marketing Items NO].[Xmas Card], [Copy of Cross Tab
Indivduals With marketing Items NO].[Breaking News], [Copy of Cross Tab
Indivduals With marketing Items NO].[The Blue Print]
FROM (dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) INNER JOIN [Copy of Cross Tab Indivduals With
marketing Items NO] ON dbo_Contact.ContactID = [Copy of Cross Tab Indivduals
With marketing Items NO].ContactID
WHERE (((dbo_Contact.ContactTypeID)=1));

This next one is just of the cross tab querry it self

TRANSFORM Count(dbo_ExtraContact.ExtraValueID) AS CountOfExtraValueID
SELECT dbo_Contact.ContactID
FROM ((dbo_ExtraContact INNER JOIN dbo_ExtraValue ON
dbo_ExtraContact.ExtraValueID = dbo_ExtraValue.ExtraValueID) INNER JOIN
(dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) ON dbo_ExtraContact.ContactID =
dbo_Contact.ContactID) INNER JOIN dbo_ExtraField ON
dbo_ExtraValue.ExtraFieldID = dbo_ExtraField.ExtraFieldID
WHERE (((dbo_ExtraValue.Value)="no") AND ((dbo_Contact.ContactTypeID)=1)) OR
(((dbo_ExtraValue.Value)="yes"))
GROUP BY dbo_Contact.ContactID, dbo_Contact.ContactTypeID
PIVOT dbo_ExtraField.FieldName;

Thanks

Michael.
 
S

Stewart Berman

Still not sure what your data looks like. If all the records with the sane dbo_Contact.ContactID
have the same value for the dbo_ExtraValue.Value field you can:

Change the SELECT clause to:
SELECT dbo_Contact.ContactID, IIF((dbo_ExtraValue.Value="yes"),1,2) as [YesNo]
or if you want to see the value "yes" or "no" use:
SELECT dbo_Contact.ContactID, dbo_ExtraValue.Value as [YesNo]


RPM_AUS said:
This first statement is of the combined queries

SELECT dbo_Contact.*, [Copy of Cross Tab Indivduals With marketing Items
NO].AUDIT, [Copy of Cross Tab Indivduals With marketing Items NO].[BUS_
DIGES], [Copy of Cross Tab Indivduals With marketing Items NO].CNIS, [Copy of
Cross Tab Indivduals With marketing Items NO].[Dental Wealth], [Copy of Cross
Tab Indivduals With marketing Items NO].PARACET, [Copy of Cross Tab
Indivduals With marketing Items NO].[Super Sleuth], [Copy of Cross Tab
Indivduals With marketing Items NO].[Tax Audit], [Copy of Cross Tab
Indivduals With marketing Items NO].[Xmas Card], [Copy of Cross Tab
Indivduals With marketing Items NO].[Breaking News], [Copy of Cross Tab
Indivduals With marketing Items NO].[The Blue Print]
FROM (dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) INNER JOIN [Copy of Cross Tab Indivduals With
marketing Items NO] ON dbo_Contact.ContactID = [Copy of Cross Tab Indivduals
With marketing Items NO].ContactID
WHERE (((dbo_Contact.ContactTypeID)=1));

This next one is just of the cross tab querry it self

TRANSFORM Count(dbo_ExtraContact.ExtraValueID) AS CountOfExtraValueID
SELECT dbo_Contact.ContactID
FROM ((dbo_ExtraContact INNER JOIN dbo_ExtraValue ON
dbo_ExtraContact.ExtraValueID = dbo_ExtraValue.ExtraValueID) INNER JOIN
(dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) ON dbo_ExtraContact.ContactID =
dbo_Contact.ContactID) INNER JOIN dbo_ExtraField ON
dbo_ExtraValue.ExtraFieldID = dbo_ExtraField.ExtraFieldID
WHERE (((dbo_ExtraValue.Value)="no") AND ((dbo_Contact.ContactTypeID)=1)) OR
(((dbo_ExtraValue.Value)="yes"))
GROUP BY dbo_Contact.ContactID, dbo_Contact.ContactTypeID
PIVOT dbo_ExtraField.FieldName;

Thanks

Michael.


KARL DEWEY said:
Post the SQL of your queries.
Click to expand...
Click to expand...
 
K

KARL DEWEY

I also do not follow. You first post says 'Using the Total function "Where"'
but where is not a total function.

It seems you want to count Yes & Nos but why pivot on 'FieldName'?

What is the purpose of all the fields in first query - BUS_DIGES, CNIS,
Dental Wealth, PARACET, Super Sleuth, Tax Audit, Xmas Card, Breaking News,
and The Blue Print?

Could you not just use the crosstab query alone?

Post some sample data and example of how you want the crosstab results to
look like.

Stewart Berman said:
Still not sure what your data looks like. If all the records with the sane dbo_Contact.ContactID
have the same value for the dbo_ExtraValue.Value field you can:

Change the SELECT clause to:
SELECT dbo_Contact.ContactID, IIF((dbo_ExtraValue.Value="yes"),1,2) as [YesNo]
or if you want to see the value "yes" or "no" use:
SELECT dbo_Contact.ContactID, dbo_ExtraValue.Value as [YesNo]


RPM_AUS said:
This first statement is of the combined queries

SELECT dbo_Contact.*, [Copy of Cross Tab Indivduals With marketing Items
NO].AUDIT, [Copy of Cross Tab Indivduals With marketing Items NO].[BUS_
DIGES], [Copy of Cross Tab Indivduals With marketing Items NO].CNIS, [Copy of
Cross Tab Indivduals With marketing Items NO].[Dental Wealth], [Copy of Cross
Tab Indivduals With marketing Items NO].PARACET, [Copy of Cross Tab
Indivduals With marketing Items NO].[Super Sleuth], [Copy of Cross Tab
Indivduals With marketing Items NO].[Tax Audit], [Copy of Cross Tab
Indivduals With marketing Items NO].[Xmas Card], [Copy of Cross Tab
Indivduals With marketing Items NO].[Breaking News], [Copy of Cross Tab
Indivduals With marketing Items NO].[The Blue Print]
FROM (dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) INNER JOIN [Copy of Cross Tab Indivduals With
marketing Items NO] ON dbo_Contact.ContactID = [Copy of Cross Tab Indivduals
With marketing Items NO].ContactID
WHERE (((dbo_Contact.ContactTypeID)=1));

This next one is just of the cross tab querry it self

TRANSFORM Count(dbo_ExtraContact.ExtraValueID) AS CountOfExtraValueID
SELECT dbo_Contact.ContactID
FROM ((dbo_ExtraContact INNER JOIN dbo_ExtraValue ON
dbo_ExtraContact.ExtraValueID = dbo_ExtraValue.ExtraValueID) INNER JOIN
(dbo_Contact INNER JOIN dbo_ContactType ON dbo_Contact.ContactTypeID =
dbo_ContactType.ContactTypeID) ON dbo_ExtraContact.ContactID =
dbo_Contact.ContactID) INNER JOIN dbo_ExtraField ON
dbo_ExtraValue.ExtraFieldID = dbo_ExtraField.ExtraFieldID
WHERE (((dbo_ExtraValue.Value)="no") AND ((dbo_Contact.ContactTypeID)=1)) OR
(((dbo_ExtraValue.Value)="yes"))
GROUP BY dbo_Contact.ContactID, dbo_Contact.ContactTypeID
PIVOT dbo_ExtraField.FieldName;

Thanks

Michael.


KARL DEWEY said:
Post the SQL of your queries.

:

Hi

I am using cross tab query to summarize the results from a query which is
pulling information from our SQL database the information that it is
summarizing is related to marketing list that clients have agreed to receive
electronically,

Using the Total function "Where" on the field that contains either the value
yes or the value no in regards to what marketing lists they have agreed to
receive. In the criteria option I input "no" and also "yes" this works fine
returns all the correct results in table view as needed. The problem is that
it assigning a value of 1 to both the yes and no. Is there a way that I can
assign the value of 2 to any field that equals no, which would then allow me
to tell the difference between a yes as it will equal 1 and a no will equal
2.

Sorry if I have not explained this well.

Thanks

Michael.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field Does Not Appear in Cross Tab 1
Cross Tab Query...Help 4
Cross Tab Query with two values 1
cross tab query outer join 1
IIF Function Within A Query 6
Cross tab query solution 4
Cross Tab Query and Missing Date(s) 1
CrossTab Query How Do I change Column Headings... 2

Top