Is this scenario considered bad design form? (see attached image)
I'll never delete a Customer or Site for whom a a Job exists. I need historical data to remain intact. I'll just mark a Customer or Site as [InActive] so they can't be used, when appropriate.
If the relationships are considered good form, then what is considered good form when deciding upon which relationships to set to Cascade Update? Do I even need to update autonumber foreign keys?
On upsizing this database, SQL complains that this arrangement is a cyclic update and won't create the relationship unless I tell it to use Triggers. This leads me to question whether I'm correctly using Cascade Updates in my Access db's.
I've done this tons of times, so I don't understand what's happening... The title says it all: when I update the ID of one record of the main table, the referenced records on related tables won't update even though they have a relationship between them with the option "Cascade update" clicked.
In the attached database, the main table "Expedientes" is related to table "Actuaciones".
What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.
Hi, I have a database with a couple of tables. The primary table has a primary key called "StaffNo". I checked the relationships between the primary table and each related table, if I have ticked on the boxes for referential integrity, cascade updates. All done. If I now create a query, which selects the primary table and one related table and enter a new StaffNo, I would expect to see the new StaffNo not only in the primary table but also in the related one. But there is nothing. Do I expect wrong? Have I missed sth.? I read a few posts in this and other forums reg. referential integrity, but I have no clue, what's wrong. :confused: Any quick help is much appreciated. Thanks :)
(named: Invalid Field Definition - Unable to set up cascade update but I have done what the moderator advised and have not been able to fix a very similar problem.
I have planned a database and must adhere to the plan or change it and basically I initially said that I would like to enforce referential integrity to synchronise my tables and I would also like to cascade update and delete to save input time because changes will be automated between table (where fields are connected.).
I have attached a plan I did if that makes the purpose of my database clearer.I have attached my database and have established the relationships* without referential integrity enforced I would like between my tables but don't know how to make them one-to-many relationships nor can I enable cascade update or cascade delete or enforce referential integrity.
I am new to databases (and to this forum) and have been promised by my line manager that I won't have to specialise, I don't think I have created the relationships but they are supposed to be between Vehicle ID and Hire Number, and Customer ID and Hire Number.
I have written an extensive Access DB Application for my company and have placed it on a cd for distribution. When we try to copy it from the CD to a folder on another machine the above mentioned error comes up. Any Suggestions. Jeff
trying to create an update query to Budget table using the Access Design View:
Field: PctSls (in tblBudget) Update to: [Expense] / [Sales]. The update query always returns 0. However, if I create a Select query using the same calculation, the correct results is displayed. PctSls is defined in the Budget table.
I haven't worked with Access (2003) for several years but this seems too simple to be causing me such frustration. (Was only a casual user even then).
I want to use Cascade Delete on a main table that has relationships with a few tables. The type of these relationships are weak and the delete will work perfectly I think. But, I am concerned about how I can only delete the FK from one of the tables because it contains a non-prime FK.
For example: Customer buys House. So Customer PK is in House as FK. If I delete customer, I don't want house deleted. Is there anyway that is native to access in how I can only delete the FK from tblHouse without deleting the house record.
Hi, all... :) I need to create cascade relationship on fly. I've succeeded to create the relationship by: ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID) and even succeeded to check the referential integrity by: ALTER TABLE tName ADD CONSTRAINT fk_id FOREIGN KEY(ID) REFERENCES tPrimary(ID) CASCADE
The real problem comes when i am trying to enforce the cascades. I do not find any expression doing it.
the standarts of sql: on update cascade or of sql server: on update cascade go just don't work in access :(
I'm using Allen Brownes very helpful cascade-to-null relations (http://allenbrowne.com/ser-64.html) code in my database project, but there comes problems whit a foreign key and null values, cause foreign key shouldn't be null. Is there anyone else using this method with relational tables? What I should do to make this work?
All hints and tips are welcome and approciated, thanks on advantage. =)
I followed the Microsoft guide detailing how to set up two cascading combo boxes. I would like to add a third box to the cascade. I have tried making some assumptions based on the code in the example but I can't get it to work.
I have three queries set up; qryBusiness, qryBusinessUnit and qryLocation.
I am using one form; frmToolInfo.
I have three combo set up; cboBusiness, cboBusinessUnit and cboLocation.
I amended my code to the following:
Private Sub cboBusiness_AfterUpdate () Me!cboBusinessUnit.Requery Me!cboBusinessUnit.SetFocus End Sub
Private Sub cboBusinessUnit_AfterUpdate () Me!cboLocation.Requery Me!cboLocation.SetFocus End Sub
Private Sub Form Current () Me!cboBusinessUnit.Requery Me!cboLocation.Requery End Sub
I want to cascade down from Business, to Business Unit to Location.
Can anyone advise me as to what I've done wrong here.
How do I create Related Cascade Combo boxes by CD Group and Name of CD To only display the records from NAme of CD related to the CD Group
Or to to a ABC and click on Artist and then click on the list with Name of CD
Someone else is wonderinf a friend of mine in Acess you can create a inventory Database to display records related like Name of Furniture Brand, Item and Search ny Last Name to see if they bought that Item
I have a form named Welcome and would like to add two cascade combo boxes.
The first combo, named cbolist get's its data from a table named QUERIES which has three fields:
ID QUERYNAME QUERYDESCRIPTION
cbolist displays the records contained in the QUERYDESCRIPTION field which are basically the name of other tables of my database with different records (obviously).
I would like that after selecting a record in the cbolist, the second combo, which I will call cboresult, will populate with all the records of that specific table selected in the cbolist.
I need some help to crete a query that will delete data in multiple tables.
My database has 5 tables. I store data in a main table and then append the ID and SSN to the other 4 tables.
The main table has also a purge date field that I will use to delete old records.
I use a form with a textbox where I type the starting date from which remove these records.
Is there a way or example I can see on how to remove all records at the same time. I have heard of cascade delete queries but really don't know how to use them.
My idea was to remove from the maintable all records with a date older than the date in txtbox and at the same time remove all records with the same ssn in all the other tables.
I have 2 tables cD Group and Cd Name 2 forms CD Group and Name I want to populate the fields in the cd Name form When someone selects CD Group field Mormon Tabernacle Chior
CD Name relates back only cd names to the cd group God Bless America
Here is the coding Row Source CD Group SELECT DISTINCT [CD Group with Name].[CD Group] FROM [CD Group with Name] ORDER BY [CD Group with Name].[CD Group];
Event Procedure After Update CD Names SELECT [CD Group with Name].[CD Name] FROM [CD Group with Name] WHERE ((([CD Group with Name].[CD Group])=Forms!frmCDs!cboGroup.value));
SQL Query
Field CD NAme Table CD Group with Name Field CD Group Table CD Group with Name [Forms]![frmCDs]![cboGroup].[value]
Private Sub Command8_Click() On Error GoTo Err_Command8_Click
Dim stDocName As String Dim stLinkCriteria As String
I'm trying to filter a combo based on the selection in another combo.The twist is that I need the row source of both combos to include an "*", "(All)" selection, as I am using these combos to filter a listbox. When I add the Union query with "*", "(All)", the row source in the 2nd combo is ignoring the where clause.
After making a selection in the Comp combo, the Emp combo should be filtered. It works with no Union in the row source, but does not work with the Union in the row source.
I need directions on Cascade Combo Box, So when I select:
CD Group
Displays only the CD's in that Group that are related to that Name So what fields would I have to set up for CD Group and CD NAme with that Table. Someone emailed me an article on Cascade combo boxes what fields so I need for the coding.......
- My second table contains if it is hardware or software:
- TypeID (Primairy key) - ApparaatID - Type (where I entered hardware and software in 2 different entries)
- The thirt table contains operating systems:
- OSID (Primairy key) - ApparaatID - TypeID - OS_versie (where I entered all the OS I want to enter)
Now I made a form I called problemen with 4 comboboxes.
- cboApparaat - cboType - cboOS - cboProbleem
1) What I want is that when you select device 1 and 4 that cboType can select both hardware and software but with device 2 and 3 you can only select software.
2) If you have selected software is cboType I want you to be able to select 1 of the 11 OS I have pre-entered in the table.
3) That you get specific problems with the previous made choices. For example if you select Hardware in cboType, thta you only get problems with hardware to select from and the same with software and OS.
4) I want a textbox, that will contain the solution of the problem you selected and that if there and several solutions that you get all of them at once.
I'm trying to Identify a particular entry in my table for editing via combo boxes. The same serial number will come in multiple times but a different job number each time it comes in. To identify a record for editing I need the user to identify the serial number in combo1 and then the job # in combo2 (cascaded combo boxes).
The issue I'm running into is that combo1 has dozens of serial number duplicates (combo2 is acting as it should). When I change some settings around I'm able to get combo1 to eliminate duplicates but now combo2 only shows 1 job # when there should be dozens for that serial #. I need all like serial numbers to show their job # in combo2 and I have not had any luck surfing around the net/forums/experimenting.
I'm pretty sure I have to use a Junction Table but I haven't messed with that and I'm not 100% sure that's the best/only way to do it.
1) How to auto fill in a text box if you select a value from the last of 4 cascade comboboxes.
I have 4 comboboxes where
1 = Apparaat - cboApparaat 2 = Type - cboType 3 = OS - cboOS 4 = Probleem - cboProbleem
With all 4 comboboxes I have the vba code Me.cbo[name combobox].Requery
If I select the last combobox, cboProbeem. I want the textbox underneath to automaticly fill in the solution of that problem. I already tried to use the following vba code
Me.Oplossing = Me.cboProbleem.Column(5)
But it does not work.
2) I want to make a form for customers, where I can fill in customer info, device info and date when there contract started. Now I want to use SUM to fill in the end date of there contract.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
