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])
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
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 RelatedI 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...
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 RelatedI have the below query (A). How do I calculate the Day of Therapy as shown in B
A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
[code]....
What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
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"));
This qry pulls from multiple tables to create a job ticket for the technician/installer. I will not list all the tables because I have narrowed down my problem to two specific ones. These two tables:
tblBusinessCustomer
tblBillingInfo
Have a one to many relationship, due to a single customer could have multiple billing notes, this is how the tables are structured:
tblBusinessCustomer:
CustomerID (Primary Key)CustomerNameAccount StatusInstallationDate
tblBillingInfo:
BillingID (Primary Key)CustomerIDBillingNotesDateBillingNotes
I have a one to many relationship using the CustomerID field from tblBusinessCustomer to tblBillingInfo. When I run the report I have a field for billing notes. So what happens is that when I run the report (using the qryJobTicket as a filter) the report will have as many iterations as there are Notes. So for example:
CustomerID: 1
BillingNotesDate: 1/1/12
BillingNotes: (some form of gibberish)
BillingNotesDate: 1/2/12
billingNotes: (different gibberish)
so the report will run twice, one for each "line" of notes. I have tried using a form of the Dmax function as a filter in the qry so that I would only grab the most recent date from the BillingNotesDate field, thus only running a report and grabbing the most recent info.The problem is that I have inherited trying to clean up this database, and I added the BillingNotesDate field, since before they were running a constant "log" in the notes field:
1/1/12 Customer changed Name to ABC company, 1/2/12 Customer was notifited of late payment, 1/3/12....and on and on.SO there are NULL values in the BillingNotesDate field for all of the OLD data. And instead of trying to clean that up right away, I was trying to use the Dmax function to give me the most recent date, but the ones that had no date at all (NULL) it would not pull that record. I am trying to use the DMax function on a Date Field with Null values
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
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 RelatedI 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]....
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.
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).
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.
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?
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;
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 RelatedSUMMARY:
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.
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];
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.
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'));
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
View 1 Replies View RelatedI have inherited an Access 2003 database and have been asked to improve the functionality of said database. Specifically, I have been asked to create some charts for improved reporting. I used the wizard to create a crosstab query to support the chart(s):
Code:
PARAMETERS [Forms]![Switchboard]![ActualDate] DateTime;
TRANSFORM Sum(qryPMCompletion.CountOfWOs) AS SumOfCountOfWOs
SELECT qryPMCompletion.PA_Priority
FROM qryPMCompletion
GROUP BY qryPMCompletion.PA_Priority
PIVOT qryPMCompletion.wo_Completed
WITH OWNERACCESS OPTION;
Now what has happened is that the wo_Completed is a Yes/No field so my column headings and therefore my legend comes up as "-1" and "0" instead of "Closed" and "Open".
Is there anyway to change the name of the column headings or shall I just create my own legend?
how to have our Access 2003 query total our report column "ProfitLoss`and produce on our report a Total Value of -$420.17
View 13 Replies View Relatedtell me if it is possible to calculate a value in a column in a datasheet view of a query. i.e. I want to take a value in 'Column A' and subtract the value in 'Column B' to create a value in 'Column C'?
View 2 Replies View Related