Using Table Value For Query Criteria And Table Name?
Nov 25, 2011
How do I use a value from a table as criteria in a query? I created a MakeTable query and converted it to SQL. Then I copied the code multiple times to create the multiple tables - see below. I have a table with the vendor names. How can I get the vendor names from the vendor table so I can put this into a loop for all vendors in the table?
Im really stumped.. and I really need some help, Here is my problem,
I have a field with a whole bunch of regular peoples names in it (user_names). Home Developer names are also in this field, it has to be this way to show if the home is still owned by the developer or moved into. What im trying to do is build a query that takes out all of the developer names (by takes out i mean does not change the table at all, just temp so i can perform some calculations on the data).. Im having a problem with this. I have a developer_name table that looks like this
Autonumber Developer 1 Walts 2 Peets 3 Donalds
When i set up my query under field i put user_names and under criteria i have used all variations i can think of pertaining the following code
<>[DeveloperInfo]![DevName]
from what I understand it should return all values that are not developer homes, it doesnt work though, it leaves some in there. if i change the code from <> to = it takes out all the user names and leaves just the Developer names as it should, unfortunatly i need it to take out the developer names not leave them in.... Any ideas?
I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
if there's a way to apply a list of items as criteria in the order listed? Say I've got a list of sales reps and I want a macro to run a query repeatedly, populating one rep from the list at a time in a field called Sales Rep.
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relavant 'ticks' across............ and how the hell would i do that......
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relevant 'ticks' across............ and how the hell would i do that......
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms): If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is: WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden") AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE, PRODUCTS.[STANDARD TARRIFF NUMBER], [ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL, [ORDER DETAILS].QUANTITY, ORDERS.ORDDELIVERYCOUNTRY, ORDERS.ORDERID, [ORDER DETAILS].PRODUCTID FROM CUSTOMERS RIGHT JOIN (PRODUCTS RIGHT JOIN (ORDERS LEFT JOIN [ORDER DETAILS] ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID) ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID) ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"), (([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"))) AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2)) ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause? Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
I am trying set up some criteria for a create table query. I am using multiple fields from different tables, I am trying to delete certain feilds if they meet the criteria. I am using iif([letter c_last]=[letter a_last],null,[letter c_last]) in the criteria for letter c last, but when I run it a box appears to enter criteria for the last name field? I want it to look at the field not for data to be entered manually. I hope this makes sense. Any help would be greatly appreciated.
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number DNIS - Text (This is the column i need filtered) PropertyName - Text ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
I have a form that run a query to select all the students taught by a given faculty member. Once the records are loaded, the faculty enters attendance data, selects a date from a combo box and hits a save button.I change my relationship and now need to be able to store the key for the field in the combobox and not the text.
How do I set the field, table and criteria in the update query to do this. The UD query uses the FacultyStudents query as its record source. Currently the FacultyStudents querry does not have the Key or the date fields since the date is selected by the faculty once their records are loaded. As I see it there is now way to make a join, so I think I either need VBA or SQL, which I am not very good (really bad) at wriitng. Can the necessary Sql be written in the query design view?
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I have a table that has banking information in it (downloaded from the internet). I have a category field (lookup field) that I have to update manually so, for example, every time the electric bill is paid I have to click it and change the category to "electricity".
I want to set up a table with phrases for access to search for and a category to change to. For example if the banking table has "VIS ELECTRICITY 20812/773474868" and my search criteria table says anything with the word "electricity" should have category of "electricity", then I want Access to update the banking table based on that.
There will be several items in the search criteria table so Access will have to read through all of them to find the correct one. Is this possible?
I have a temporary table with Yes/No fields (F101,F102,F103,....etc). In another table i have a field called ProductCode with values (101,102,103,....etc) Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.
He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.
I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code: Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long Const conJetDate = "#dd/mm/yyyy#"
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org .gov .du .pk .dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS FROM tableA WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
In my database I have 2 tables that contains values I would like to add if a check box is marked.
Table 1 columns: JobID;JobNumber; Product; Qty.
Table 2 columns: COrderID, JobNumber, QtyChanged, Accepted(Accepted has the check box).Also, there are multiple records for each job number based on how many change orders will be.
I would like to change the value of Qty on Table1 with the values of QtyChanged from Table2 only if the checkbox is marked.
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?