Queries :: Including (In Between) Information In Query?
Sep 3, 2013
I have a table of events that includes fields for ages, a Start Age and a End Age.
I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.
My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012 April 2012 May 2012 June 2012 July 2012 August 2012 September 2012 October 2012 November 2012 December 2012 January 2013 February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
I populate the table "date" field from a Microsoft Date and Time Picker Control 6.0(SP6). I havent been able to figure out how to ONLY populate the date portion of the control so my field value containes Date & Time.
When I run the query, I have to select the day after my request to include the records I need. I think this is because of the time in the field Example, If I want all records thru 6/17/2006 I must select "thru" 6/18/2006. This mucks things up if I have records dated 6/18/2006
Both of these statements give the same bad results:
Between [forms]![freports]![from] And [forms]![freports]![thru]
>=[forms]![freports]![from] And <=[forms]![freports]![thru]
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I want to list all the records which contains data as per my filter form field company.
I have tried this
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")
problem is this i want to display all the records including null, but this code only show all records except null
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")
I have an Acess database (2013). This will be used by several people within one company, they don't have their own server. I would use SharePoint Online, but it seems to me, that you only can "export" the tables. The database I have is quite a complicated system, and it is neccessary to use all the Access tools - forms, reports, queries. Not tables only.
Another idea is to split the database and "store" the backend part (the tables) on Sharepoint (Office 365 online version). And all the users would have their own frontend (forms, queries...) on their own PC, linked to this backend. Would this work? Can everyone add some entries into the database this way? How does it "synchronize" then?
User Query #1: Notes: ReportStartDate will always be entered as the first day of the month; ReportEndDate will always be entered as the last day of the month The report will always show 6 consecutive months
User selects ReportStartDate = 7/1/12; ReportEndDate = 12/31/12
Notable conditions: -The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0. -The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value. -The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
I need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.
I am trying to create a query where a user will enter information in boxes (any combination) on a form and a query will bring up the joined information from 4 different tables but I do not know where to start with the relationships on the query let alone the best method to search.
To start it off I have attached a db with the tables and the form I want the user to enter the search criteria into as well as my attempt at a query.
I currently have a query set up to pull information from 4 linked tables: Event, Readers, Class and School. The Event table contains a ClassID and a ReaderID. If everything has been assigned properly, there's no problem. If there's no reader assigned to ReaderID I still want the event details to be included in the output. Right now it's not. I'm pretty new to Access so if anyone can help, that'd be great. If you need to be try to explain further, let me know.
I am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.
Part of a database I'm developing stores details of events. Each event is funded from a variety of different sources.
There are three tables involved in making this work: tblEvents - which stores the main event details tblFundingOptions - which stores all the possible funding sources tblEventFunding - which links the two together.
I have created a form to access all of this, with the information from tblEvents at the top and the Funding details on a subform.
I have created a query based on tblEventFunding which adds up the total amount of funding allocated to each event. I want to display this total on the form I've developed.
Can I do this without creating a second subform to pull the details in from the Query? I've tried creating a text box and linking it to the appropriate query, but it just displays #Name! - presumably because it doesn't know which record from the Query it should display, and I don't know how to tell it!!
Is what I'm trying to do possible, or do I need another subform?
I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?
In the scheme below, I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2. There could be more than 1 Tier using the AppID. I don't know if I'm clear though.
Code:
Table1 Table2 Table3
AppID (PK) _ AutoNb (PK) AutoNB Name Tier (FK) ---_ Name - AppID (FK) \_ Tier (PK)
Here's my failing attempt...
Code:
SELECT Table3.* FROM Table3 WHERE Table3.Tier = (SELECT AppID FROM Table1 WHERE Table1.AppID = 2002);
I am trying to split a field that typically contains a revision level and a revision date in a single entry as follows:
(Rev Level) {Space} (Date: MM/DD/YY) REL 10-30-14 A 11-20-14 C 11/13/14
Note: dates use either - or / as separators. 99% of the time, there is a space between revision level and date.
Is there a simple way to write an update query to take the value in the current field, strip away all letters and spaces (but not - or /), and insert the remaining value into the revision_date field?
I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild.
I am trying to connect individual responses to a survey to a series of variables describing each environment where the survey was conducted. I have about 1,400 individual survey responses (of about 34 questions each that have been separated into columns) and about 42 environments that are described with 30 different criteria each. They are matched by the environment description
- A line for each respondent and their survey answers - On that same line would be the criteria of each environment.
Thus, the environment criteria should be repeated several times..However, when I join the fields in a query and require both fields to be equal, When I prioritize the environment panel, I only receive 42 records and the survey responses are blank. Prioritizing the survey panel gives 1,400 rows, but omits the environment info.
how do I populate the environment information variables on each row of the survey responses, even if that information duplicates the same variables on other rows?
So there's a memo field in my database that has a copies of an entire email message. I was wondering if there was a way for me to search for, extract, and place specific information from the text in the email into a new field automatically for each of them.
Retrieval of data from a query and putting it into a textbox on a form. I currently have a form where I would like to put in a Job Number, and then hit an "Update" button and it will show some of the data fields associated with that Job Number. I have a table called "All Jobs" and each Job Number is unique. Where I'm really having issues with is in regards to Dates and Boolean fields.
In one form, I put in a job number and it will bring up the Company, Motor type, Received Date, and Quote. I have been able to retrieve the Company, Motor, and Quote with a DLookUp expression using a query. The expression looks like:
CompanyName.Value = DLookUp("Company","Approved Job Form - Find Job Info").
CompanyName would be the name of the textbox on the form, with "Company" the field within the "Approved Job Form - Find Job Info" query. The query itself uses the job number entered onto the form and returns the other data fields associated with that job number. Running the query by itself returns the correct information. My issue comes with using the Date Received field. It seems that because that field type is a Short Date, my DLookUp expression keeps prompting the debugger in the code. Currently I have:
RcvDate.Value = DLookUp("Receive Date","Approved Job Form - Find Job Info")
This will not bring the date from the query into my text box like the others.
In another form, I'm also having issues with a Boolean data field. I would like to have a query that looks up a job number, and if my data field (Job On Hold) is checked (True), I would like it to return a value of "Job On Hold / In Progress" into a text box. If it is unchecked (False), I would like it to return "Job Approved". I've tried a bunch of different ways to make it work, but I'm really stumped. The makeshift code I last tried (I've tried this numerous ways, so I'm not sure if I was closer before) is:
Dim jobstatus As Boolean jobstatus = DLookup("Job on Hold", "Job Status Form - Search Status") If jobstatus = False Then Status.Value = "Job Approved" Else Status.Value = "Job On Hold / In Progress" End If
I have a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with
record 1 example ) REP/1349/999/426066/XX/9 record 2 example ) REP/UDKBS01N/1/448174/XX/
what i need to extract is
1) = 1349 2) = UDKBS01N
I need to get the information between the first / and the second / is there a function in access to get this.
