Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code: DELETE Warranty FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair] WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table bub|12345|1 bub|45678|2 bub|91011|3
Child Table bub|45678|2
result in Main table
bub|12345|1 bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.
For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.
There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position.
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]
I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
I have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.
The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.
I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.
Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
I have a query which I'd like to create which would be to retrieve records from a specific month of a specific year, in this format "01/14" "02/13" etc..
Obviously it would need a prompt box for the query for the user to input the month and year.
I have a query from a table (to reduce the number of fields).The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.
Fields are:- -employee ID -start date -position
I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.I think it makes it more difficult that there is no END DATE.
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
I have a task completion database. Each record of model consists of an expiry date. I need to review and update the records before the expiry date. Each model may have more than one record as I have updated the models a couple of times. I would like to select all of records with the expiry date less than 30 days so I can plan to review and update them on time. How to select all these records.
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
I want to make a delete query, which delete record between certain dates, I want access to ask user to specify range of date before executing the query.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE * FROM M0070 WHERE EXISTS (SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
I am trying to connect to excel from Access database. Once I make the connection I want to delete data from a range on a specific sheet. There are 4 columns on the spreadsheet but i wouldn't know how many rows. So for example, A150... but I may not know what the last row is. How would I be able to delete data from a range of columns to the last row?
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError