Not Exists Query Not Working

Jun 5, 2007

Hi, i have a simple query but for some reason it's not working.

Let me get to the point.
2 tables - table1(id,txtfield1) table2(userid, idfield)
idfield contains id so foreign key (although hasnt been set up as that yet)

Aim: select all fields from table1 where table2 doesn't contain them.

e.g if table 1 had, 5 rows containing:
1 - test
2 - test2
3 - test3
4 - test4
5 - test6

and table 2 had:
1 - 4
2 - 5

Then when the query ran, it would only pick up 1 - test, 2 - test2, 3 - test3.

Any ideas. I have tried but with no luck:

SELECT [ID], [txtfield1] FROM table1 WHERE NOT EXISTS (SELECT [idfield] FROM table2)

View Replies


ADVERTISEMENT

NOT EXISTS Aint Working

Feb 12, 2007

I have 2 tables
TKYOKUMD01 and TTOKUKMD01.
So Im supposed to get the records that aint found by this query in the TKYOKUMD01 table:
SELECT a.* FROM TKYOKUMD01 a, TTOKUKMD01 b WHERE a.KYOKUSYOCD & a.BUNSITUCD = b.KYOKUSYOCD & b.BUNSITUCD

Easy right? All I have to do is use NOT EXISTS Right? So I did, hence:
SELECT * FROM TKYOKUMD01
WHERE NOT EXISTS (SELECT a.* FROM TKYOKUMD01 a, TTOKUKMD01 b WHERE a.KYOKUSYOCD & a.BUNSITUCD = b.KYOKUSYOCD & b.BUNSITUCD)

No records returned, so maybe all is good and happy in their relationship interconnected world. So just to check I inputted 99999 for KYOKUSYOCD and 9 FOR BUNSITUCD in the TKYOKUMD01, now clearly it should appear right? Coz its there, on the blessed SQL code. BUt the I ran it, BAM, no results? Whaja...juhh...

Please please please help me with this, is there something wrong with this statement coz Im pretty sure I was right :(

Oh and I have 24,000 records in the TKYOKUMD01 table and 18,000 records on the TTOKUKMD01 table, weird eh? Every one in the 24K table is in the 18K table? Like, what I said, Whaja...juhhh....

Please help :D

View 7 Replies View Related

Query: Put Value In Column If Rrecord Exists

May 12, 2005

I have a multi table query that I am looking to retrieve a single record from

However one of these tables has multiple records for each previous record so when I run my query, I am recieving results like this

value1 value2 value3 value4
A A A 1
A A A 2
A A A 3
A A A 4
B B B 1
B B B 2
B B B 4

I have a finite number of values that value4 can equal so I would like to have my results come up like this.

value1 value2 value3 value4(1) value4(2) value4(3) value4(4)
A A A True TRUE TRUE TRUE
B B B True FALSE TRUE TRUE

View 1 Replies View Related

Check Query To See If Record Exists

Dec 4, 2006

Hello all,
I have a form that looks at my "master table" where users put in general info about a sheet of material.
There are two combo boxes on this form, Batch# and Lot#. They will use the same Batch# and Lot# for many sheets. Every time a new batch and lot are entered, this will bring up a pop-up form where the user needs to take a measurement of that batch and lot for QA. This will go into a different table, where the batch and lot #'s are unique(many of the same batch#'s with different Lot#'s).
After the lot# is entered, I want to run a query in VB that looks for those unique identifiers, and if it doesn't find a record, my pop-up form will run.
This is probably something very easy....... It was a very long weekend and I'm tired of thinking.

Thanks
Scott

View 2 Replies View Related

Queries :: How To Pull All Records In One Query If A Value Exists

Mar 18, 2014

I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.

Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.

Name EE_ID JOB CAT EFF_DATE

John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012

View 3 Replies View Related

General :: Pull All Records In One Query If A Value Exists?

Mar 18, 2014

I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.

Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.

Name EE_ID JOB CAT EFF_DATE

John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012

View 3 Replies View Related

Subquery EXISTS NOT EXISTS ...

Dec 23, 2004

Hi guys...got a problem here....

I got a query which joins two table....

Individual
fid1
fQSname
fQSRegno

Point
fid2
fQSRegno
fQSPoint

Tables join using fQSRegno.
Scenario is
Tbl Individual contains all the members info.Tbl Point contains point given to members who attend courses. Problem is when I created a query...

Query1
fid3
fQSname
fQSRegno *from tblPoint
fQSPoint

the data shows only members who got points. Is it possible to include member who don't have point like new registred member to show in this query....

TQ

View 1 Replies View Related

Updating Query Flag If Child Records Exists

Jul 12, 2006

I have a stock-type record and a stock-item record.
I want to update a flag on the stock-type record called instock to be true if there are related stock-item records and false if there are no related records.
Does anyone know how to run a updating query that will do this?
I am fairly new to this so I'm sorry if this is a bit of a basic question.
Thank you for your help in advance.

John

View 1 Replies View Related

Modules & VBA :: Using Query As Recordset To Check If Path Exists?

Sep 8, 2014

I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.

Code:

Sub TestIt2()
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("QryUpdateRevisionHistory")

[code]....

View 1 Replies View Related

SELECT Query To Not Include Record If Already Exists In Another Table

Aug 25, 2015

I have 2 tables:

Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145

[code]...

So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.

The desired out put should be:

Unit
R_Date
Sold_Rate

X
25-AUG-15
200

View 3 Replies View Related

Modules & VBA :: Query A Table To See If Record Exists With Particular Field Blank

Mar 14, 2014

I am trying to achieve the following - I want to query a table to see if a record exists with a particular field blank. If so, I would like to prompt the user for data.

In real world terms, when assigning an item to a user I would like to first make sure that the item is not already assigned to somebody else. I have 4 fields, UserName, Item, IssueFrom, IssueTo. So when an item is assigned to a user, the first 3 fields are populated and the IssueTo remains blank, until that item is assigned to somebody else.

At the minute I have nothing in place to prevent a user from assigning the same item to multiple users and having multiple records for the same item in the table.

View 7 Replies View Related

Query Not Working...

Jan 30, 2007

Hi,

I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:

SELECT [Casual Roster Information].ID, [Casual Jobs/Dates].LinkID, Sponsors.SponsorID

I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.

My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.

Anyone know how I can go about fixing this??
Thanks!!
Nick

View 3 Replies View Related

Delete Query Not Working

May 12, 2005

Hi all,

I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.

I have a table called [PMI] and a table called [Referrals]

The tables have a one to many relationship and are linked by the primary key field Patient_No.

I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.

Hope that makes sense, all help is greatly appreciated.

Thanks in advance.

View 4 Replies View Related

Update Query Not Working

Aug 2, 2005

Hi all,

I'm sure this is easy to do but for some reason i just cannot get it working. Hopefully someone here can tell me where i'm going wrong.

I have two tables in a database and the have a linking unique field. I want to update a column in one table from a column in another where the linking ID field matches.

Here's what i have at present.


Update Table_1 inner join Table_2 On Table_1.ID = Table_2.ID
Set Table_1.[CHI Number] = [Table_2]![CHI_No]


This is just returning the CHI Number column as blanks instead of what i expected would be the same as Chi_No.

Can anyone shed some light on this for me?

Thanks in advance

View 2 Replies View Related

Date Query Not Working

Aug 18, 2005

SELECT count(Workitem) AS ["Total Workitem"]
FROM qryReport
WHERE qryreport.date >(08/10/2005); 'MM/DD/YYYY

Secondy how do i supply dates dynamically.

View 4 Replies View Related

Very Simple Query Not Working....

Sep 15, 2005

we have a main table for tracking pickups....some of the fields are combo boxes with relationships to other tables....one such relationship is units (ml, gallon, ounce, etc)...in the main table everything works just fine....a user enters the location info, the amount and the corresponding units. thus, an example would be a user picks up something from building X, room 101, 10, Gallons.....the 10 refers to the gallons, the user picked up 10 gallons...

now for the query:
i'm creating a query for which i will base a report on...in query design if i just select the above info everything works fine....it comes out on the report as 5 Gallons....however, what i'd like to do is concatenate the two fields so that they take up less space on the report and are more visibly appealing...here's what i tried in query design:
Amt: [amount]&Space(1)&[units]
this does not work....in the units table that provides Gallons as a selection in the combo field, Gallons has a key value of 5.....thus, when i run the query using the above code i get 10 5 - where 10 is the amount the amount and 5 is the key from the combo box...

i've tried bringing in the units table but then i get a type mismatch....how can i concatenate the two fields and show the proper units? this has to be simple but i cannot get it to work... :mad:

View 4 Replies View Related

Unmatched Query Not Working

Sep 21, 2005

I have a a table that changes monthly, and each month I need to find the records from the old table that are not in the new table and visa versa. I made the entire row in the table the key.
and wrote two find unmatched queries.
However, the queries are not finding all the information that is no longer there or that has been added.
Can someone please take a look and see what I am doing wrong?
Any help appriciated!!
Thanks


the tables are imported from Excel

View 2 Replies View Related

This Query For A Rowsource Not Working

Sep 29, 2005

Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.

I have a form [Training Record] that has 2 combo boxes.

Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.

Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.

Here is the current rowsource query for box B - which is basically showing blank fields...

SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID

I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.

Any help would be appreciated no end - im totally stuck now :(

View 8 Replies View Related

If Statement In A Query Not Working?

Oct 9, 2005

hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.

-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]

------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"

View 4 Replies View Related

IIF Statemenst In Query Not Working??

Nov 30, 2005

Hi, i have the below code in a field in a query

PD1ACTFORVAL: IIf([ACTUALS_TO_PD]>=1,[PD1ACTVAL],IIF([FORECAST_1_INC_BASE]=TRUE,[FORECAST_1_BASE_VALUE]+[FORECAST_1_PROMO_VALUE]),[FORECAST_1_PROMO_QTY])

but a message says:"the expression you entered has the wrong number of arguments" .....Any ideas what im doing wrong?

(if forecast_1_inc_base is false id like just the last part of code to be done...ie FORECAST_1_PROMO_QTY)

Any help would be great, cheers

View 10 Replies View Related

Update Query Not Working

Jan 12, 2006

Hello all,

I have the below update query running

UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Null))
WHERE tbl_master.EXCLUDEREASON Is Null

but when I add this line:

UPDATE tbl_master SET tbl_master.COLLRAW =
Iif(tbl_master.PSTATE = "WV","WV",
Iif(tbl_master.PSTATE = "MA","MA",
Iif(tbl_master,MSPBANK in ("751","752","753","854","855"),"GS",
Null)))
WHERE tbl_master.EXCLUDEREASON Is Null

It give me an error message that says "Wrong number of arguments in Query Expression." Can anyone tell me what is wrong with the query above?

Thanks in advance,

Vassago

View 2 Replies View Related

Query Criteria Not Working

Feb 3, 2006

I have a query with several criteria

SELECT tblProjectDetails.projectid, SubType.SubType, tblProjectDetails.status, tblProjectDetails.product, tblProjectDetails.description, tblProjectDetails.type, tblProjectDetails.producttype, tblProjectDetails.confidence, tblProjectDetails.flatamount, IIf(IsNull([reduction]),(([currentprice]-[newprice])*[volume])/(13-[cutinmonthnmbr]),(([reduction]*[volume])/(13-[cutinmonthnmbr]))) AS Calc, ([flatamount]/(13-[cutinmonthnmbr])) AS Calc2, IIf(1>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Jan, IIf(2>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Feb, IIf(3>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Mar, IIf(4>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS April, IIf(5>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS May, IIf(6>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS June, IIf(7>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS July, IIf(8>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Aug, IIf(9>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Sept, IIf(10>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Oct, IIf(11>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS Nov, IIf(12>=[cutinmonthnmbr],IIf(IsNull([flatamount]),[Calc],[Calc2])) AS [Dec], tblProjectDetails.cutinmonth, tblProjectDetails.DMO, tblProjectDetails.fcstloc, tblProjectDetails.projecttype, tblManagers.last, tblManagers.managerid, tblProjectDetails.currentsupplier
FROM tblManagers RIGHT JOIN (SubType RIGHT JOIN tblProjectDetails ON SubType.SubTypeID = tblProjectDetails.subtypeid) ON tblManagers.managerid = tblProjectDetails.managernew
WHERE (((tblProjectDetails.status)<>"Concept" And (tblProjectDetails.status)<>"Tabled") AND ((tblProjectDetails.producttype)<>"3") AND ((tblProjectDetails.confidence)="1") AND ((tblProjectDetails.cutinmonth)<>13) AND ((tblProjectDetails.DMO)<>Yes) AND ((tblProjectDetails.fcstloc) Is Null) AND ((tblProjectDetails.projecttype)<>"Other") AND ((tblProjectDetails.cutinyear)=2006) AND ((tblProjectDetails.currentsupplier)<>"Venture"));

Everything works great except for the very last criteria <> "Venture". I have one project that this query should leave out but it eliminates any project with a Null in the supplier field instead of just supplier "Venture". Why would it do that and what would be the solution?

Thanks

Toni

View 2 Replies View Related

Query Stopped Working

Mar 3, 2006

I made some minor changes in my data entry form, the table and the query form. I believe they are all consistent. But now my query comes up empty no matter which parameters I choose. How do I figure out what went wrong?

View 2 Replies View Related

Insert Into Query Not Working

May 10, 2006

I have designed a database to store training records. I created a form with a multi-column text box so I can select all employees who have attended a training session that day, and update all records at once. The form has a combo box, as well as two text boxes. Even though the listbox has three columns, I only need to pass the first column. The three columns (in order) are Employees.EmployeeNumber, Employees.FirstName and Employees.LastName. Selecting the command button should run an append query to enter the EmployeeNumber, SOPNumber, RevisionNumber and TrainingDate to the SOPTraining table.

FORM
frmAppendSOPTraining
lstEmployees (unbound)
cboSOPNumber (unbound) (the numbers are pulled from an SOP table)
txtRevisionNumber (unbound)
txtTrainingDate (unbound)
cmdAddRecords

TABLE Employees
EmployeeID (AutoNumber)
EmployeeNumber (Number Long)
FirstName (Text)
LastName (Text)

TABLE SOPTraining
TrainingID (AutoNumber)
EmployeeNumber (Number Long)
SOPNumber (Text)
RevisionNumber (Text)
TrainingDate (Date/Time)

I had some errors in the code at first, but with help from another site, I was able to fix those. The problem now is, that the SOPTraining table is not being appended. :(

Private Sub cmdAddRecords_Click()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQL2 As String
Set frm = Forms!frmAppendSOPTraining
Set ctl = frm!lstEmployees
strSQL = "INSERT INTO SOPTraining (EmployeeNumber, SOPNumber, RevisionNumber, TrainingDate) VALUES ("
strSQL = strSQL & "'" & Me.cboSOPNumber & "', '" & Me.txtRevisionNumber & "', #" & Me.txtTrainingDate & "#, "

For Each varItem In ctl.ItemsSelected
strSQL2 = strSQL & ctl.ItemData(varItem) & ")"
CurrentDb.Execute strSQL2
Next varItem
End Sub

View 4 Replies View Related

V. Simple Query, Not Working

Jun 25, 2006

Hi Lads

Hi guys, I want to return all the records which contain information under a particular column heading, hence the statement thats supposed to return all tables where Sector colum is not empty. I am new to SQL and Access so not sure why this simple query isn't working. It is a subform and displays the results in the "details" section of the form below the controls.

I think it could be something to do with the fact that its a subform, but Im not sure about this.

Me.RecordSource = "SELECT * FROM [tblRecordInfo] WHERE Sector <> "" "

any ideas guys? any assistance is greatly appreciated

thanks in advance

View 2 Replies View Related

Query Criteria Not Working

Aug 31, 2006

Does anyone know why this is not working for me?

I am trying to change a query to add additional criteria and the records that I am trying to exclude keep showing up.

Original query

Field - "otherloc"
Criteria - <>"Supplies forecast" Or Is Null
New Criteria - <>"Supplies forecast" Or Is Null Or <> "PO*"

I am trying to tell it to exclude any record in which "PO" is the first term in the field. The data in the fields in my main table are like PO 702968.

I have tried changing it to PO702968 but nothing seems to matter, the records won't go away. I also tried to duplicate all of the criteria in my query on a second line and add the new criteria and I get the same results.

I've never seen this before and I can't figure out why it would be doing this.

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved