Access: Query Design/Add Column

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,682
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,682
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,682
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,682
That worked - I added it to the SQL code. Now its shows in my query and I can add additional columns. Thanks, Joe!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,416
Office Version
365
Platform
Windows
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"!
 

Forum statistics

Threads
1,081,488
Messages
5,359,013
Members
400,515
Latest member
smigaud

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top