Queries :: Query Using Criteria From Non-related Table?
May 15, 2015
I want a query selecting records from table1 based upon a value in table2 -
table1 contains thousands of records - fields include VendorNo and VendorName
table2 contains a single record - fields include VendorSearchName (which is a "short name")
I am trying to create a query which lists all VendorNo and VendorName entries where VendorName contains VendorSearchName
eg. VendorSearchName = "Fred" will list VendorNames including "Fred Flinstone", "Big Fred" etc
View Replies
ADVERTISEMENT
Aug 18, 2014
I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
View 9 Replies
View Related
Aug 25, 2014
Code:
DCount("*","[tblLeaveRequests]","Status='Approved' And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")
The above code counts all leave with the status of approved on the table [tblLeaveRequests]. Great, but i did forget a critera.
Of the leave it is counting, I want it to only count if the employee the leave is for is in a particular location. which exists on the related table, and not the one dcount is calling.
Is this possible? or will i have to resort to replicated data in [tblLeaveRequests]
View 3 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
Jul 23, 2015
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.
View 3 Replies
View Related
Feb 15, 2007
I have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data?
Thanks.
View 2 Replies
View Related
Dec 17, 2013
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?
View 3 Replies
View Related
May 13, 2013
I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).
Here is my query:
UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD
and it doesn't work since access ask me to enter a parameter value.
View 1 Replies
View Related
Nov 14, 2013
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
View 6 Replies
View Related
Apr 22, 2013
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?
View 3 Replies
View Related
Jul 10, 2015
How can I include a field from a table in to a query - that has no relationship?
For example...
The 'Despatch' field is in a seperate table called 'Product', because we despatch multiple products for any ONE order at different times, so it needs to be against the Product and not the full job itself.However, when I include the 'Product' table into the query and insert the 'Despatch' field into my query, and run it comes up with an error...
"The SQL Statement could not be executed because it contains ambiguouse outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then iclude that query in your SQL statement". Here's the current SQL statement
Code:
SELECT [Materials Requested].[Job No], [Materials/Orders].Total, Jobs.[Project Name], Product.Despatch
FROM Product, Orders INNER JOIN (((Customer RIGHT JOIN Jobs ON Customer.[Customer ID] = Jobs.[Customer ID]) INNER JOIN [Materials Requested] ON Jobs.[Job No] = [Materials Requested].[Job No]) INNER JOIN [Materials/Orders] ON [Materials Requested].[Materials Requested ID] = [Materials/Orders].[Materials Requested ID]) ON Orders.[Orders ID] = [Materials/Orders].[Orders ID];
View 1 Replies
View Related
May 29, 2005
Hi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, Caribbean£0.20
+20Egypt £0.30
+212Morocco £0.30
How would i set up a query to solve this problem?#Can someone please help me???
View 1 Replies
View Related
Aug 30, 2006
I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID
1 Null
2 1
3 2
4 Null
5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Thanks!
View 14 Replies
View Related
Aug 30, 2007
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things:
1. Displays the results of queries that sum data from a table (I think I have this figured) and
2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.)
Any help/advice that anyone could offer would be much appreciated!
Thanks!
View 5 Replies
View Related
Nov 8, 2014
I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
View 1 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Mar 13, 2013
I have a table with a list of different government programs that products can take advantage of. Each of these programs has criteria such as "must use less than 1000W" or "lasts for 100 hours". Rather than have a column for each possible condition, I've created 3 fields that will accept any type of condition; Var1Condition (example: watts) , Var1Requirement (example: >=), and Var1Value (example: 50). I figured this would be the more efficient database design than to add 15-20 columns.
I then built a form that would where I could enter product attributes and would hopefully query my database and only return the programs for which the product would qualify. So I would have a field named "Watts" in which I would put the wattage of the product and then I would see which programs it would fall in.
In theory, it should be simple. I figured I could just find a way to combine my 3 criteria fields into one string "Forms!Search!watts >= 50" and then use that as a query filter, but I can't find a way to do it.
View 2 Replies
View Related
Nov 25, 2014
How to get my query to run from criteria based on a combobox from another table. I have a field "prac" that has entries such as JF, MG, PM, RJ etc.
My query has the criteria for prac as [Forms]![PracReportPrint]![PracChoice].
PracChoice is the name of the combobox.
If I specify a valuelist for the combobox the query works. I created a different table just for a list of practitioners that contains only an index key and practitioners (JF, MG, PM, RJ, and so forth). When I set the combobox to table/query and select practitioners from my other table I get the correct list,b ut the query doesn't seem to detect what is selected from the combobox.
View 2 Replies
View Related
Sep 16, 2013
Is it possible to have a query that uses criteria to pull data from a specific table?
For example: IIf([Result]'"Negative",(tblNegative goes here),IIf([Result]="Positive",(tblPositive goes here].
The tables are just text, but the query would be too long if it was used.
View 2 Replies
View Related
Jun 3, 2013
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
View 8 Replies
View Related
Aug 12, 2015
How can i update fields of one table with update query where creteria are more than one 1
Example:
DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"
What is the syntax error ...
View 5 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)Value1(Double)Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 11 Replies
View Related
Aug 29, 2013
I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.
My table is in the form,
TimeandDate,WS127m_Avg,WS82m_Avg....
I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,
SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s
FROM CL_AllData
WHERE (((WS127m_Avg)=9999))
GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);
I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).
View 2 Replies
View Related
Apr 24, 2015
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.
IIf([Forms]![DatesSalesLeague]![Site]="BOLTON MA","BOLTON","MANCHESTER")
View 1 Replies
View Related
Aug 9, 2005
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.
Thanks,
CB
View 1 Replies
View Related
Sep 14, 2007
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.
Thanks in advance,
Emily
View 5 Replies
View Related
