I guess this is a fairly basic question but bear with me.
As with all newbies 'how can I' type questions come thick and fast and this one involves using Criteria in a Query. I am comfortable with the concept of prompting for a variable within a query using the square brackets ie.[Enter Post Town]. This of course relies on the person entering a valid value or spelling it right.
So I thought, what if I make Post Town a Combo box (I am working with a small sub set of places). Which then brings me to my question.
Can I create a query (which runs a report) that when activated will prompt for variable (in my case Town) and display the drop down list ?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I Currently have a query that has various information on it. WHen i click to open the query, It states to "enter line number", where i can enter a line (e.g. line43). If entered EXACTLY as it is found in the table where i took the information from, the records will show. However if i type in 43, or L43 or line43/44, nothing will show up. How can i make it so that there are multiple ways to show records, when i enter different information? (There are about 8 different lines).
Thanks to anybody who takes the time to help me solve this problem.
I am just trying to write a query by a code of table i would like to pick up any record with id number betwee 1-99 and 200-299 i try typing them in manually but access wouldn't cope
any idea of how i can achieve it please Many Many Thanks Si
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 am trying to create a crosstab query, but can get it right.
Table1 = input Table __________________________________________________ ___________ name StartDate date Present A 1201 1201 P B 1202 1205 P C 1203 1206 P D 1204 1207 P E 1206 1209 P
Can get this result with Crosstab Query;
TRANSFORM NZ(First([Table1].[Present]),"a") AS FirstOfPresent SELECT Table1.name, Table1.StartDate FROM Table1 GROUP BY Table1.name, Table1.StartDate PIVOT Table1.date;
__________________________________________________ ____________ name StartDate 1201 1205 1206 1207 1209 A1201 P a a a a B 1202 a P a a a C 1203 a a P a a D 1204 a a a P a E 1206 a a a a P
But am looking to add NA for StartDate > Header Date(1201,1205,1206,1207,1209). See below. Does anyone have a solution or an alternate method
Looking to get this type of results __________________________________________________ ____________ nameStartDate12011205120612071209 A1201 Paaaa B1202 NAPaaa C1203 NAaPaa D1204 NAaaPa E1206 NANAaaP
Ive had a quick look at other threads to see if this question has been asked already, so hopefully Im not repeating a previous question!
I am completely new to Access but have been asked to perform multi-criteria queries across multiple tables. I realise it would be easier to just put all the information into one table, but if anyone could explain how to do the above I would be very grateful.
I have a table that I want to append to another table, but I do not want to duplicate records if the check number is already in the table I am appending to. I have tried <>[deposit].[check_number] or not like [deposit].[check_number] or not [deposit].[check_number] but I always get prompted to enter a check number... therefore the criteria statement not working. do not know what I am doing wrong.
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.
I'm a little new to Access but I'm trying to create an IIf Expression:
iif ( condition, value_if_true, value_if_false )
Mine is: Au_ppb: IIf([Element]="Au" And [Units]="ppb",[Result],Null)
I need to have three criteria's (each looking into a different column). I can get two criterias but not three:
[Element]="Au" And [Units]="ppb"
When I add in the third (And [Priority]="12") it doesn't work .
Also, I need to have about 6 columns with similar IIF statements in the one query in my query and if a row has Null for all then I don't want it visible however if I make the Criteria "Is not Null" on one column or on all it comes up with no results.
I'm trying to generate a list of employees who haven't had a review since Dec 2013.I'm using this expression in my query:
Last review: DLast("[ReviewDate]","[Tbl_OutcomeHistory]")
which brings up the last date the employee had a review.(I tried using DMax but it didn't seem to like dates?)How do I get it to just show those who haven't had a review since 1st Dec 2014. I thought I could use "<01/12/2014" in the criteria but it returns no values. I've tried to drop the date into the expression but that returns no values.
I used a DCount however this does not separate the employees. Currently using Count Of Visits: DCount("[Duration]","Test Query","[Duration]<20 AND [Employee ID] = [Employee ID]")
In an Access 2010 query is it possible to set a criteria on a field (Results), that If it is "Negative" then another field (Variants) has the text "None Detected". Or is it easier to do that in the Variants field?
I am able to successfully set one of the above as a criteria just fine.I am stalled on trying to combine two or more of the above in an iif statement. Here is what I 'think it should look like:
In other words, in a separate field, I would like to print a period name such as above.I have not been able to find any reference to connect these long criteria together in one query expression.
My workaround is to use a union query. That works, but it is a little clumsy due to some outer joins which require a separate query. If that is my only solution, I will run with it.
Basically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.
I also have a query for sales with a SellingBranch field on.
I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".
At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.
I am using a function to set criteria in a an query. It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria.
E.g., the sql criteria is set to : like fnCountry()
And the function fnCountry() is something like;
If SelectedCountry = "All" Then fnCountry = "*" else fnCountry = SelectedCountry end if
This works fine for a single selection (SelectedCountry = Africa) but doesn't work if I try to combine multiple selections into the criteria string.
E.g. SelectedCountry = Africa Or Italy
So the criteria would need to be Like "Africa" Or like "Italy"
How else can i build this criteria with multiple values?
I am creating a query to select and sort events whose payments are overdue, prioritizing those that are most overdue at the top. The catch is that the due dates for payment varies based on several conditions and criteria.
Here's my end goal of the sort order (it's not in SQL, I'm just describing it):
1st step- If [Program_Code]="BD" Or "GT" Or "SG" Or "SC" Or "PR", then the sort criteria is [Date_of_Event] < Date()
2nd step- If [Program_Code]="WE" Or "KD", then the sort criteria is [Date_of_Invoice] < Date()
3rd step- If [Program_Code]="ZM" And [Cost_Category]="Full Price" Or "Discount", then the sort criteria is [Invoice_Date]+30 Days is < Date()
4th step- If [Program_Code]="BD" Or "PR" And [Deposit_Paid] Is Null, then the sort criteria is [Invoice_Date] < Date()
I'm not sure how to go about this but I have a feeling it involves an iif statement in the SQL and/or breaking it into smaller queries that are then consolidated into a "master" query.
Is there a way to search for queries that use specific criteria? Let's say I have 60 queries in total, but only 35 of them use the "Province" field as criteria. The criteria is set to retrieve all records that are in Province AB, SK, ON.
Suddenly we need to also include Province MB to all of these 35 queries. Is there a way to identify these 35 queries (all the queries use criteria in the "Province" field). These are the queries that would need to be modified to include "MB" as part of the criteria.
I hope my explanation is clear. Thanks upfront for any suggestions!
I have a problem in setting the criteria of queries.
I have two inspection methods: ABC and XYZ. Every two years, ABC will be carried out, and all other years, XYZ will be performed. However, information to which factory XYZ is performed is required. If ABC is carried out, then the information can be simply "N.A".
But when I do the query, I therefore need to add an extra field which will show "N.A" if ABC is performed, or the factory name if XYZ is performed (factory name can be retrieved from another table).
Hi, I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 x x (check boxes) Y ACD 2 x x
Query 2. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 10/8/05 (query performs Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
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
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
I have two questions about entering Criteria in the query dialog box.
1. I know how to have the program ask the user what information they want to look at. For example if I am advertising coats and I have different colored coats like, red, blue., green. etc. I can enter [Enter the colored coat you want to view] How do I tell the user to hit Enter if they want to view all the coats.
2. Referring back to the coat example. If I used an ID to number each coat, I have a customer email me asking for information about five ID's that are not in consecutive order, how do I run a query in return a report with the information of just the ID's that I want to send to the customer.
