Query To Set Status Based Upon 2 Fields
Jan 25, 2007
I'm trying to create a report eventually, however, I need to get my query set up.
My report should look like the following when complete.
Timeframe (mth,qtr,year)
# of Total Projects Completed - regardless of status
# and % of Projects Completed within Requested Delivery Date
# and % of Projects Completed within Committed Delivery Date
# and % of Projects Completed within Requested and
Committed Delivery Date
I have 3 different fields to base my calculation from:
Actual Delivery Date
Requested Delivery Date
Committed Delivery Date
I have created 2 new fields that calculate the number of days:
[Actual Delivery Date] - [Requested Delivery Date]
[Actual Delivery Date] - [Committed Delivery Date]
I'm trying to set up a "Status" field to indicate whether the project was completed "Within Requested", "Within Committed", or "Within Requested and Committed", however, I'm stumped. I've tried an Iff statement to no resolve. I don't want 10 queries just to get me there either.
Please help! :confused:
Thanks!
View Replies
ADVERTISEMENT
May 29, 2015
I am working on MS Access program where i import data from Excel and i would like to generate a report in MS Access where the fields are empty. Like the MS Access generate a table of errors when we import data and shows information like field name and row number along with Type Conversion label. Can i generate similar table from an MS Access table where fields are null.
View 3 Replies
View Related
Mar 21, 2006
I am not sure if what I am about to ask is best done in a query, table or report, so I thought I would post this in the query section of the forum.
I have a table that contains the following fields (plus other fields)
RC_START_DATE
RC_DUE_DATE
RC_APPROVAL_DATE
RC_STATUS
On one of my reports, I want to show the "Days Open". If the RC_Status is "Active" I want to calculate the "Days Open" by subtracting RC_START_DATE from Today(). However if the RC_Status is "Closed" I want the "Days Open" to be calculated by RC_APPROVAL_DATE minus RC_START_DATE.
So the question is, what is the best way to do this and how would I write the formula to calculate this?
Thanks for the help.
Jim
View 6 Replies
View Related
Aug 2, 2014
In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:
If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if
This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true).
View 14 Replies
View Related
Jan 25, 2015
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
View 2 Replies
View Related
Mar 8, 2013
My database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.
If [table.status] = 'W'
then checkbox = 0
else
checkbox = -1
and then somewhere on the update it would be
if checkbox = -1 then [table.status] = R
Except that Access doesn't think the way I do.
View 1 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
May 31, 2006
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
View 1 Replies
View Related
Jun 2, 2014
using access 2010. I have two tables, an example of their structure is in the attached PDF, and in text below. I want to be able to join/ answer question between the tables like the one outlined below.
Reference Table: A table with the most likely pet based on income and location
Header: Country, State, Income Min, Income Max, Pet
Record: US , NY , 1 , 5 , Cat
Record: US , NY , 5 , 9 , Dog
Question Table: A table with income and location
Header: Country, State, Income
Record: US , NY , 5
What type of pet will the people in the question table have?
Answer: Header: Country, State, Income, Pet
Record: US , NY , 5 , Cat
The logic works like this:
Question Country = Reference Country
Question State = Reference State
Question Income >= Reference Income Min AND Question Income <=Reference Income Max
Obviously I am not working with cats and dogs but this is my table structure.
View 3 Replies
View Related
May 25, 2005
i am trying to make a cross tab query that will give me the status of the unavalble rooms in my hotel
somthing like that:
romm num today today+1 today+1 today+2 today+3 today+4 and so on...
1 X X
2 X X X
3 X X X
4 X X
5 X
thas how i will see where do i have a free rooms in a range of dates that i want to insert.
please advice,
Thanks
View 1 Replies
View Related
Aug 21, 2005
Hi all,
I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Regards,
Rod
View 2 Replies
View Related
Sep 7, 2007
Hi All
I want to create a query which is based on two fields.
One field is a date query i.e >=Date()-7 OR >=Date+7
The Other is a Combo box where the criteria is No
My problem is that access is currently running this as two individual queries.
Is it possible to make this query return data only when both fields are matching?
Or do I have to run this as a parameter query?
Thanks!!
View 2 Replies
View Related
Jul 10, 2013
I have a table containing the area, location and name of someone there is also a field contains a number from 0-10. I need to run a query that will count the number of times a number appears in this field and put this total in a field on its own.
this my table fields are as follows...
Area Location Name Number
And I would like the query to show....
Area Location Name 10's 9's 8's ect...
View 4 Replies
View Related
Sep 7, 2005
Hey guys, I am trying to creat a query from my Status table, but having NO luck getting it to work. Basicly the Query is going to create brand new columns with the Status entry from the row selected. I have been able to create a query with 1 status column, but I cann't seem to figure out how to code it where it creates a 2nd. Heres what I have
SELECT SiteStatus.Status AS MailStatus
FROM SiteStatus
WHERE (([SiteStatus].[Site Component]="mail"));
Some how I need the Where statement to go along with the new Mailstatus column and then create a new column with the same effect, have it select the status from the row what contains the "component" in the "component" col. Anyone get any of this cause its starting to confuse me even now. Using Access 2003
View 2 Replies
View Related
Feb 4, 2007
Hi there,
i am writing a query that calculates a date
ExpiryDate: DateAdd("yyyy",[NATATable].[NATAExpiryPeriod],[NATATable].[NATADate])
which works
The expiry period is always 2 or 4 and the natadate is a date i want it to add 2 or 4 years to the date the NATA was done... works no problems.
Now i want a query in another field as a status
ExpiryStatus: Expired or Valid
ExpiryStatus: IIf([ExpiryDate]>=Now(),"Expired", "Valid")
Which works
i am ultimatley aiming to end up with 3 Fields - Expiry date (working), Status (working) and Remark (not working)
Remark is based on this excel query
=IF(AL3<NOW(),(IF(AL3<(NOW()-365/4),"(3) Expired more than 3 months ago","(2) EXPIRED during last 3 months")),(IF(AL3<(NOW()+365/4),"(1) Expires within next 3 months","(4) Expires in more than 3 months time")))
which i need to change to an acess query.
Where AL3 is the expiry date.
Please help
Thanks
Kris
View 1 Replies
View Related
Dec 19, 2006
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.
I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.
However, if one of the fields is blank, the query doesn't return anything.
Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.
It's not an error-checking issue, either, as entering info into all of the fields isn't required.
For reference, the searchable fields are:
Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.
A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.
There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).
I hope I'm clear in what I'm trying to accomplish :o
Thanks in advance!
View 6 Replies
View Related
May 13, 2013
I am making a parameter query that looks up quality data by lot number. For some of the lot numbers certain fields of data may be null. How can I omit these fields in the query if they are null?
OR automatically omit them when exporting them data to excel?
View 1 Replies
View Related
Mar 26, 2013
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.
View 3 Replies
View Related
Sep 27, 2006
Hi folks,
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
Files_id int
Filetransdate date
Filestatus_id int
A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
Regards
Charles
View 4 Replies
View Related
Jun 8, 2015
I want to run a query that returns a "small" or "medium" or "large" etc on each respective row against another columns respective numeric value...
< 500 = "small"
500 up to 1000 = "medium"
1001 up to 10000 = "large"
10001 up to 100000 = "very large".
View 8 Replies
View Related
Feb 11, 2014
I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:
Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.
This is not currently working.
View 8 Replies
View Related
Jan 18, 2015
I have the query below that return a table like:
PLOTNR; period,Value, ID, Basal_area/ha, Basal_area/ha, perc_BA_sp
What I want to is to add another field that rank the perc_BA_sp by PLOTNR descending (thus highest perc_BA_sp values rank one etc.)
Code:
SELECT[Q:INV1-Basal_area_plot-spp].PLOTNR,
1 AS period,
[Q:INV1-Basal_area_plot-spp].Value,
[Q:INV1-Basal_area_plot-spp].ID,
[Q:INV1-Basal_area_plot-spp].[Basal_area/ha],
[Q:INV1-Basal_area_plot].[Basal_area/ha],
([Q:INV1-Basal_area_plot-spp]![Basal_area/ha]/[Q:INV1-Basal_area_plot]![Basal_area/ha])*100 AS perc_BA_spFROM[Q:INV1-Basal_area_plot-spp]
[code]....
View 1 Replies
View Related
Feb 12, 2007
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
View 6 Replies
View Related
Jun 19, 2015
I have a database where there are 3 tables. table "d_cases", table "d_user" and table "d_status"
The table "d_cases" has the users and statuses only with a number. Peter has ID 2, John ID 1. There is status ID 1 that is New, ID 2 Started and so on.
How can I run a query that will show me the names and status instead of the numbers?
I have attached the database (2003 format) ....
View 2 Replies
View Related
Nov 6, 2013
Is it possible to add a field (i.e., variable) to a query (or SQL programming) based on a form selection?For instance, if I use a form to allow an end-user to specify which fields they wish to include in the query (essentially creating a UI for the query builder), how would I go about creating the query/SQL or updating the query/SQL?
View 3 Replies
View Related
Nov 5, 2013
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
View 13 Replies
View Related
