Compare 1st qtr this year to 4th qtr last year

D

diaare

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
 
D

diaare

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane
 
K

KARL DEWEY

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


diaare said:
Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

KARL DEWEY said:
Post your query SQL.
Click to expand...
Click to expand...
 
D

diaare

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


KARL DEWEY said:
What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


diaare said:
Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

KARL DEWEY said:
Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


diaare said:
It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


KARL DEWEY said:
What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


diaare said:
Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
D

diaare

I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

KARL DEWEY said:
Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


diaare said:
It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


KARL DEWEY said:
What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
D

diaare

Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

diaare said:
I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

KARL DEWEY said:
Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


diaare said:
It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


diaare said:
Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

diaare said:
I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

KARL DEWEY said:
Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
D

diaare

Still getting the same error.

I didn't realize that I could format the date as "yyyy q". This really
points me in the right direction. I will keep playing with it, but if you
have any other suggestions to troubleshoot it let me know.

Thanks so much for all of your help.

Diane

KARL DEWEY said:
I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


diaare said:
Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

diaare said:
I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

:

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Just to check if a hard return got in due to copying and pasting from the post.
--
KARL DEWEY
Build a little - Test a little


diaare said:
Still getting the same error.

I didn't realize that I could format the date as "yyyy q". This really
points me in the right direction. I will keep playing with it, but if you
have any other suggestions to troubleshoot it let me know.

Thanks so much for all of your help.

Diane

KARL DEWEY said:
I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


diaare said:
Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

:

I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

:

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
D

diaare

That was it. Now when I run it I get the Parameter prompt. I entered 2007 4
and received a new error:

"This experssion is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated
elecments. Try simplifying the expression by assigning parts of the
expresion to variables."

I am going to keep at it.

Thanks again for all of your help,
Diane



KARL DEWEY said:
Just to check if a hard return got in due to copying and pasting from the post.
--
KARL DEWEY
Build a little - Test a little


diaare said:
Still getting the same error.

I didn't realize that I could format the date as "yyyy q". This really
points me in the right direction. I will keep playing with it, but if you
have any other suggestions to troubleshoot it let me know.

Thanks so much for all of your help.

Diane

KARL DEWEY said:
I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


:

Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

:

I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

:

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

All I can suggest is to check for hard returns in the where statement, check
the PARAMETERS [Enter year and quarter - yyyy x] TEXT (255); and maybe tear
the where statement apart.
Open the query in design view and in the criteria part of the grid delete
all to the right of [Enter year and quarter - yyyy x] ----- Or
IIf(Right([Enter year and quarter - yyyy x],1)=1,Lef..... and see if you
are getting the one quarter of data.
--
KARL DEWEY
Build a little - Test a little


diaare said:
That was it. Now when I run it I get the Parameter prompt. I entered 2007 4
and received a new error:

"This experssion is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated
elecments. Try simplifying the expression by assigning parts of the
expresion to variables."

I am going to keep at it.

Thanks again for all of your help,
Diane



KARL DEWEY said:
Just to check if a hard return got in due to copying and pasting from the post.
--
KARL DEWEY
Build a little - Test a little


diaare said:
Still getting the same error.

I didn't realize that I could format the date as "yyyy q". This really
points me in the right direction. I will keep playing with it, but if you
have any other suggestions to troubleshoot it let me know.

Thanks so much for all of your help.

Diane

:

I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


:

Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

:

I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

:

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
Click to expand...
Click to expand...
Click to expand...
 
D

diaare

I took your advice, and changed it a little, and was able to get my report to
run correctly.

Here is my SQL:

TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, Styles.StyleID, Styles.StyleType,
CoolerTypes.CoolerTypeID, Production_all.ModelID, Production_all.LineID,
Sum(Production_all.QtyProduced) AS [Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Production_all LEFT JOIN
Models ON Production_all.ModelID = Models.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((CoolerTypes.CoolerTypeID)<>9999))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, Styles.StyleID, Styles.StyleType,
CoolerTypes.CoolerTypeID, Production_all.ModelID, Production_all.LineID
PIVOT Format([ProductionDate],"yyyy q");


Now the only issue I have is I removed the parameters completely and set the
controls on the report to just use the qtrs I needed.

This works for the time being, but next year I will have to change the
controls in the report so it pulls the correct years data.

I am not sure what parameters I need to enter to get it to pull 4th quarter
from one year and 1st qtr from another.

In any case...thanks so much for all of your help. For now I have what I
need...

Diane

KARL DEWEY said:
All I can suggest is to check for hard returns in the where statement, check
the PARAMETERS [Enter year and quarter - yyyy x] TEXT (255); and maybe tear
the where statement apart.
Open the query in design view and in the criteria part of the grid delete
all to the right of [Enter year and quarter - yyyy x] ----- Or
IIf(Right([Enter year and quarter - yyyy x],1)=1,Lef..... and see if you
are getting the one quarter of data.
--
KARL DEWEY
Build a little - Test a little


diaare said:
That was it. Now when I run it I get the Parameter prompt. I entered 2007 4
and received a new error:

"This experssion is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated
elecments. Try simplifying the expression by assigning parts of the
expresion to variables."

I am going to keep at it.

Thanks again for all of your help,
Diane



KARL DEWEY said:
Just to check if a hard return got in due to copying and pasting from the post.
--
KARL DEWEY
Build a little - Test a little


:

Still getting the same error.

I didn't realize that I could format the date as "yyyy q". This really
points me in the right direction. I will keep playing with it, but if you
have any other suggestions to troubleshoot it let me know.

Thanks so much for all of your help.

Diane

:

I do not get that error but you might try changing ---
PARAMETERS [Enter year and quarter - yyyy x] Value;
to this ---
PARAMETERS [Enter year and quarter - yyyy x] TEXT (255);

--
KARL DEWEY
Build a little - Test a little


:

Agggh.

Sorry about the previous sloppy post. Consider all the spelling errors and
typos just that, including the one in the error message. It really read
'[Enter year and quarter - yyyy x]'

:

I pasted your SQL into a new query and recieved the following error:

The Microsoft Jet database engine does not recognize '[Enter year and
quarter - yyy x]' as a valid filed name or expression.

Is there something else I need to do?

This was just a quick first attempt, I will try this evening to read what
you posted more closely so I understand what you changed.

Diane

:

Try this --
PARAMETERS [Enter year and quarter - yyyy x] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>"9999") AND ((Format([ProductionDate],"yyyy
q"))=[Enter year and quarter - yyyy x] Or (Format([ProductionDate],"yyyy
q"))=IIf(Right([Enter year and quarter - yyyy x],1)=1,Left([Enter year and
quarter - yyyy x],4)-1 & " 4",Left([Enter year and quarter - yyyy x],4) & " "
& Right([Enter year and quarter - yyyy x],1)-1)))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType
PIVOT "Year Qtr " & Format([ProductionDate],"yyyy q");

--
KARL DEWEY
Build a little - Test a little


:

It is in the Production table. It has these feilds.

ProductionID (PK)
Production Date
ModelID
LineID
QtyProduced
Notes

I know my problem is with the fact that the query the report is based on is
based on another query (Production_all)

Here is the SQL for that query:

SELECT Production.ProductionID, Production.ProductionDate,
Production.ModelID, Production.QtyProduced, Year([ProductionDate]) AS
TheYear, Month([ProductionDate]) AS theMonth, Production.LineID
FROM Production;


I am smart enough to recognize that this is the base of my problem, but not
smart enough to come up with a way to fix it.

Thanks again for your help.
Diane


:

What table is ProductionDate in?
--
KARL DEWEY
Build a little - Test a little


:

Gladly.

PARAMETERS [Which Year?] Value;
TRANSFORM Sum(Production_all.QtyProduced) AS SumOfQtyProduced
SELECT Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Sum(Production_all.QtyProduced) AS
[Total Of QtyProduced]
FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN
Production_all ON Models.ModelID = Production_all.ModelID) ON
CoolerTypes.CoolerTypeID = Models.CoolerTypeID) ON Styles.StyleID =
CoolerTypes.StyleID
WHERE (((Models.CoolerTypeID)<>9999) AND ((Production_all.TheYear)=[which
year?]))
GROUP BY Models.Horizontal, Models.Freezer, Models.NumberofDoors,
CoolerTypes.ProductionGroup, CoolerTypes.StyleID, Models.CoolerTypeID,
Production_all.ModelID, Styles.StyleType, Production_all.TheYear
PIVOT "Qtr " & Format([ProductionDate],"q");


Thanks, for your offer to help. Please have patience with me as I am fairly
new at this and nearly all self taught (probably incorrectly).

Diane

:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have created a report that compares production data from one qtr to the
previous qtr.

It is based on a crosstab query that sums the production numbers by qtr. I
have a parameter that prompts the user to choose which years production data
to use (filters by year).

This works wonderful when comparing 2nd to 1st, 3rd to 2nd, and 4th to 3rd.
However, obviously, it doesn't work to compare 4th to 1st, because now I have
data from two different production years.

I now realize I need to restructure the query so that I can compare
quarterly data from two years, but I don't know how.

Could someone please guide me in the right direction?

Thanks,
Diane
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

Compare this year/qtr/mo to date with prior year/qtr/mo to date 1
Counting or summing up a group of cells with dates as datas 3
CHARTS: combination 1
Create Qtr Ending Dates 2
How do I set a pivot table to group quarterly reports by F/Y? 2
Space between series 1
Converting Dates to Fiscal Year Quarters 2
Importing a file..then saving back to original file 1

Top