Crosstab Conundrum

  • Thread starter David Mulholland
  • Start date
D

David Mulholland

I'm trying to work up a dynamic crosstab query. The criteria I have to use is
bouncing against a text field holding data like "0810", "0712" - basically a
yymm but as text instead of a date field.

I'm doing it the hard way now...using a query to grab what I need then feed
the crosstab query.

Query1 SQL:
SELECT NoValPay.Month, NoValPay.Name, Left([orgstrRptSeqCode],2) AS MACOM
FROM tblOrganization INNER JOIN NoValPay ON tblOrganization.orgstrUPC =
NoValPay.UIC
WHERE (((NoValPay.Month)>"0710" And (NoValPay.Month)<"0811"))
ORDER BY Left([orgstrRptSeqCode],2);

Crosstab Query:
TRANSFORM Count(qryNoValPay_Rollup.Name) AS CountOfName
SELECT tblParameters.Folder
FROM qryNoValPay_Rollup INNER JOIN tblParameters ON qryNoValPay_Rollup.MACOM
= tblParameters.MACOM
GROUP BY qryNoValPay_Rollup.MACOM, tblParameters.Folder
ORDER BY qryNoValPay_Rollup.MACOM
PIVOT qryNoValPay_Rollup.Month;

Rather than manually change the >"0710" and <"0811" each month to reflect
the last 12 months, what could I do to automate this process.
 
K

KARL DEWEY

Try this --
WHERE (NoValPay.Month)>Format(DateAdd("m",-13,Date()),"yymm") And
(NoValPay.Month)<Format(Date(),"yymm")
 
D

David Mulholland

Works perfectly. Much appreciated.

Now for the next part...hope you can tweak this. Both the feeder query and
crosstab work like I need to. I have a another query feeding off the crosstab
and doing a calculation:

SELECT qryNoValPay_Rollup_Crosstab.*, qryMACOM_Strength.SumOfstreintAsgnStr
AS AsgnStr, ([0810]/[AsgnStr]) AS [Percent]
FROM (tblParameters INNER JOIN qryNoValPay_Rollup_Crosstab ON
tblParameters.Folder = qryNoValPay_Rollup_Crosstab.Folder) INNER JOIN
qryMACOM_Strength ON tblParameters.MACOM = qryMACOM_Strength.MACOM;

How can I change the [0810] to use the lastest field, based on the month
criteria used in your response?

KARL DEWEY said:
Try this --
WHERE (NoValPay.Month)>Format(DateAdd("m",-13,Date()),"yymm") And
(NoValPay.Month)<Format(Date(),"yymm")

--
KARL DEWEY
Build a little - Test a little


David Mulholland said:
I'm trying to work up a dynamic crosstab query. The criteria I have to use is
bouncing against a text field holding data like "0810", "0712" - basically a
yymm but as text instead of a date field.

I'm doing it the hard way now...using a query to grab what I need then feed
the crosstab query.

Query1 SQL:
SELECT NoValPay.Month, NoValPay.Name, Left([orgstrRptSeqCode],2) AS MACOM
FROM tblOrganization INNER JOIN NoValPay ON tblOrganization.orgstrUPC =
NoValPay.UIC
WHERE (((NoValPay.Month)>"0710" And (NoValPay.Month)<"0811"))
ORDER BY Left([orgstrRptSeqCode],2);

Crosstab Query:
TRANSFORM Count(qryNoValPay_Rollup.Name) AS CountOfName
SELECT tblParameters.Folder
FROM qryNoValPay_Rollup INNER JOIN tblParameters ON qryNoValPay_Rollup.MACOM
= tblParameters.MACOM
GROUP BY qryNoValPay_Rollup.MACOM, tblParameters.Folder
ORDER BY qryNoValPay_Rollup.MACOM
PIVOT qryNoValPay_Rollup.Month;

Rather than manually change the >"0710" and <"0811" each month to reflect
the last 12 months, what could I do to automate this process.
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not think you can.

Extract the data you need using this --
WHERE (NoValPay.Month) = Format(DateAdd("m",-1,Date()),"yymm")

I see another problem with your query. You are creating alias AsgnStr and
then using it in the same query. This usually does not work as it maybe used
before it is created in the query processing. You need to use the
calculation again instead of the alias.
--
KARL DEWEY
Build a little - Test a little


David Mulholland said:
Works perfectly. Much appreciated.

Now for the next part...hope you can tweak this. Both the feeder query and
crosstab work like I need to. I have a another query feeding off the crosstab
and doing a calculation:

SELECT qryNoValPay_Rollup_Crosstab.*, qryMACOM_Strength.SumOfstreintAsgnStr
AS AsgnStr, ([0810]/[AsgnStr]) AS [Percent]
FROM (tblParameters INNER JOIN qryNoValPay_Rollup_Crosstab ON
tblParameters.Folder = qryNoValPay_Rollup_Crosstab.Folder) INNER JOIN
qryMACOM_Strength ON tblParameters.MACOM = qryMACOM_Strength.MACOM;

How can I change the [0810] to use the lastest field, based on the month
criteria used in your response?

KARL DEWEY said:
Try this --
WHERE (NoValPay.Month)>Format(DateAdd("m",-13,Date()),"yymm") And
(NoValPay.Month)<Format(Date(),"yymm")

--
KARL DEWEY
Build a little - Test a little


David Mulholland said:
I'm trying to work up a dynamic crosstab query. The criteria I have to use is
bouncing against a text field holding data like "0810", "0712" - basically a
yymm but as text instead of a date field.

I'm doing it the hard way now...using a query to grab what I need then feed
the crosstab query.

Query1 SQL:
SELECT NoValPay.Month, NoValPay.Name, Left([orgstrRptSeqCode],2) AS MACOM
FROM tblOrganization INNER JOIN NoValPay ON tblOrganization.orgstrUPC =
NoValPay.UIC
WHERE (((NoValPay.Month)>"0710" And (NoValPay.Month)<"0811"))
ORDER BY Left([orgstrRptSeqCode],2);

Crosstab Query:
TRANSFORM Count(qryNoValPay_Rollup.Name) AS CountOfName
SELECT tblParameters.Folder
FROM qryNoValPay_Rollup INNER JOIN tblParameters ON qryNoValPay_Rollup.MACOM
= tblParameters.MACOM
GROUP BY qryNoValPay_Rollup.MACOM, tblParameters.Folder
ORDER BY qryNoValPay_Rollup.MACOM
PIVOT qryNoValPay_Rollup.Month;

Rather than manually change the >"0710" and <"0811" each month to reflect
the last 12 months, what could I do to automate this process.
Click to expand...
Click to expand...
Click to expand...
 
D

David Mulholland

Thanks again for the help. I went ahead and aliased the AsgnStr in the
previous query. Even though it's worked perfectly since created, i did this
to forstall any future problems.

KARL DEWEY said:
I do not think you can.

Extract the data you need using this --
WHERE (NoValPay.Month) = Format(DateAdd("m",-1,Date()),"yymm")

I see another problem with your query. You are creating alias AsgnStr and
then using it in the same query. This usually does not work as it maybe used
before it is created in the query processing. You need to use the
calculation again instead of the alias.
--
KARL DEWEY
Build a little - Test a little


David Mulholland said:
Works perfectly. Much appreciated.

Now for the next part...hope you can tweak this. Both the feeder query and
crosstab work like I need to. I have a another query feeding off the crosstab
and doing a calculation:

SELECT qryNoValPay_Rollup_Crosstab.*, qryMACOM_Strength.SumOfstreintAsgnStr
AS AsgnStr, ([0810]/[AsgnStr]) AS [Percent]
FROM (tblParameters INNER JOIN qryNoValPay_Rollup_Crosstab ON
tblParameters.Folder = qryNoValPay_Rollup_Crosstab.Folder) INNER JOIN
qryMACOM_Strength ON tblParameters.MACOM = qryMACOM_Strength.MACOM;

How can I change the [0810] to use the lastest field, based on the month
criteria used in your response?

KARL DEWEY said:
Try this --
WHERE (NoValPay.Month)>Format(DateAdd("m",-13,Date()),"yymm") And
(NoValPay.Month)<Format(Date(),"yymm")

--
KARL DEWEY
Build a little - Test a little


:

I'm trying to work up a dynamic crosstab query. The criteria I have to use is
bouncing against a text field holding data like "0810", "0712" - basically a
yymm but as text instead of a date field.

I'm doing it the hard way now...using a query to grab what I need then feed
the crosstab query.

Query1 SQL:
SELECT NoValPay.Month, NoValPay.Name, Left([orgstrRptSeqCode],2) AS MACOM
FROM tblOrganization INNER JOIN NoValPay ON tblOrganization.orgstrUPC =
NoValPay.UIC
WHERE (((NoValPay.Month)>"0710" And (NoValPay.Month)<"0811"))
ORDER BY Left([orgstrRptSeqCode],2);

Crosstab Query:
TRANSFORM Count(qryNoValPay_Rollup.Name) AS CountOfName
SELECT tblParameters.Folder
FROM qryNoValPay_Rollup INNER JOIN tblParameters ON qryNoValPay_Rollup.MACOM
= tblParameters.MACOM
GROUP BY qryNoValPay_Rollup.MACOM, tblParameters.Folder
ORDER BY qryNoValPay_Rollup.MACOM
PIVOT qryNoValPay_Rollup.Month;

Rather than manually change the >"0710" and <"0811" each month to reflect
the last 12 months, what could I do to automate this process.
Click to expand...
Click to expand...
Click to expand...
 
Top