If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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! -- Bonnie W. Anderson Cincinnati, OH |
#2
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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" wrote: 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! -- Bonnie W. Anderson Cincinnati, OH |
#3
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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" wrote: 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" wrote: 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! -- Bonnie W. Anderson Cincinnati, OH |
#4
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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" wrote: 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" wrote: 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" wrote: 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! -- Bonnie W. Anderson Cincinnati, OH |
#5
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! -- Bonnie W. Anderson Cincinnati, OH |
#6
|
|||
|
|||
Doesn't recognize Crosstab gen Fields
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! -- Bonnie W. Anderson Cincinnati, OH |
Thread Tools | |
Display Modes | |
|
|