Delete Records With Multiple Relationships
Sep 20, 2006
Hi
I am in the process of designing a sports database and have come across a problem that I can not seem to fix.
I have 3 tables that are linked, they are Customer, Program (this is the customers exercise program) and Health (the customers health records).
I have tried to set up a query that deletes a customer if a command button is clicked. This works find as long as the table I am deleting the data from is not linked to another table.
However when I try to delete a customer record it says that I can't as the data is linked and I have to delete the relationships first.
The only way I can think of doing this is to setup a query that deletes all the data in each table at the same time. I can not get this to work though.
Is this a problem that can be solved or will I have to change the way my relationships are set up?
I would appreciate any help or suggestions.
Thanks
View Replies
ADVERTISEMENT
May 9, 2007
how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
View 5 Replies
View Related
Aug 15, 2005
Hi,
I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.
How can I write the SQL DELETE query, so that only 1000 records remain based on the query?
Thank you in advance for your great help.
regards,
Pranesh
Alberta, Canada
View 1 Replies
View Related
Apr 26, 2006
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
Thanks in advance for your help...
View 3 Replies
View Related
Sep 7, 2014
If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this
given the following with one record:
Private Sub Form_Delete(Cancel As Integer)
DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)"
Me.Refresh
End Sub
PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.
View 4 Replies
View Related
Sep 21, 2015
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies
View Related
Jan 23, 2012
I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
View 5 Replies
View Related
Mar 3, 2006
I know you can manually delete table relationships in an .mdb, but I was wondering: Can you programatically delete table relationships? If it is possible, how would it be done?
Thanks in advance to all you nice folk who reply.
View 14 Replies
View Related
Feb 19, 2014
I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?
Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.
Details:
Access 2010
Table "Employees" - holds username and some other user specific info.
Table "Hours" - holds username related data(how many hours they worked on specific dates).
Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours).
Then have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.
View 5 Replies
View Related
Jul 6, 2006
I have the following problem.
I have 3 tables made up of ID and NAME and other parameters.
NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.
Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.
see attached picture for the relationships.
http://www.members.iinet.net.au/~thydzik/temp_diag.jpg
View 5 Replies
View Related
Mar 14, 2005
I have an address table which had five one-to-many RI relationships with other tables. I recently added two more tables/relationships and it seems to have slowed down my database. I made other changes at the same time so can't be sure. Would I be correct in saying that this is something that can cause performance problems? Is there anything I can do to improve the performance?
View 4 Replies
View Related
Aug 17, 2005
Hi
I need three tables with a relationship between their primary id columns.
The first two tables:
CREATE TABLE [Table2] ([id] INTEGER PRIMARY KEY);
CREATE TABLE [Table3] ([id] INTEGER PRIMARY KEY);
and then I need the last SQL query to create Table1 with column id related to the column id in table2 and table3.
As I understand the relationship must to set when creating the table...
so this is what I got:
CREATE TABLE [Table1] (ID INTEGER PRIMARY KEY CONSTRAINT Table1ID REFERENCES [Table2](ID) REFERENCES [Table3](ID));
This produces no errors but when checking in MS Access there are no relationships between them.
Any help with this is very appreciated!
View 2 Replies
View Related
Aug 15, 2006
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
View 6 Replies
View Related
Mar 30, 2012
HR.zip. I'm trying to create database for HR and all the tables has to be linked with on table "PFEmployees (one employee has to be linked with all other tables "beneficiary,disciplinary,caruser,otherinfo etc...). Is it possible to do it.
Look into attachment relationship. I am really stuck with this.
View 1 Replies
View Related
Jan 21, 2013
I have a table for keeping track of software keys This table has all the software keys and an ID field in it.
I then have a table for computers that currently I have it set up so that there is
OSID Field linked to SoftwareID
OfficeID1 Field Linked to SoftwareID
OfficeID2 Field Linked to SoftwareID
On a form I would like it to list ALL software no matter if it is linked or "installed" and show for those that are installed the ComputerID that they are installed on.
Currently no matter how I set it up I can only get it to show the ComputerID for the OS only or for the OfficeID1 only. I want to see if the software is on a computer no matter if it is in the OSID, OfficeID1 or OfficeID2. How do I do that if it is possible?
View 3 Replies
View Related
Feb 7, 2014
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
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
View 14 Replies
View Related
Nov 16, 2006
Hey all, i would appreciate some help with Access here.
I am an attorney, and i am trying to set up an access database for all of my cases.
I have one table in which i keep the names, contact information, etc of all the witnesses who i have used or will be using (especially expert or law enforcement witnesses who i will be using in multiple cases).
I also have a main table in which i have a record for each case. Because I have multiple witnesses for most of my cases, I have had to create multiple relationships between my main cases table and my witnesses table.
My question is this: How do I specify when setting up a report (or a form/query/etc) that when i ask for the witness's address i am looking for the address i made with a particular witness, specify that relationship between the two tables, as opposed to the other relationships between the same two tables.
I hope that makes sense, and i would very much appreciate if someone could give me instructions.
Thank you.
Jay B
View 1 Replies
View Related
Aug 2, 2013
I'm trying to create multiple relationships between the same two tables, but I run into problems every time I try. I'm using Access 2007.
Specifics:
I have a table with information on meetings and there are two hosts. So I have fields Host 1 and Host 2. I have a second table that lists possible hosts and their personal information. I have a relationship between table 1 "Host 1" and the Host in table 2; I cannot create another working relationship between table 1 "Host 2" and the Host in table 2.
how I can get that to work? With just the one relationship, I can go to table 2 of the hosts, click on their name, and see all their meetings.But if I add another relationship, it takes out all of the information. I've been working on this for over an hour,
View 2 Replies
View Related
Aug 5, 2013
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
Process1_triggers
Process1_targets
Process2_triggers
...
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
View 3 Replies
View Related
Sep 14, 2014
I'm trying to create a database at work keep track of projects I'm working on and all the different events that happen during the project.
I want the main table to be customers, which will include the job number (as the primary key) name, address etc. Then I need other tables that have information about the building permit that will include dates city names etc and then another table that would include information on our sales people and so on, there would be maybe 5 tables that all connect back to the customer table.
I have tried this several times and keep running into problems, I'm sure it has something to do with the relationships. I'm missing something. I can create 2 tables and it works fine but once I add a 3rd it wont work.
View 4 Replies
View Related
Apr 22, 2005
Hello all,
I am in the early stages of building my 1st relational database (see attached). Ones I have built in the past were built on a knowledge of the sample ones provided with A97. I have since realised (from this excellent site) everything I have done in the past was completely wrong! One big table to hold all info causing hundreds of duplicate records - Doh!
My Db is being designed to keep a record of all the refurbished dictation/transcription stock we hold.
I have a question or two (basic stuff but i'm learning (slowly)).
1. Do my relationships look ok so far?
2. I want user to simply select a model in "Tbl_UnitDetail", and some how automatically lookup the rest of the info such as make and type from "Tbl_unit"
New models will be added to "Tbl_unit" by administrators as and when they become available. How can this be done?
Any advise or comments on my limited knowledge would be greatly received. Many thanks.
View 5 Replies
View Related
Mar 19, 2007
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
View 3 Replies
View Related
Sep 12, 2012
I'm learning how to do Many to Many relationships and I think I've got the basic idea down, I am just not sure how to prevent the same 2 records from being associated twice.
For example:
My practice database deals with Authors and Books.
I was able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Upgrader's Guide to Microsoft Office System 2003".
I was also able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Automating Microsoft Access 2003 with VBA".
The thing I want to prevent is something like this:
Book:
- Automating Microsoft Access 2003 with VBA
Authors:
- Mike Gunderloy
- Susan Harkins
- Susan Harkins (Duplicate)
And vise versa
View 5 Replies
View Related
Mar 22, 2008
Hi, I am designing a database for a basic payroll function. So far everything was going well until i started to design forms. I cannot delete multiple table entries from one table that connects two tables. This situation is as such:Employee Table: contains entries as follows:ID: Autonumber (PK)First Name Last Name PayCategory (Salary or Hourly) (List Box)PayInfo Table: contains entries as follows:ID: Autonumber (PK)Employee Name (Text)Pay RateThe relationship of these 2 tables is a one to many relationship; where the ID from Employee Table is connecting to Employee Name in PayInfo Table.When i created a form using these 2 tables and added a delete button to it, the entries from Payinfo table get deleted when i click the delete button, but not from the employee table.I have tried everything i can think of :( . What am i doing wrong? Can someone please guide me in the right direction. Any help would be greatly appreciated.
View 2 Replies
View Related
Nov 16, 2014
I am trying to delete all records on [TableA] that Match [TableB] at Columns [SKU] and [ASIN].
View 1 Replies
View Related
Dec 14, 2007
Hi there.
I'm trying to archive records by using append and delete queries that span accross 12 tables.
the VBA will execute them all. However, I have a junction table near the end; It will be the Junction table, then a parent then a child table. I can get the junction table keys deleted but after that I can't get the Parent and the child table records deleted.
Does anyone know how to do this. the parent table can't delete any records because I've deleted the junction table keys first but if I delete the parent table keys access obviously wont let me because it's leaving orphan records.
I'm sure there's a way to do this. hope someone can help me.
Thanks.
View 2 Replies
View Related
