Doesn't recognize Crosstab gen Fields

B

Bonnie A

Hi everyone! Using A02 on XP. I have a table with multiple records for 1-4
quarters. I have my first query (Query01) that pulls by [GP] (contract
number) and [BillDate]. Usually there are 4 records (one for each quarter).
Sometimes though, there are only 1, 2 or 3 (never zero or there is just no
data). I have to open Query01 in design view to change the GP and BillDate
criteria. I can't use parameter inquiry because Query02 is a crosstab and
doesn't like parameters.

Query01 pulls the data I need. Query02 summarizes the data (adds the fees
for each quarter and lists the fees by quarter) and Query03 appends the data
to a FeesTable (I concatenate the quarterly totals so I can list the
individual amounts that make up the total due).

I tried this:

Pieces: "Cycle fee amounts: " & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " &
IIf(([Qtr 2]) Is Null,0,[Qtr 2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) &
"; " & IIf(([Qtr 4]) Is Null,0,[Qtr 4])

But, because this one only has data in [Qtr 4], I get the error: "Microsoft
Jet DB Engine does not recognize '[Qtr 1]' as a valid field name or
expression."

How can I allow for Qtrs that may not exist? Can I? I would appreciate any
help or advice you can provide. Thank you in advance for your time!
 
K

KARL DEWEY

You need to define the parameters in the crosstab query and preset the output
columns.
Post your SQL for your Q1 and Q2.
 
B

Bonnie A

Hi Karl,

Thanks very much for the quick reply.

Here is my Query01:

SELECT ParticipantStmtTable.PlanNum, [ParticipantCount]*[CostPer] AS FeeAmt,
ParticipantStmtTable.DateBilled, ParticipantStmtTable.CycleDate
FROM ParticipantStmtTable
WHERE (((ParticipantStmtTable.PlanNum)="GP3194") AND
((ParticipantStmtTable.DateBilled)=#7/16/2008#) AND
((ParticipantStmtTable.Untimely)=0) AND
((ParticipantStmtTable.OverRideFee)=0))
ORDER BY ParticipantStmtTable.CycleDate
WITH OWNERACCESS OPTION;

Here is my Query02:

TRANSFORM Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS SumOfFeeAmt
SELECT qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum,
Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS [Total Of FeeAmt]
FROM qPartStmtBill5aCreateFeeRecordOneGP01
GROUP BY qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum
PIVOT "Qtr " & Format([CycleDate],"q");

Here is my Query03:

INSERT INTO [Fees Table] ( [Plan #], [Amount Billed], FeeComments, [Date
Billed], [Bill Type], CreatedBy, [Serv Rendered] )
SELECT qPartStmtBill5aCreateFeeRecordOneGP02.PlanNum,
qPartStmtBill5aCreateFeeRecordOneGP02.[Total Of FeeAmt], "Cycle fee amounts:
" & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " & IIf(([Qtr 2]) Is Null,0,[Qtr
2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) & "; " & IIf(([Qtr 4]) Is
Null,0,[Qtr 4]) AS Pieces, "07/16/2008" AS BillDate, "S" AS BillType,
CurrentUser() AS CreatedBy, "Participant Stmts Mailed" AS Service
FROM qPartStmtBill5aCreateFeeRecordOneGP02
WITH OWNERACCESS OPTION;

I can't put the parameter in Query01 such as [What contract number?] or
[What date billed?] because Query02 (crosstab) gives me the error:

"Microsoft Jet DB Engine does not recognize '[What contract number?] as a
valid field name or expression."

Not sure what you mean by defining the parameters in the crosstab. I tried
to add a parameter to the criteria line but got the same error message.

If you can assist me in performing magic, I would love to try. I come
across this in crosstabs alot and would like to have a fix.

Thanks again for your time!

--
Bonnie W. Anderson
Cincinnati, OH


KARL DEWEY said:
You need to define the parameters in the crosstab query and preset the output
columns.
Post your SQL for your Q1 and Q2.
--
KARL DEWEY
Build a little - Test a little


Bonnie A said:
Hi everyone! Using A02 on XP. I have a table with multiple records for 1-4
quarters. I have my first query (Query01) that pulls by [GP] (contract
number) and [BillDate]. Usually there are 4 records (one for each quarter).
Sometimes though, there are only 1, 2 or 3 (never zero or there is just no
data). I have to open Query01 in design view to change the GP and BillDate
criteria. I can't use parameter inquiry because Query02 is a crosstab and
doesn't like parameters.

Query01 pulls the data I need. Query02 summarizes the data (adds the fees
for each quarter and lists the fees by quarter) and Query03 appends the data
to a FeesTable (I concatenate the quarterly totals so I can list the
individual amounts that make up the total due).

I tried this:

Pieces: "Cycle fee amounts: " & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " &
IIf(([Qtr 2]) Is Null,0,[Qtr 2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) &
"; " & IIf(([Qtr 4]) Is Null,0,[Qtr 4])

But, because this one only has data in [Qtr 4], I get the error: "Microsoft
Jet DB Engine does not recognize '[Qtr 1]' as a valid field name or
expression."

How can I allow for Qtrs that may not exist? Can I? I would appreciate any
help or advice you can provide. Thank you in advance for your time!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try these and make sure to remove any returns the copying and pasting puts in
--
SELECT ParticipantStmtTable.PlanNum, [ParticipantCount]*[CostPer] AS FeeAmt,
ParticipantStmtTable.DateBilled, ParticipantStmtTable.CycleDate
FROM ParticipantStmtTable
WHERE (((ParticipantStmtTable.PlanNum)=[What contract number?]) AND
((ParticipantStmtTable.DateBilled)=[What date billed?]) AND
((ParticipantStmtTable.Untimely)=0) AND
((ParticipantStmtTable.OverRideFee)=0))
ORDER BY ParticipantStmtTable.CycleDate
WITH OWNERACCESS OPTION;

Here is my Query02:
PARAMETERS [What date billed?] DateTime, [What contract number?] Text (
255 );
TRANSFORM Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS SumOfFeeAmt
SELECT qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum,
Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS [Total Of FeeAmt]
FROM qPartStmtBill5aCreateFeeRecordOneGP01
GROUP BY qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum
PIVOT "Qtr " & Format([CycleDate],"q");

--
KARL DEWEY
Build a little - Test a little


Bonnie A said:
Hi Karl,

Thanks very much for the quick reply.

Here is my Query01:

SELECT ParticipantStmtTable.PlanNum, [ParticipantCount]*[CostPer] AS FeeAmt,
ParticipantStmtTable.DateBilled, ParticipantStmtTable.CycleDate
FROM ParticipantStmtTable
WHERE (((ParticipantStmtTable.PlanNum)="GP3194") AND
((ParticipantStmtTable.DateBilled)=#7/16/2008#) AND
((ParticipantStmtTable.Untimely)=0) AND
((ParticipantStmtTable.OverRideFee)=0))
ORDER BY ParticipantStmtTable.CycleDate
WITH OWNERACCESS OPTION;

Here is my Query02:

TRANSFORM Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS SumOfFeeAmt
SELECT qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum,
Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS [Total Of FeeAmt]
FROM qPartStmtBill5aCreateFeeRecordOneGP01
GROUP BY qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum
PIVOT "Qtr " & Format([CycleDate],"q");

Here is my Query03:

INSERT INTO [Fees Table] ( [Plan #], [Amount Billed], FeeComments, [Date
Billed], [Bill Type], CreatedBy, [Serv Rendered] )
SELECT qPartStmtBill5aCreateFeeRecordOneGP02.PlanNum,
qPartStmtBill5aCreateFeeRecordOneGP02.[Total Of FeeAmt], "Cycle fee amounts:
" & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " & IIf(([Qtr 2]) Is Null,0,[Qtr
2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) & "; " & IIf(([Qtr 4]) Is
Null,0,[Qtr 4]) AS Pieces, "07/16/2008" AS BillDate, "S" AS BillType,
CurrentUser() AS CreatedBy, "Participant Stmts Mailed" AS Service
FROM qPartStmtBill5aCreateFeeRecordOneGP02
WITH OWNERACCESS OPTION;

I can't put the parameter in Query01 such as [What contract number?] or
[What date billed?] because Query02 (crosstab) gives me the error:

"Microsoft Jet DB Engine does not recognize '[What contract number?] as a
valid field name or expression."

Not sure what you mean by defining the parameters in the crosstab. I tried
to add a parameter to the criteria line but got the same error message.

If you can assist me in performing magic, I would love to try. I come
across this in crosstabs alot and would like to have a fix.

Thanks again for your time!

--
Bonnie W. Anderson
Cincinnati, OH


KARL DEWEY said:
You need to define the parameters in the crosstab query and preset the output
columns.
Post your SQL for your Q1 and Q2.
--
KARL DEWEY
Build a little - Test a little


Bonnie A said:
Hi everyone! Using A02 on XP. I have a table with multiple records for 1-4
quarters. I have my first query (Query01) that pulls by [GP] (contract
number) and [BillDate]. Usually there are 4 records (one for each quarter).
Sometimes though, there are only 1, 2 or 3 (never zero or there is just no
data). I have to open Query01 in design view to change the GP and BillDate
criteria. I can't use parameter inquiry because Query02 is a crosstab and
doesn't like parameters.

Query01 pulls the data I need. Query02 summarizes the data (adds the fees
for each quarter and lists the fees by quarter) and Query03 appends the data
to a FeesTable (I concatenate the quarterly totals so I can list the
individual amounts that make up the total due).

I tried this:

Pieces: "Cycle fee amounts: " & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " &
IIf(([Qtr 2]) Is Null,0,[Qtr 2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) &
"; " & IIf(([Qtr 4]) Is Null,0,[Qtr 4])

But, because this one only has data in [Qtr 4], I get the error: "Microsoft
Jet DB Engine does not recognize '[Qtr 1]' as a valid field name or
expression."

How can I allow for Qtrs that may not exist? Can I? I would appreciate any
help or advice you can provide. Thank you in advance for your time!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I forgot to change Q2 as below --
PIVOT "Qtr " & Format([CycleDate],"q") IN("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4");

The above produces the columns even if there is no data for the quarter.
--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try these and make sure to remove any returns the copying and pasting puts in
--
SELECT ParticipantStmtTable.PlanNum, [ParticipantCount]*[CostPer] AS FeeAmt,
ParticipantStmtTable.DateBilled, ParticipantStmtTable.CycleDate
FROM ParticipantStmtTable
WHERE (((ParticipantStmtTable.PlanNum)=[What contract number?]) AND
((ParticipantStmtTable.DateBilled)=[What date billed?]) AND
((ParticipantStmtTable.Untimely)=0) AND
((ParticipantStmtTable.OverRideFee)=0))
ORDER BY ParticipantStmtTable.CycleDate
WITH OWNERACCESS OPTION;

Here is my Query02:
PARAMETERS [What date billed?] DateTime, [What contract number?] Text (
255 );
TRANSFORM Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS SumOfFeeAmt
SELECT qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum,
Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS [Total Of FeeAmt]
FROM qPartStmtBill5aCreateFeeRecordOneGP01
GROUP BY qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum
PIVOT "Qtr " & Format([CycleDate],"q");

--
KARL DEWEY
Build a little - Test a little


Bonnie A said:
Hi Karl,

Thanks very much for the quick reply.

Here is my Query01:

SELECT ParticipantStmtTable.PlanNum, [ParticipantCount]*[CostPer] AS FeeAmt,
ParticipantStmtTable.DateBilled, ParticipantStmtTable.CycleDate
FROM ParticipantStmtTable
WHERE (((ParticipantStmtTable.PlanNum)="GP3194") AND
((ParticipantStmtTable.DateBilled)=#7/16/2008#) AND
((ParticipantStmtTable.Untimely)=0) AND
((ParticipantStmtTable.OverRideFee)=0))
ORDER BY ParticipantStmtTable.CycleDate
WITH OWNERACCESS OPTION;

Here is my Query02:

TRANSFORM Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS SumOfFeeAmt
SELECT qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum,
Sum(qPartStmtBill5aCreateFeeRecordOneGP01.FeeAmt) AS [Total Of FeeAmt]
FROM qPartStmtBill5aCreateFeeRecordOneGP01
GROUP BY qPartStmtBill5aCreateFeeRecordOneGP01.PlanNum
PIVOT "Qtr " & Format([CycleDate],"q");

Here is my Query03:

INSERT INTO [Fees Table] ( [Plan #], [Amount Billed], FeeComments, [Date
Billed], [Bill Type], CreatedBy, [Serv Rendered] )
SELECT qPartStmtBill5aCreateFeeRecordOneGP02.PlanNum,
qPartStmtBill5aCreateFeeRecordOneGP02.[Total Of FeeAmt], "Cycle fee amounts:
" & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " & IIf(([Qtr 2]) Is Null,0,[Qtr
2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) & "; " & IIf(([Qtr 4]) Is
Null,0,[Qtr 4]) AS Pieces, "07/16/2008" AS BillDate, "S" AS BillType,
CurrentUser() AS CreatedBy, "Participant Stmts Mailed" AS Service
FROM qPartStmtBill5aCreateFeeRecordOneGP02
WITH OWNERACCESS OPTION;

I can't put the parameter in Query01 such as [What contract number?] or
[What date billed?] because Query02 (crosstab) gives me the error:

"Microsoft Jet DB Engine does not recognize '[What contract number?] as a
valid field name or expression."

Not sure what you mean by defining the parameters in the crosstab. I tried
to add a parameter to the criteria line but got the same error message.

If you can assist me in performing magic, I would love to try. I come
across this in crosstabs alot and would like to have a fix.

Thanks again for your time!

--
Bonnie W. Anderson
Cincinnati, OH


KARL DEWEY said:
You need to define the parameters in the crosstab query and preset the output
columns.
Post your SQL for your Q1 and Q2.
--
KARL DEWEY
Build a little - Test a little


:

Hi everyone! Using A02 on XP. I have a table with multiple records for 1-4
quarters. I have my first query (Query01) that pulls by [GP] (contract
number) and [BillDate]. Usually there are 4 records (one for each quarter).
Sometimes though, there are only 1, 2 or 3 (never zero or there is just no
data). I have to open Query01 in design view to change the GP and BillDate
criteria. I can't use parameter inquiry because Query02 is a crosstab and
doesn't like parameters.

Query01 pulls the data I need. Query02 summarizes the data (adds the fees
for each quarter and lists the fees by quarter) and Query03 appends the data
to a FeesTable (I concatenate the quarterly totals so I can list the
individual amounts that make up the total due).

I tried this:

Pieces: "Cycle fee amounts: " & IIf(([Qtr 1]) Is Null,0,[Qtr 1]) & "; " &
IIf(([Qtr 2]) Is Null,0,[Qtr 2]) & "; " & IIf(([Qtr 3]) Is Null,0,[Qtr 3]) &
"; " & IIf(([Qtr 4]) Is Null,0,[Qtr 4])

But, because this one only has data in [Qtr 4], I get the error: "Microsoft
Jet DB Engine does not recognize '[Qtr 1]' as a valid field name or
expression."

How can I allow for Qtrs that may not exist? Can I? I would appreciate any
help or advice you can provide. Thank you in advance for your time!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie A

Karl!!! I LOVE IT! I've got to keep this to play with and see what I can
learn from it. Was a bit disappointed when I got the same "Doesn't
recognize..." error but went back and saw your extra post to lock in the
fields. SO glad you put that in. Absolutely saves me time for this process.

Thank you very much for being such a wonderful help!
 

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

Crosstab - Calculation 3
Count person only 1 time and show person not counted 10
Pivot tabel formulas 1
Totalling SubReports - have tried and tried 7
Pie Charts 1
Query to calculate percent, but not greater that 100% 6
Sub-report not showing up 3
Creating multiple summaries within a single report by date 1

Top