Queries :: Max Function In Query By Row Not Column
Apr 3, 2015
I have a table that holds the dates of various approvals for documents.
Approval DatesDocumentID
ApproverA_DateApproved
ApproverB_DateApproved
ApproverC_DateApproved
LastUpdated
In a query, I'd like there to be a column with the date of the most recent approval (max date) for each document. Using the following formula in the Expression Builder gives me the error below.
Code:
Name: Max( [Projects]![ApproverA_DateApproved],[Projects]![ApproverB_DateApproved],[Projects]![ApproverC_DateApproved])
"The expression you entered has a function containing the wrong number of arguments"
How can I get the most recent date a document was approved by any approver?
BTW - I initially thought I could just use the date the record was last updated. However, sometimes people remove their approval. This causes the LastUpdated date to be wrong (for this purpose), since it then reflects the date they removed their approval, not the most recent date that someone gave it.
View Replies
ADVERTISEMENT
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Mar 24, 2014
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
View 1 Replies
View Related
May 21, 2015
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
View 3 Replies
View Related
Mar 15, 2014
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
View 2 Replies
View Related
Jun 6, 2013
I would like to be able to take 1 column from 2 different queries and put them into 2 columns in a 3rd query.
View 6 Replies
View Related
Apr 2, 2013
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
View 3 Replies
View Related
Oct 11, 2013
I have a query driving a report that fills in the on time or late based on comparing the release date to the production due date. The code works except for if we haven't released it yet and the release date is void. I need it to then check versus today's date and then populate the late/on time. But I need this to take a back seat to if both fields are populated. Here is the code I am currently using and I have attached a screen shot of the query with the 2 boxes highlighted in red.
On Time / Late Eng: IIf([Released to Prod]<=[Prod Due Date],"On Time",IIf([Released to Prod]>[Prod Due Date],"Late",""))
View 7 Replies
View Related
Apr 20, 2014
I have create few option buttons in a form and assigned values. when each option button selected it will store a value in tbl.attend.log
Exp - Opt 1 button = 1 = OnDuty
Opt 2 Button = 2 = DutyTravel
Opt 3 Button = 3 = SeekLeave
[code]....
View 2 Replies
View Related
Jun 16, 2013
What I am wanting to do is to have columns at the end of my imported data that reference another table to tell me based on the queue what service type and centre it relates to.
I have attached 2 pictures one with table 1 and the other with table 2. Table one shows the last column 'SERVICE_TYPE' which I need to lookup from the reference table (aka table 2 - which has been attached).
View 3 Replies
View Related
Nov 3, 2014
I have a member table linked to another table via id, one to many. The many table has a year field and a code. I need a query whose column heads are name, followed by each year represented in the second table. As:
Name 2014 2013 2012 2011
Jane Doe A B A E
Jan Little A B A
Bob Jones E A
etc.
View 7 Replies
View Related
Nov 14, 2013
I have a combo box which queries a table and then stores said tables primary key field in a field for my main table. The table queried has two fields; one for the primary key and the other for the string, such as "chris".
My problem is when I run new query from the main table it shows the bound column, which is the primary key, instead of the string column which would display my name. I can't figure out if there is code that I should deposit in the criteria field that would tell the query to display the name field (ala "Chris") instead of the bound column field (ala primary key).
Do I need to add something to the criteria field or run an expression that says display column 1 instead of column 0 in your quey?
View 5 Replies
View Related
Aug 22, 2013
emp_idtask_idstartdateenddate
1217/7/201320/7/2013
1321/7/201328/7/2013
2317/7/201320/7/2013
this is part of my table. It mean that empid-1 will work task 2 from 17/7/2013until 20/7/2013.Now i face problem in query that I have criteria that emp_id is 1 and date is 23/7/2013, I want to know which task_id assigned to emp 1 on 23/7/2013. The result should return 3 as the task_id
View 1 Replies
View Related
Aug 1, 2014
How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.
Code:
SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate
[Code] ....
View 8 Replies
View Related
Jul 5, 2013
I am currently designing a dashboard for my team which works on projects, I have a main table which has main fields which i use for my dashboard
Project Type,Project ID (Primary Key), Submitted by, Project Start date, Project end date, Status
The tricky part is that the raw data has projects which got started couple of years back some got closed in the previous fiscal year but some are still being worked upon..I was able to put in a where clause to only display projects whose close dates either have null value ( projects which are still being worked) and where the close dates are >= 1/1/2013. My Dashboard needs to have the below fields
Project Type, Count of Projects, Count of Closed Projects, Average days to complete, Count of Active Projects & Average Active days
I was able to get it correct except the last field Average Active days as it is displaying Average no's even against the project type which doesn't have any active projects or where the active project count is zero.
SELECT TblWO.[Operational* Categorization Tier 2], Count([TblWO]![Operational* Categorization Tier 2]) AS [Count], Avg([TblWO]![SLA in Days]) AS [Internal SLO in Days], Count([TblWO]![Closedate]) AS [No Closed Projects], Round(Avg([TblWO]![Closedate]-[TblWO]![Work Order Submit Date Time]),2) AS [Avg Close Days], [Count]-[No Closed Projects] AS [No Active Projects], Round(Avg(Date()-[TblWO]![Work Order Submit Date Time]),2) AS [Active Avg Days]
FROM TblWO
WHERE ((([TblWO]![Closedate]) Is Null Or ([TblWO]![Closedate])>=#10/1/2012#) AND (([TblWO]![Status])<>"Cancelled" And ([TblWO]![Status])<>"Rejected"))
GROUP BY TblWO.[Operational* Categorization Tier 2]
HAVING (((TblWO.[Operational* Categorization Tier 2])<>" "));
View 3 Replies
View Related
Apr 8, 2014
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies
View Related
Apr 26, 2013
I'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :
prod1 = 4
prod2 = 3
prod3 = 3
How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)
View 6 Replies
View Related
Mar 25, 2015
I have a list of product sales for various regions.How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
View 3 Replies
View Related
Sep 28, 2013
I have an option group that lists a variety of categories which I want to query on.
I know that the option group only returns values.
In the Option Group, On Time = 1, Over Due =2 ,,,,
I am using a Choose function in my query. That is working fine. But ... I would like to have a last choice called "All" such that when selected all data is shown (i.e., On Time, Over Due, ....).
I am using the following:
Choose ([optiongrpStatus],"On Time", "Over Due", .... ,???) as my query criteria.
Have not figured what I can put in the last choice to get all data.
Tried "Is Not Null", tried "On Time or Over Due" - know these don't make sense -just wanted to try.
View 7 Replies
View Related
Oct 10, 2014
I just wondered is it possible to add my dateadd calculation to my findlast calculation using dmax in one column in my query
here's what I hoping to put in one column as at the moment it takes 2
findlast: DMax("[ServiceDate]","[PlantServiceTbl]","([ImpPlantItemID]= " & [PlantItemID] & ")")
this is what I want to add without a separate column
DateAdd("m",[Months],[findlast])
View 2 Replies
View Related
Apr 12, 2013
I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:
TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS
SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE
FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID
WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8))
GROUP BY IUIDCODES.CODEDESCRIPTION
PIVOT CASESTATUSCODES.CODEDESCRIPTION;
View 2 Replies
View Related
Jun 2, 2013
1 I have a delivery method column and I want to only show 1 type for each method. How would I do that? Is it in expression builder?
View 1 Replies
View Related
Aug 29, 2013
SUMMARY:
In Access 2007, can I pass a Column Name as a parameter from a Combo Box in a form?
DETAILS:
I would like to use a String value from a Combo Box as a parameter in a Inner Join query:
...
WHERE (((AAA.HSC) Like Forms!My_Form!My_TextBox)
AND((CCC.Forms!My_Form!My_ComboBox)="X"))
Where "CCC is a Table from my Join Query
I'm able to run the code above without generating any errors. However, instead of the query accepting the value from the Combo Box, it opens a pop up asking for the value of "CCC.Forms!My_Form!My_ComboBox", not once, but twice! After it finally runs, the result set is empty when it should not be. I also find it strange that it is asking for the combo box value with the Table name appended to the front.
View 4 Replies
View Related
Jun 9, 2013
I am trying to group the following Query to find duplicates with no success.
Base Query:
SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));
Find Duplicates Query:
SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];
View 3 Replies
View Related
Dec 20, 2013
I have employee attendance in Excel i.e Emplpyee Number, Day1, Day2, Day3....Day31
I have a table in access that have columns Employee Number, Day1, Day2, Day3...Day31, Presents, Annual Leaves etc
Excel file data needs to be transferred in Access table but the number of Presents, Annual Leaves etc needs to be calculated in Access during transfer not in Excel.
Now due to limited knowledge I do not understand how to resolve this issue and how to start. How to calculate the number of Present and Leaves.
View 3 Replies
View Related
Apr 14, 2014
I'm trying to retrieve data based on the contents of one column.
Sample table
Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 S2378797
gfd 1002 S1164478
gfdsffsdf 1003 R1165778
ssdad 1004 M0007867
gfd 1005 N7765111
gfdsffsdf 1006 W5464111
gfd 1005 N7765111
gfdsffsdf 1006 A4000011
gfdsffsdf 1006 W5464111
ssdad 1001 2378797
gfd 1002 1164478
ssdad 1001 965000
gfd 1002 780000
yjgk 4456 540000
kjhkh 2009 150000
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not
IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Code:
Description EID Basecode
----------- ---- ---------
ssdad 1001 2378797
gfd 1002 1164478
ddd 1004 1040
d88jg 1004 14C676
fsa 6565 158
fdh 1004 2Khlm
ggdg 2009 967
Third query not working
Code:
SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1
WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR
(((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
View 5 Replies
View Related
