Crosstab Query?-NOT BLANK

[DissentChick]

Sorry for the previous blank post, finger slipped. Hi there, I am having
trouble designing a query to bring the following information so I can put it
all on one report (I'm not opposed to using subreports): Number of PM's
issued by month, no. of issued PM's for month completed in month, and the #
of previously issued PM's completed within that month.My fields are: Tbl
PMLog
PMOrderID StartDate DateCompleted (The StartDate is the issue date)
What kind of query would I use, and if it is a crosstab, how do I set it up?
I have never used them. Thanks so much in advance!

 

[ken]

Chick,

Yes, it sounds like you need a crosstab query. Make a query in design mode,
and add all the fields you mention below. Then go to the menu at the top and
change the query type to crosstab. I think you want a calculated field that
looks something like month(date) & "/" & year(date) for your column header,
and another calculated field that determines what "type" of pm it is for your
row header. This field would involved several nested iif() functions and I
will take a shot at it in a sec. For the Value you want a count(*) function,
which will count the number of pm's (i assume 1 row in table = 1 pm). If pm
is a number field rather than a row, you need sum(pm) for this instead of
count(*).

This paragraph above may be a little confusing, but if you give crosstabs a
try I am sure you can figure out what I mean. You just need a column header,
a row header, and a value. The column/row headers should be set to "group
by" and the value should be a sum() or count() depending on your structure,
as I mentioned above.

Your row header calculated field would look something like this:
iif(datediff("m",now(),startdate)<0 AND dateCompleted is not
null,"Previous",iif(datediff("m",now(),startdate)=0 AND dateCompleted is not
null,"Started/Completed In Month",iif(datediff("m",now(),startdate)=0 AND
dateCompleted is null,"Started This Month But Not Done")))

I think that's it.. Keep in mind i have no idea what a PM is.

Can I get a free record if this helps? j/k

Ken

(former dj)

 

[KARL DEWEY]

You can do it with multiple queries.
PMLogQuery-1
SELECT "X" AS x, Sum(1) AS [Completed last month]
FROM TblPMLog
WHERE (((Format([DateCompleted],"m yyyy"))=Format(Date()-Day(Date()),"m
yyyy")))
GROUP BY "X";

PMLogQuery-2
SELECT "X" AS x, Sum(1) AS [Completed as scheduled]
FROM TblPMLog
WHERE ((((Format([StartDate],"m yyyy")))=Format(Date()-Day(Date()),"m yyyy")))
GROUP BY "X";

PMLogQuery-3
SELECT "X" AS x, Sum(1) AS [Completed from prior months]
FROM TblPMLog
WHERE (((Format([StartDate],"m yyyy"))=Format(Date()-Day(Date()),"m yyyy"))
AND ((Format([DateCompleted],"m yyyy"))=Format(Date()-Day(Date()),"m yyyy")))
GROUP BY "X";

PM_Log
SELECT [PMLogQuery-1].[Completed last month], [PMLogQuery-2].[Completed as
scheduled], [PMLogQuery-3].[Completed from prior months]
FROM [PMLogQuery-1], [PMLogQuery-2], [PMLogQuery-3];

 

[DissentChick]

Hey guys, Thanks so much for the help! Ken- check out nbtnc.com, and click on
either MUSIC or the MySpace icon on the home page- There's free music (3-4
songs) there for you to enjoy! I'll let you know if I have any trouble with
the query, I'll let you know! Thanks again!

 

[DissentChick]

Ken, (or anybody!)- I tried to put those iif functions in, and everything
looked ok except I've got a data type mismatch. Here's what the fields in the
crosstab look like-
PMOrderID- Count, Value (Autonumber)
StartDate- Group By, Column Heading (date)
DateCompleted- Group By, Row Heading (date)
DateCompleted- Group By, Row Heading, nested function (date)
Being so NOT crosstab savvy, how do I match it up? Thanx in Advance

 

[Duane Hookom]

First, normalize your data (I wonder how many times per month this is
suggested).
SELECT PMOrderID, StartDate as ActivityDate, "Start" as Activity
FROM tblPMLog
WHERE StartDate is not Null
UNION ALL
SELECT PMOrderID, DateCompleted, "Completed"
FROM tblPMLog;

Save this query as quniActivityDates. Then create a crosstab query based on
quniActivityDates that has
Row Heading: Format(ActivityDate,"yyyy mm")
Column Heading: Activity
Value: Count of PMOrderID

 

[DissentChick]

Thank you Duane- I think this is close to what I need!

 

[efwerwerr]

i want myspace....

You can do it with multiple queries.
PMLogQuery-1
SELECT "X" AS x, Sum(1) AS [Completed last month]
FROM TblPMLog
WHERE (((Format([DateCompleted],"m yyyy"))=Format(Date()-Day(Date()),"m
yyyy")))
GROUP BY "X";

PMLogQuery-2
SELECT "X" AS x, Sum(1) AS [Completed as scheduled]
FROM TblPMLog
WHERE ((((Format([StartDate],"m yyyy")))=Format(Date()-Day(Date()),"m yyyy")))
GROUP BY "X";

PMLogQuery-3
SELECT "X" AS x, Sum(1) AS [Completed from prior months]
FROM TblPMLog
WHERE (((Format([StartDate],"m yyyy"))=Format(Date()-Day(Date()),"m yyyy"))
AND ((Format([DateCompleted],"m yyyy"))=Format(Date()-Day(Date()),"m yyyy")))
GROUP BY "X";

PM_Log
SELECT [PMLogQuery-1].[Completed last month], [PMLogQuery-2].[Completed as
scheduled], [PMLogQuery-3].[Completed from prior months]
FROM [PMLogQuery-1], [PMLogQuery-2], [PMLogQuery-3];

Sorry for the previous blank post, finger slipped. Hi there, I am having
trouble designing a query to bring the following information so I can put it
all on one report (I'm not opposed to using subreports): Number of PM's
issued by month, no. of issued PM's for month completed in month, and the #
of previously issued PM's completed within that month.My fields are: Tbl
PMLog
PMOrderID StartDate DateCompleted (The StartDate is the issue date)
What kind of query would I use, and if it is a crosstab, how do I set it up?
I have never used them. Thanks so much in advance!