ONLY NUMBERS ABOVE (2) (3) AND (6) DO NOT WORK, IF ANYONE COULD GIVE SOME LIGHT ON THIS I WOULD BE VERY GRATEFUL, AS I AM IN THE BLUE ON THIS.:rolleyes:
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.
the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.
cResort is populated with a textfield: PF, PV, and GS. cWeek is populated with an integer: 1-52 (the number of weeks in a year)
This is what the query is supposed to do: SELECT tblCustomer.* FROM tblCustomer WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10
here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.
I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.
Example: Show me cResorts: PF with corresponding cWeek:1-10. Do NOT show me cResorts: PF with corresponding cWeek:11-52.
I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.
I'll show you the SQL below first:
SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count] FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));
The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.
This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.
I am sure this is not as hard as I am making it but I can't figure it out at all.
I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form. Can anyone tell me where my problem lies? ( if this makes sense)
Thanks Kevin
=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))
I am trying to produce a report or query that lists 11 attributes for several projects measured each month over the duration of each project. i.e. It is required to have the attributes for project A listed (horizontally) for months 1,2,3,...etc (vertically) followed by those for projects B for months 1,2,3,4,5,6... etc. Can anyone advice on how to do this please?
I'm working on a purchasing app in access. At this point i'm working on the reporting module. I want a user to be able to fill out a start date (text box), end date (text box), and select a code from a list, hit Run Query, and have it pull up a report listing the date that the selected code was used, between the start and end date, and display other info as well.
The problem i'm having is that i can get the date ranges to work, or the code to work, but not both of them. Here's what i have in my query:
Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.
EX: SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down] FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end])) GROUP BY tblDownTime.dtDate, tblLine.lineName HAVING (tblLine.lineName)="[B]name of line");
criteria being name of line. Choices being line 1 or line 2
What I am looking for is one query to somehow generate the info on the two different lines in two different reports.
Is there a way or do I actually have to write the different reports?
I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.
I have a form that has seven unbound fields named txtCityCriteria1 through txtCityCriteria7
The idea is to have the use type in up to seven cities and have this applied to the criteria of the city field in a query. This query is the datasource for a report. I know how to do a single text ....
[Forms]![frm_OpenReport]![txtCityCriteria1]
in the criteria row would do the job. But how can I do seven?
Oh and if all are left blank i need it to open another form that informs them of their mistake.
I have looked through these forums and have found several possible solutions but I can not get any to work. Any assistance would be greatly appreciated. Take it easy on me I am relatively new to this code thing. Used to do everything in macros.
I'm sure this one has been asked time and time again!
I currently have a set of staff questionairres each on a different table. Currently when I want to export all the scores for one employee to excel (eventually) I have a macro which runs 7 queries, but each asks for the same criteria (Date To & From) and (Staff Name) in turn - very time consuming!
How do I create a query or macro which will ask once for a date range and an employee name and apply to all of my queries?
I am a novice on access and am trying to do this without SQL
Dim SearchStr2 As String SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _ And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code] DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal Forms!FRMASSESMENTHEAD.Filter = SearchStr Forms!FRMASSESMENTHEAD.FilterOn = True
If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.
It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying
Run-time error '13':
Type mismatch
hope this makes sense and can anyone help :confused:
I have a series of text boxes on my form. The user makes various selections in order to submit a query to the database. The default value for these boxes is *
When the user clicks on the submit button, I only want the code to run for the button as long as not all of the boxes still have a * in them.
What direction should I head off in when it comes to coding this. basically, as long as at least one of the 5 boxes does not contain a star then run blah blah blah code, else, display message (I know this bit).
I have a form on which I have about 6 combo-boxes and a set of 3 radio buttons.
I'm to the point that all the querys that fill these combo-boxes are in place.
What I want now is a search button that updates a datagrid under it when clicked. The query in this datagrid needs to be parameterised with the selected values in the comboboxes or radio buttons. It should be possible to only select one combobox before pressing update.
At this point I placed a subform in the form to bind this query to. ( the datagrid I need). Is a subform the correct object for this or are there other possibilities?
For some boxes only a line to the where clausule of the SQL statement needs to be added. For some other (one) and the radio buttons a join needs to be made with another table. So I will have to build my sql statement dynamicaly in some vba code, run it when the search button is clicked and refresh the datagrid.
Does ab has a sample database in which such a search form is being used?
I must have downloaded like 10 sample DB's now but nothing I can use...
This is probably an easy one but for some reason I'm not finding it in any of my reference sources.
I'm trying to set up a query that calculates tax my company owes the gov't, and the tax varies based on year to date totals. The 1st $30,000 is taxed at 4% and everything over $30,000 is taxed at 5%.
tblOrders has fields for date, CustomerID, OrderID and OrderTotal. Can the query have an expression field ("UpTo30") for ([OrderTotal] <=$30000) and also a 2nd expression ("Over30") for ([OrderTotal]>$30000)? And in the same query is it possible to also include the calculated fields for ([Upto30]*0.04) and ([Over30]*0.05)?
My main question is where to put the criteria - all I'm seeing talks about multiple criteria being State=NH AND/OR Name=R*, not multiple criteria on the same field. When I try WHERE statements I just get error msg no matter what the syntax is.
I have multiple queries pulling data from multiple data tables but are all common by a date that I have to enter in the criteria field for each query each time I need to change the date. Is there a way for the criteria field to read data (the date I want) from somewhere so I only have to change the date in one place and is read into each query where specified? I'm having to go into many queries to change the date criteria which I change to the same date for all queries - I'd like to do it once and have the queries read that date.
Hi! I have two related tables, one containing human resource information, the other containing a weekly record of hours worked by employees.
Here's the dilemma: I need to keep a running total of hours available based on hours worked.
Employees begin each 7 day work week with 70 hours of time available. Any hours worked in that week are subtracted from the available hours for that employee. If there are two days in a row not worked during any given week, the 70 hours needs to automatically reset.
These hours are tracked in the Hours Available field (a calculated control which needs the Dsum expression) which should examine the Hours Worked field. Presently, the Hours Worked control gives a total of all hours worked for the employee shown on screen, but I need to examine the current work week to see whether or not an employee has worked more than 70 hours (it's a legal requirement.) If during any 7 day period counting backwards from the present date the employee had two consecutive days off, the employee becomes eligible to work 70 hours and the countdown starts again. In short, the total hours worked should be subtracted from 70 until 0 is achieved in any given week, unless the employee has had two days off (Hours Worked sum for two consecutive days =0.) I have attached a paired down version of the database for review.
I've had a look around this forum and haven't seen anything really matching my needs, but please feel free to point me in the right direction.
What I am trying to do (using macros & queries - very new to SQL / VBA) is filter by multiple fields, but also allow the user to choose to filter by different combinations of fields - such as searching for first name and suburb, or salesperson and customer source, or first name and salesperson, or suburb and customer source, or three options or one, etc etc.
There will only be a certain number of search fields, probably five or six, but how do I get the query to recognise that if one of the search boxes is blank, it should allow all records in that field and only search by the other criteria, but if all the search boxes are filled in, the results must match all the criteria?
Does this make any sense?
PS - I am planning to use a command button as an "Apply Filter" so that the filter only updates after the user has specified all the options they want.
I am using MS Access 2010 and have an issue with one of my forms. First off, I have a table called coefficient which contains a column MIN, a column MAX, and a column COEFF.In my form, called CALC, I want to grab a number that it is in textbox [Text12] and place the related coefficient number in textbox [Text15].I placed a control source in [Text15] using =DLookup("[COEFF]","coefficient","[Text12]>[MIN] And [Text12]<[MAX]").
My database has a street address field that I would like find and replace a handful of certain strings with other strings. For example I would like to see:
Sunset Point = Sunset Pt Main = Main Prospect = Prospect Caminito Paseo = Camto Paseo Camino Litoral = Cam Litoral
I would like to perform something exactly like Find and Replace but to multiple strings in ONE function or query to update.
Is this possible? I looked at fReplace() but not sure where to put this.
I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly. Thanks, a_brooks
I keep thinking this should be pretty simple, but more than 2 hours searching through posts has revealed no answers, probably because the answer is too simple.
OK, I have an append query. I've created a prompt to enter the number of the record the user wishes to append, as it is usually one single record. However, I've recently run into a problem that now there is the possibility of needing to specify more than one record at the same time to be appended.
How do I change the criteria syntax to allow the user to enter two, three, or a dozen (if need be) different record numbers? They are not listed sequentially, so I can't use a "between" statement. I've tried separating the numbers by commas, quotes, and so forth, but can't get it to recognise the records.
