Access: Query Design/Add Column

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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
 
Upvote 0
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 ...
 
Upvote 0
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)
 
Upvote 0
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];
 
Upvote 0
That worked - I added it to the SQL code. Now its shows in my query and I can add additional columns. Thanks, Joe!
 
Upvote 0
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"!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top