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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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


"diaare" wrote:

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" wrote:

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


"diaare" wrote:

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
.

Relevant Pages

  • RE: Compare 1st qtr this year to 4th qtr last year
    ... FROM Styles RIGHT JOIN (CoolerTypes RIGHT JOIN (Models RIGHT JOIN ... I know my problem is with the fact that the query the report is based on is ... "KARL DEWEY" wrote: ... It is based on a crosstab query that sums the production numbers by qtr. ...
    (microsoft.public.access.reports)
  • RE: Compare 1st qtr this year to 4th qtr last year
    ... "diaare" wrote: ... "KARL DEWEY" wrote: ... I know my problem is with the fact that the query the report is based on is ... It is based on a crosstab query that sums the production numbers by qtr. ...
    (microsoft.public.access.reports)
  • RE: Compare 1st qtr this year to 4th qtr last year
    ... "diaare" wrote: ... "KARL DEWEY" wrote: ... I know my problem is with the fact that the query the report is based on is ... It is based on a crosstab query that sums the production numbers by qtr. ...
    (microsoft.public.access.reports)
  • RE: Compare 1st qtr this year to 4th qtr last year
    ... I took your advice, and changed it a little, and was able to get my report to ... "KARL DEWEY" wrote: ... "diaare" wrote: ... I know my problem is with the fact that the query the report is based on is ...
    (microsoft.public.access.reports)
  • Re: LEFT or RIGHT functions
    ... "Wildcats" wrote: ... "KARL DEWEY" wrote: ... The idea of the query was to remove the prefix so they would match. ...
    (microsoft.public.access.gettingstarted)