It was suggested to me to use a Not Exists Join to accomplish the below problem. I'm not sure how to write SQL for this.
I have the following:
1,000 records in table A
500 records in table B
Field ProductId is common to both tables.
If a ProductId from table A does not exist in B table a record needs to be added to table B. In this case 500 records from table A need to be added to table B.
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
Hi,
I've got an Access 97 piece that imports text files, copies and moves the used files and several other things.
I've now been asked to enable it to create a fairly complex folder structure.
I've done that, but I'd like to be able to issue a warning if the root folder already exists, can someone please show me how to check if a particular folder exists (I don't particularly care if its full or empty).
Hi,
My database has 3 fields ID,Cust_Balance,Cust_Type.
I need to assign Cust_Type as 1,2,or 3 based on Cust_Balance amount.
If Cust_balance is lessthan 3 million then Cust_Type is 1,if between 3 and
10 million then type 2 and,more than 10 million as type 3.How do i accomplish this in Access ?Is IIf the right choice?:confused:
I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.
SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;
I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.
If you can help, thanks in advance.
Dave
Hello all. I'm trying to finish up a database for our farm and am running into issues as I'm pretty new to Access. I know VB6 but not a lot of VBA. We spray chemicals, and need to keep track of the acerage we spray for the date.
So here's my problem:
When I go to write my records, I need to search a table for a date. If the date exists already, then I need to update the total acreage related to the date. So first, how do I search the table and keep track of if the date was or wasnt found, and if found, calculate total acreage from value pulled from table, and the new acreage value, then write that back to the table.
Would really appreciate the help. Thanks.
Hi,
I have an access database with 8 tables and as many queries and forms. Our company uses an application that performs backups of other companies data, this data is imported into access into one table. The backups are scheduled to run overnight, but sometimes they do not start for whatever reason. We need to use a query to identify when a backup does not start, so therefore to identify when data does not appear or exist in the table.
This problem is only concerned with two tables. In one table i have the backups policies (Backup Policy) and the details about when the backup should run. In the other, the main table (Backup) is the actual data that has been imported. So far I have queried only the backup policies that should run, but i need to go one step further and compare this to data in the Backup table that does not exist.
Im not sure whether this should be a bit of code in the query that compares data in the Backup Policy table with that in the Backup table when it is not present.
Ideally i need something that says. "If a particular policy name does not appear in any record in the Backup table, report back a list of policy names."
OR
(Lookup a list of policy names in table backup policy,
if these policy names do not appear in the backup table, report back a list of the policy names,
if these policy names appear in the backup table, do not report back a list of the policy names)
If anyone has any ideas if this is possible, and how, I would be most grateful. Also, if it involves the use of code or functions etc I may need a little bit of guidance!!
Let me know if you need any more details of the table structures.
Hi all,
I have a form that is based on a query that uses a yes/no option as the filter. the problem i am having is that in this form if a user has more than 1 no entry the users name shows up as many times as entries that they have. i would like this to be shortened down to only one time for all no entries. is this done within the query?
SELECT Employee.EmployeeLastName, Employee.EmployeeFirstName, Employee.EmployeeID
FROM Employee INNER JOIN Expense ON Employee.EmployeeID = Expense.EmployeeID
WHERE (((Expense.AdminApproval)=No));
OR could this be done better in the on load of the form using a counting loop?
I need a function or way of dealing with a field that may or may not exist.
I am crosstabbing a large database and then building queries on the crosstab. However, the field from which the column headings come does not always have the same data in it as I have to cut the data in different ways (always similar but not always the same). As a result, sometimes I get the error message:
"The Microsoft Jet Engine doesnot recognise [FIELD] as a valid field name or expression"
where a field I was expecting did not appear.
If it was a null value within a field I remove it like this:
iif([Field] is null, 0, [Field])
Is there an equivalent formula for "exists" that will stop my queries falling over when it cannot find the field?
eg iif(exists([Field]),[Field],0)
Any help, guidance or assistance gratefully received!
I would like help with a macro that looks to see if a record in my database table exists, then if it doesn't it adds a record with my data, but if the records does exist, it stops the routine and sends a message box to prompt if you want to continue with adding a new record.
The problem that I'm having is that my macro errors out when it doesn't find a record. I just want my macro to just see if a record exists or not. Then I can write my Iff statements based on whether it finds our or not. How do I just have my macro check for an record, then if it doesn't exist tell that it doesn't exist and not stop my macro. Is there some kind of IfExist() function or something?
Thanks for any help anyone can give me.
Hi,
I put a Dlookup function to compare the new entry in a form and the code is like this:
If (Not IsNull(DLookup("[empno]", "Attnd_tbl" , "[empno]=[empnotxt]")))then
MsgBox "This Employee Already Exists"
cancel = true
Me!empnotxt.undo
end if
end sub
this works for comparing one field only, since I want to check if the employee is already entered on a certain date, how do I put two fields in this function to be looked up if for example "[empno]and[date]","Attnd_tbl","[empno]and[date]=[empnotxt]and[datetxt]"
I'm using the following code to autofill the city and state on my form.
I can't figure out how to capture if there is no matching record in the table.
If there is no matching record, I will:
1) inform the user with a msgbox
then if the user wants
2) open a form to add the record to the table
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("tblZipcodes", dbOpenTable)
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
If strZipLookup = rst!ZipCode Then
Me.txtCoCity = rst![ZipCity]
Me.cboCoSt = rst![ZipState]
End If
rst.MoveNext
Loop
rst.Close
End If
How can I capture if there is no matching record in the table?
Thanks,
Sup
I have set up a simplified event booking procedure, similar to the one generated by the access event manager wizard. It uses three tables, one with attendees details, one with the event details and abooking table that links the attendee ID with the Event ID.
The booking table uses commands similar to SELECT[Names + Contacts].[ID],[Names + Contacts].[Surname]FROM[Names + Contacts] to generate drop down lists of surnames and events so that the two can be matched together.
This works fine as a table.
However when placed into a form, whenever the form is opened I get an error message saying the record source specified on the form does not exist.
I have tried regenerating the form, etc. I have used a wizard to generate the form, closed the form, not changed anything in the database and then tried reopening it and I get the error message, can anyone shed any light?
Also if you accept the message then it knows how many records there are, so it must know there's something behind it, also if you change to design mode and then back to form mode, it finds all the data.
I am trying to import data from excel into one of Access table but it showing error F30 does not exist in destination table. I have checked headings which are matched. Same sheet tried to import in access successfully, but unable to import specific table...
View 1 Replies View RelatedHello all
I had a query which was working fine to tell me the number of weekend bookings for holiday parks for a specified year:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date])
HAVING (((Year([Start Date]))=2005));
However it has become necessary to only find bookings of specific accommodation types so I modified it like so:
SELECT Bookinfx.[Park Name], Month([Start Date]) AS FilterMonth, Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings
FROM Bookinfx
WHERE ((Bookinfx.Day)="FRI" Or (Bookinfx.Day)="SAT") And ((Bookinfx.Nights)<=3) And ((Bookingfx.[Accom Type])="CHALET/LODGE" Or (Bookinfx.[Accom Type])="STATIC VAN")
GROUP BY Bookinfx.[Park Name], Month([Start Date]), Year([Start Date])
HAVING (((Year([Start Date]))=2005));
Now when I run this, the first problem is that it asks me for the accommodation type, which is odd as I've already specified this in the query. The second problem is that if I enter say 'chalet/lodge' it returns records telling me that there are bookings for chalets and lodges at parks that only have camping! I have trawled through the database to make sure no erroneous records exist and so I know it is a problem with the query.
If anyone can shed any light on what I've done wrong, I would be extremely grateful! :)
I have a training database with multiple courses. Certain staff do course1 and thereafter course2. Course1 is only done once and course2 is a refresher done annually. I want the query to find staff that have done course2 but not course1.
Thanks.
I need to find all MaintItems which field MIC don't exist in either MIC1 or MIC2. So the only result which I need to see is MaintItem '5100161086' as MIC <> MIC1 Unfortunately I can create query which would approve '5100662734' as well
MaintItem ------MIC------ MIC1 ------ MIC2
5100634036 50000173 50000173
5100662790 50000174 50000174
5100662734 50000145 50000145 50000180
5100662734 50000180 50000145 50000180
5100161086 500001711 50000173
So bottom line I need a query which will display only the row below as MIC doesn't exist in neither MIC1 nore MIC2. Also I will have to deal with much more MICs soon so maybe there is a way to concatenate them and compare, isn't it? 5100161086 500001711 50000173
Attached query is what I got so far, but that displays last 3 rows of table above
Hi,
I'm trying to have a button on a form that would delete a record from a table only if there are no child records linked to it.
If there are child records for the selected record, the button would be disabled.
Is there a simple way of doing this?
TIA.
Afternoon.
I have had a search through the forum however am unable to find anything...
I have two questions:
a) How do I check whether someone else is currently accessing a record / form?
i.e. Opening the form (SchemeDetails) like this:
DoCmd.OpenForm "SchemeDetails", acNormal, "", "", acEdit, acNormal
Forms!SchemeDetails.SetFocus
DoCmd.GoToRecord acForm, "SchemeDetails", acGoTo, tempVal
Can I check if someone else is in the specified record before I open it? Record Locking is (deliberately) set to "No Locks".
b) How do I check to see if a table exists?
i.e. If DataTable2.Exists = True then ...
Or something like this?
Hope someone can help me!
I have a database with 200.000 records, how I Add Primary Key Field.
I'm getting error "File sharing count exceeded..", tried to increase "MaxLocksPerFile" registry , but without success.
My problem is if there is no FaultTotals nothing shows up. I would still like for the Four Columns to show data and have a 0 in the FaultTotals.
How can I accomplish this?
In a nutshell I am totaling all Cosmetic Faults based on the SystemGroup CTWT and a Date Range. If there are
no Cosmetic CTWT Faults for the DateRange I want a zero. The query works great as long as there is at least
One FaultTotal for the Date Range.
SELECT "1-3" AS Truck, "Cosmetic" AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate)
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup
HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"))
ORDER BY Count(*) DESC;
I have a table that is filled based on selections that a user makes in combo boxes on a form. I am having trouble trying to figure out the best way to prevent duplicates from being inserted in the table when I do the append query part.
Since the table is filled based on the various selections the user makes I dont have a primary key or unique field.
Can someone help me figure out the best way to do this append/update queries to prevent duplicates. Also if you have a strategy for setting up some sort of unique or primary key for fields that could all be the same yet in the table only 1 record will show, no duplicates.
Hi
I have to rethink and change the way of display in my form.
I have a form bound to ID.
I want to show 2 fields if they contain data, otherwise hide them
How do you do that?
Micke
How would i write an if statemet that does the following:
If Forms!Form1!Field1 exists in table1!Field1 then run Query1
Let me know,
Thanks,
ovadoggvo
I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.
For example, imagine this table:
Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
How would I get this ouput:
Name MonthHours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0