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....
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.
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...
I have 2 identic tables: T1 and T2 which contain only one field NR.
T1 contains the data: 1 2 3 4 5 6 7 8 9 10 11 12
T2 contains the data: 5 6 7 8 A B C D
I want to select all the records found in T1, but not found in T2. So, I wrote the following SQL query:
SELECT T1.NR FROM T1 WHERE NOT EXISTS (select T2.NR from T2);
Unfortunately, this query doesn`t return any record. And the strangest thing is that the query:
SELECT T1.NR FROM T1 WHERE NOT EXISTS (select T2.NR from T2);
have the same effect like:
SELECT * FROM T1,
I mean it returns all the records of T1. I mention that the query was written in Access. What I have to do? Can anybody help me? What solutions do I have? I need a query, not a VBA code!
I need to make a query that only selects data that is in another query/table.
I have tried to do this using a query with IN criteria as follows.
SELECT fldOne,fldTwo,fldThree, FROM tblOne WHERE (((tblOne.fldOne) In (Select fldOne from tblTwo)) AND ((tblOne.fldTwo) In (Select fldTwo from tblTwo));
But that isn't working. Any ideas as to why?
I think that the answer might be using the EXISTS reserved word, but I don't know how.
How can I do an existence test on a table in my Access MDB in a script. The job is to determine if a temp table exists, and if so, then to drop the table.
What syntax would I use to accomplish this in form event code?
Hi, I have the following problem which I hope someone can help me with:
I have a form which is used to enter data into a table. One of the fields is 'Code'. I want the form to check when data is enetered into this field is it already exists, ie if the code is already present in the 'code' field in the table, and if it is it should give a warning message, if not then continue as normal.
Any help would be greatly appreciated, thanks in advance!
I have a form used to enter new contacts. I would like to insert a query or macro that would automatically to see if the name (first and last) already exists in the table to which this form is attached. If the name exists, I want to open the existing record and add information to it. If not, I want to enter the information as a new record.
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
I keep getting the error message "Table TempMsysAccessObjects already exists" when I try to compact/repair my database. The only help I've found in the MS knowledge base just says to delete the table. I keep deleting it, but it keeps coming back. I've tried the decompile cmd line option, no change. Do I need to just rebuild this db, or what?
the problem is that sometimes the table doesn't exist... Instead of trapping the error code, is there a way I can check to see if the table exists? If it doesn't I'll simply skip this step of my code and move on.
I have tried the attached code from a previous post http://www.access-programmers.co.uk/forums/showthread.php?t=98727&highlight=table+exists but some of the code doesn't work on Microsoft Access Project. Does anyone have any ideas how I would amend this code so that it works?
I have created a query/report that meets all necessary criteria except that it contains multiple TYPEs per employee. This isn’t an issue unless one of the TYPEs is ‘W’. As TYPE ‘W’ supersedes all other TYPEs, any others that meet the other criteria should no longer be reflected in the query/report.
I know this can be done i MySQL and with SQL Server but is there a way to drop the table but check if it exists first in Access before running a query ?
On my Form, I want to have a query look at one specific field for a number (1). If the number is found then I would like to have the query do basic subtraction on two other fields. (one from each table)
What I have so far is one unbound text box with the following, =IIf(tblDaStrategy!DaNumber=1,(tblDaStrategy!DaTar get-tblDaReport!DaActual1))
Simply put, if one field has the number one in it, then take the other two fields and subtract (ex. 100-75) to display 25.
It's not working. I keep getting the #Name?
Update: I think I got it. I went back to square one and made a master query that contains the data from both tables. Then I bound one of the forms to the query. I updated my IIF statement and poof. It seems to be working. Now, where's the Asprin and my pillow?
Update 2: Bummer, now my magical 25 is in place but now my form is locked tight. Guess I need to consider abandoning this concept and simply producing a report with the 25 on it. Unless someone out there has a better idea.
hello all, i've written this query and all was working fine, but.... i added the Where IN statement and no it runs really slow. i have tried using the Where exists option but couldn't get it working.
As i understand it this is probably the worst query to run on a jet database as it contains subqueries, select Disinct and a Where statement.
any /pointers to speed it up?
thanks all
SELECT LNE_ACTUAL_COST_JOIN.Project_ID, LNE_ACTUAL_COST_JOIN.UID, SUM(LNE_ACTUAL_COST_JOIN.[10/08] ) AS ActualCost, PPE.[Plan at Prior Period End]
FROM LNE_ACTUAL_COST_JOIN RIGHT JOIN (SELECT DISTINCT LNE_P3e_Activities.Project_ID, LNE_P3e_Activities.UID, LNE_P3e_Activities.UID_Desc, sum(Results_Cost_Next_Period_Activities.Planned_Co st) AS [Plan at Prior Period End] FROM LNE_P3e_Activities INNER JOIN Results_Cost_Next_Period_Activities ON (LNE_P3e_Activities.Project_ID = Results_Cost_Next_Period_Activities.Project_ID) AND (LNE_P3e_Activities.Activity_ID = Results_Cost_Next_Period_Activities.Activity_ID)
WHERE LNE_P3e_Activities.Project_ID IN (SELECT project_ID FROM Project_List_LNE WHERE LNE_P3e_Activities.Project_ID = Project_List_LNE.Project_ID ) AND
((Results_Cost_Next_Period_Activities.Year)="07/08") AND ((Results_Cost_Next_Period_Activities.Period)=11)
GROUP BY LNE_P3e_Activities.UID, LNE_P3e_Activities.Project_ID, LNE_P3e_Activities.UID_Desc ) AS PPE ON (LNE_ACTUAL_COST_JOIN.UID = PPE.UID) AND (LNE_ACTUAL_COST_JOIN.Project_ID = PPE.Project_ID)
GROUP BY LNE_ACTUAL_COST_JOIN.Project_ID, LNE_ACTUAL_COST_JOIN.UID, PPE.[Plan at Prior Period End];
I have a problem where a user would enter a project code eg 113-099. I put in a list box that filters all the project codes for instance if the user enters 1 it will show all codes starting with a 1 and every additional number they enter it filters the list box to show which codes already exists! I hope this makes sense.
The problem i have is i need to check when they enter the project code that it is a unique value. The Database developer we got in didnt index this field as no duplicates allowed and as result we have large amount of duplicate project codes.
I have started to experiment with Dlookup but with no great success.
If anyone can help it would be much appreciated as i dont have much experience with access.
The field name on the form is
[forms]![projects_frm].[project_job_number] and the table which stores this information is [projects]
I have a table called 'maintable'. It has 5000 plus entries of employees with different fields. Every Employee has unique ID. One field is date of birth (dob). Normally more than one person can have the same date of birth. A form (maintable1) is created to enter data into this table.
In order to avoid duplicate entry of the same employee again with all his data, I wish to know if the same date of birth (dob) which is being entered now already exists for any employee to verify if the employee being entered is the same, if not then I can enter the data of new employee with the same date of birth.
So basically when I will enter data in a form for the dob field, if the value is the same, then a message that entry already exists, then the 'maintable' may open highlighting the same date of birth entry, so it can be verified if the same person is not entered again.
Is there any way to check if a particular table already exist in MS Access Database using ACCESS VBA?
Actually I'm creating temp table on the file but I want to check if temp table already exits then need to delete before creating.
e.g. I can check if query defniniton already exists using below code:
Code: If .DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = '" & strTempQueryName & "' ") <> 0 Then .DoCmd.DeleteObject acQuery, strTempQueryName .CurrentDb.QueryDefs.Refresh End If
I am trying to figure out how to make my database check to see if the primary key value is a duplicate of anything ive entered previously and if yes, for my database to bring up that record instead of adding a new record.
I know how to do bring up records using a separate combobox (find) but I cant seem to do it using the primary key field itself.
