Access: Query Design/Add Column

[gheyman]

I am in query design view and I am trying to add a new column, but access is not letting (not doing anything). I currently have about 26 columns in my query and i wanted to add two more. When I go to the Design ribbon and click Insert Column - nothing happens. I tried saving the query, getting out of access and then back in, compacting.... nothing helps. Did I reach a limit?

 

[Joe4]

No, you can have a lot more than 26 columns in a query, but it could be dependent on the TYPE of query it is.
What kind of query is it?
What kind of field are you trying to add?
Is it a calculated field?

Can you change your current query to SQL View and copy/paste the code here so we can see what it currently looks like?
Then let us know exactly what you are trying to add and exactly how you are trying to add it?

 

[gheyman]

Its just a Select query. If I could add a column it would be a calculated one just like the last one in this Sql'

SELECT DateDiff("m",[PDIR Due Date],Now()) AS Month_Due, [QUOTES MASTER TABLE].[Quote Number], [QUOTES MASTER TABLE].Procurement, IIf([PastDue]=1,"Past Due",IIf([CompletedLate]=1,"Completed Late",IIf([OnTime]=1,"On Time","Open"))) AS OTD_Status, [QUOTES MASTER TABLE].[Recommended Product], [QUOTES MASTER TABLE].Subject, Left([Line of Business],4) AS Line, [QUOTES MASTER TABLE].[Bid Status], [QUOTES MASTER TABLE].Category, [QUOTES MASTER TABLE].[Estimated Value Max], [QUOTES MASTER TABLE].[Cert Required], tbl_Material_Tracking.Number_Buy_Items, [QUOTES MASTER TABLE].[PDIR Submission Date], [QUOTES MASTER TABLE].[PDIR Due Date], [QUOTES MASTER TABLE].[PDIR Completed], IIf(([PDIR Completed] Is Not Null) And ([PDIR Submission Date] Is Not Null),DateDiff("d",[PDIR Submission Date],[PDIR Completed]),"") AS TAT, IIf([PDIR Completed] Is Null,DateDiff("d",[PDIR Due Date],Now()),"") AS Days_Past_Due, IIf([PDIR Completed] Is Null,IIf(DateDiff("d",Now(),[PDIR Due Date])<1,1),0) AS PastDue, IIf([PDIR Completed] Is Not Null,IIf([PDIR Completed]>[PDIR Due Date],1,0),0) AS CompletedLate, IIf(([PDIR Due Date] Is Not Null) And ([PDIR Submission Date] Is Not Null) And ([PDIR Completed] Is Not Null),IIf([PDIR Completed]>[PDIR Due Date],0,1),0) AS OnTime, IIf([Category]="f.<$150K",1,0) AS f, IIf([Category]="e.$150K-$750K",1,0) AS e, IIf([Category]="d.$750K-$5M",1,0) AS d, IIf([Category]="d.$750K-$5M",1,0) AS d, IIf([Category]="c.$5M-$20M",1,0) AS c, IIf([Category]="a.>$40M",1,0) AS a
FROM tbl_SO_Req, (([QUOTES MASTER TABLE] LEFT JOIN tbl_Material_Tracking ON [QUOTES MASTER TABLE].[Quote Number] = tbl_Material_Tracking.[Quote Number]) INNER JOIN tbl_ME ON [QUOTES MASTER TABLE].Procurement = tbl_ME.ID_Material_Estimator) INNER JOIN tbl_BidStatus ON [QUOTES MASTER TABLE].[Bid Status] = tbl_BidStatus.Bid_Status
WHERE (((DateDiff("m",[PDIR Due Date],Now())) Like [Forms]![frm_OTD_Metric]![Combo_Month]) AND (([QUOTES MASTER TABLE].Procurement) Like [Forms]![frm_OTD_Metric]![Combo_ME]) AND ((Left([Line of Business],4)) Like [Forms]![frm_OTD_Metric]![Combo_Line]))
ORDER BY DateDiff("m",[PDIR Due Date],Now()), [QUOTES MASTER TABLE].Procurement, [QUOTES MASTER TABLE].[PDIR Due Date];

Thanks for the help, Joe

 

[Joe4]

I don't know if this is what is causing the issue or not, but It looks like you have a repeated calculated, which would cause a duplicated/ambiguous field:

... IIf([Category]="d.$750K-$5M",1,0) AS d, IIf([Category]="d.$750K-$5M",1,0) AS d ...

 

[gheyman]

Thanks for catching that. But unfortionatley it still will not let me add a column. do you think if the SQL was modified, it can be added that way?
g: IIf([Category]="g.TBD",1,0)

 

[Joe4]

Sure, I would certainly try it that way. You will want to add it in the same fashion, though, i.e.

SELECT DateDiff("m",[PDIR Due Date],Now()) AS Month_Due, [QUOTES MASTER TABLE].[Quote Number], [QUOTES MASTER TABLE].Procurement, IIf([PastDue]=1,"Past Due",IIf([CompletedLate]=1,"Completed Late",IIf([OnTime]=1,"On Time","Open"))) AS OTD_Status, [QUOTES MASTER TABLE].[Recommended Product], [QUOTES MASTER TABLE].Subject, Left([Line of Business],4) AS Line, [QUOTES MASTER TABLE].[Bid Status], [QUOTES MASTER TABLE].Category, [QUOTES MASTER TABLE].[Estimated Value Max], [QUOTES MASTER TABLE].[Cert Required], tbl_Material_Tracking.Number_Buy_Items, [QUOTES MASTER TABLE].[PDIR Submission Date], [QUOTES MASTER TABLE].[PDIR Due Date], [QUOTES MASTER TABLE].[PDIR Completed], IIf(([PDIR Completed] Is Not Null) And ([PDIR Submission Date] Is Not Null),DateDiff("d",[PDIR Submission Date],[PDIR Completed]),"") AS TAT, IIf([PDIR Completed] Is Null,DateDiff("d",[PDIR Due Date],Now()),"") AS Days_Past_Due, IIf([PDIR Completed] Is Null,IIf(DateDiff("d",Now(),[PDIR Due Date])<1,1),0) AS PastDue, IIf([PDIR Completed] Is Not Null,IIf([PDIR Completed]>[PDIR Due Date],1,0),0) AS CompletedLate, IIf(([PDIR Due Date] Is Not Null) And ([PDIR Submission Date] Is Not Null) And ([PDIR Completed] Is Not Null),IIf([PDIR Completed]>[PDIR Due Date],0,1),0) AS OnTime, IIf([Category]="f.<$150K",1,0) AS f, IIf([Category]="e.$150K-$750K",1,0) AS e, IIf([Category]="d.$750K-$5M",1,0) AS d, IIf([Category]="c.$5M-$20M",1,0) AS c, IIf([Category]="a.>$40M",1,0) AS a, IIf([Category]="g.TBD",1,0) as g
FROM tbl_SO_Req, (([QUOTES MASTER TABLE] LEFT JOIN tbl_Material_Tracking ON [QUOTES MASTER TABLE].[Quote Number] = tbl_Material_Tracking.[Quote Number]) INNER JOIN tbl_ME ON [QUOTES MASTER TABLE].Procurement = tbl_ME.ID_Material_Estimator) INNER JOIN tbl_BidStatus ON [QUOTES MASTER TABLE].[Bid Status] = tbl_BidStatus.Bid_Status
WHERE (((DateDiff("m",[PDIR Due Date],Now())) Like [Forms]![frm_OTD_Metric]![Combo_Month]) AND (([QUOTES MASTER TABLE].Procurement) Like [Forms]![frm_OTD_Metric]![Combo_ME]) AND ((Left([Line of Business],4)) Like [Forms]![frm_OTD_Metric]![Combo_Line]))
ORDER BY DateDiff("m",[PDIR Due Date],Now()), [QUOTES MASTER TABLE].Procurement, [QUOTES MASTER TABLE].[PDIR Due Date];

 

[gheyman]

That worked - I added it to the SQL code. Now its shows in my query and I can add additional columns. Thanks, Joe!

 

[Joe4]

You are welcome.

When you are using the Query Builder, all that is really doing is building the SQL code. So you can bypass that, and just type the code directly, as long as you know the correct syntax.
Sometimes you need to, because there is some code that cannot be represented via the query builder (like Union queries).

So hopefully, you just learned a new "trick"!