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

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: Seemingly (and probably) complex re: turnaround time
    ... Create a query based on "tblProduction". ... ReceiptToEntry: Entry - OrderReceipt ... DesignToProduction: Production - Design ...
    (microsoft.public.access.queries)
  • RE: Compare 1st qtr this year to 4th qtr last year
    ... "KARL DEWEY" wrote: ... "diaare" 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)