1. Cat NA
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield NA
table: felines
1. Cat
2. Garfield
3. Simba
what I want to do is:
UPDATE animals.field3 (which starts off as NA's) to "meow" if animals.field2 matches any item in felines.field2.
it is a recursive operation and I'm not sure how to do it
here is what the animals table should look like if the UPDATE query runs as it should
1. Cat Meow
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield Meow
Any help would be great! I prefer SQL view of the UPDATE query if anyone knows how to do this. Note: I like cats and dogs equally, this is just to learn how to do such an update.
I have a set of data in a database that has a field "parent id" (the id of another row in the database) that I want to track back to where the parent id = 0
Now there could be any number of iterations needed to get through to this result so i cant just run a query once...
I could write a vb script to do the queries and build up an array of the results, but I am wanting to access this data through crystal reports so dont think that option will work.
Ideally what I want to do is create a query that will return the list of data as a result set that I can access through crystal.
Is this possible at all?
If it is, please can someone advise how this could be done?
Sooo, I finally have the chance to tear apart some of the horrible structures that live in one of my databases.
Currently it Tracks systems access levels across the company, and to do this we create Roles that are loosely based on the company structure and comprise of 4 constituent parts in the name and a bunch of stuff that govern the access level of accounts in that role on the system in question.
To handle this I have 4, interrelated, Tables called role 1, role 2 and so on which contain simply the descriptor of the role part that they contain, so that [Role 1] might contain "Finance", [role 2] might contain "payroll", [role 3] "contrator payments", [role 4] "payments administrator".
Role 1 is related to role2,3,4 and so on up the chain and each individual role table is related to the "master" Role definition which contains the access level information for the system in question.
I'm hoping at this point that everyone currently looks similar to :eek:
If not, let me add that A role can currently contain either [role 1],[role 2][role 3] and a placeholder "#no level 4#" or can contain a "proper" descriptor in [Role 4].
Because of the design, we currently have 3000+ "no level 4#"s held in [Role 4] (wheres the slap head smiley when you need it?)
Now I've been looking at a number of ways of trying to Normalise and improve this part of the DB, the obvious solution, because role 1-4 tables are purely descriptors is to just combine all of those into one "role" table, stick a junction table between it and the Role Definition table and be done with it. However this still leaves several problems, we're still, sort of, hardcoded to 4 levels within the database itself (ok so we can just add another column if we need more) and a few other obvious failings.
Still with me?
So I've started to look into the possiblity of using a recursive relationship on what is still, in effect, the Junction table between the descriptors and the Role Definition.
At a basic level I now have 3 tables:
Role ---- RoleID - PK Description - varchar
Roleconfig ---------- ConfigID - PK ParentconfigID - int RoleID - int
Roledefinition ------------- ID RoleconfigID
ParentconfigID relates to ConfigID within RoleConfig
At the moment, this structure, again at a basic level, now appears to work. However the variable elements within a role looked like a potential problem. Finding element one is simple, the [partentconfigID] is NULL. Finding the Top element when you've got 4 is simple, [configID] doesn't appear in [parentconfigID].
Where the fun starts is trying to control the recursion where you've got role1,role2, role3 being a valid role description and a role4 added to it also being a valid role description. Now as far as I can see there are two options to handle this.
1) Create in Roleconfig an entry (ok, entries) for role1,2,3 and use that as your 3 element role description. Create new entries containing the same information for your 1,2,3,4 role element. Less than ideal for, I hope, obvious reasons, we're still basically duplicating information and it is also difficult to build your role description in a query because you don't know how many elements will comprise that description.
2) Add a "valid" boolean column to roleconfig so that you can reuse your existing 1,2,3 and simply tag role 3 as 'valid', then add a role4 element and also tag that as 'valid'. The main downside to this is similar to the last one above, you know that valid means it is a top level description, but you still don't know how many elements there are and outputting a list containing
Finance-Payroll-ContractorPayments-PaymentAdmin AND Finance-Payroll-ContractorPayments
As valid roles still looks like it requires some jiggery-Pokery
I still have some concerns about controlling the recursion and ensuring that roledefinition can only relate back to a valid top level role which looks like it will require some careful planning. It's necessary to create a validation rule so that parentconfigID cannot be the configID for example, and I'll need to ensure that Roledefinition cannot relate to a roleconfig that isn't the last element in the chain.
We already "shoehorn" what are effectively 5+ element role descriptions into this structure, using recursion like this, I believe, eliminates the need for future Database changes if the front end code is amended to handle it. Which I guess is where the "discussion" part of the thread title comes in.
Sorry for the length of the thread, but this is melting my brain at the moment and it's not something that seems to come up very often so thought it might be interesting.
I am trying to write a product-row material cost program. Every product consists of row materials. When I sum up cost of row materials of each product I can find cost of products. But when the row material of the a product is a row material again the my solution does not work.
I have another table that has costs of row materials.
My query computes the cost of p1 but does not compute the cost of p2 since not knowing the cost of p1. I need a recursive structure that can compute cost of p2.
Now I would like to get all "child-nodes" for a given parentId.
The recursion goes 4 levels deep.
What I've so far is:
Code: SELECT t1.id, t2.id, t3.id, t4.id FROM tblPersons AS t4 INNER JOIN (tblPersons AS t3 INNER JOIN (tblPersons AS t1 INNER JOIN tblPersons AS t2 ON t1.id = t2.parentId) ON t3.parentId = t2.id) ON t4.parentId = t3.id WHERE (t1.id=1234);
i want to update data fields (time fields) in a table based on a number input by user (so it will search for record in table using the number and then update the relevant fields)....How can i do this am quiete confused there PLEASE DO HELP
Table 1: two columns - Child Tag and Parent Tag. Parent Tags can also be in Child Tag column. In other words, a parent can have multiple levels of children.
Table 2: one column - Backup Tag.
I'd like to have a form with a combo box, pick a Parent Tag, the search all its child tags and compare each Child Tag found with records in Table 2 to see if there is a match. Then populate all results in a tree view control.
A visual example :
Parent Tag ...Child Tag 1 - Back up tag found ......Child Tag 11 ......Child Tag 12 - Back up tag found ...Child Tag 2 ......Child Tag 21 ...Child Tag 3 ......Child Tag 31 .........Child Tag 311 - Back up tag found
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
I have several lookup tables in an Access database. These tables are used to populate fields in the main table and act as filters for viewing record subsets.I want to create a form that does the following:
1.) List the lookup table via a combobox.I was able to accomplish this with the following code:
Code: SELECT MSysObjects.Name AS [Table Name] FROM MSysObjects WHERE (((MSysObjects.Name) Like "tblJob*") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0)) ORDER BY MSysObjects.Name;
2.) When a table is selected from the combobox, display the table in a subform for updating.
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
Is it possible to run an update query to increment the value in a lookup field on all records, if the values are not numerical?
For example if the look up field contains Unit A, Unit B etc? can a query be executed so that anything with Unit A will be set to Unit B, and Unit B records will be set to Unit C?
I am wondering if there is somekind of tool that can be used in access that can run and locate any queries that are redundant and not used within a system. Also if there is any kind of tool that can be run that identifies fields that are in tables that are also not used in the application!
Not sure if such software exists but thought id ask before i manually begin to go through the application, this is all done to be done before normalisation of the application data and upgrade of the system
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
I am trying replicate the northwind web database macro called getproductname getproductlist which is on the products table. this macro has a simple lookup action which looks up a record by a parameter being sent.
the customer order form has a productid combo box. on its after update it finds the product list. this UI macro just passess the product id to the data macro on the table and looks up the price for that id. then sets the value on the form. simple right? however i consistently get an error from the data macro on the table which says the "the identifier (enter field name here) could not be found"
in my case im selecting id and productname from my table. the syntax is correct. but when i run the macro i get "the identifier productname could not be found. ive been struggling with this for hours. and im using a working sample as an example.
I have created a database with a list of product codes, pricing, costs, and supplier name. Now I have about 100 product codes which I would like to look up for all those information. Is there an effective way to do it in Access? I know I could export the whole datasheet to Excel to do a vLookup, but it may be lots of waiting time if the data are massive.
I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:
[code]...
I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?
I work for a Local Council and trying to streamline the DA process a bit by using Access. I have two tables.
tbl_Conditions which has ConditionID(PKey) and ConditionDescription Fields.
tbl_DA has the field DA (PKey) followed by numerous conditions for simplification let's assume I have only five conditions each with there own field. SC363, SC449, SC106, S105, SC32.
For each DA in tbl_DA I go through and if condition SC363 applies I input SC363 if it does not apply I input NO. This is repeated for other conditions.
Everything above this point I have working like a charm. However from here on in I am seriously struggling been stuck for two days with no success!!
I would then like to create a new table ready for merging into word. The table would be the following
tbl_DAMerge would have fields DA (Pkey), SC1ConditionDescription, SC2ConditionDescription....., SC5ConditionDescription.
The condition descriptions would be coming from tbl_Conditions the description would only be inputted if the condition applied otherwise it would be left blank.
I have tried Dlookup, and update queries with multiple joins but it always comes back doing something random.
I have a database that, I would like to add a button that performs a active directory lookup. I would like it to check a username with Active Directory, and auto populate a few fields.
First Name Last Name Manager Department
This is my first database and I have very little exp using VBA.
I want to create a Web App in Access 2013 that contains a table of client names, addresses etc, and a second table that contains order details, including client name. It would be nice to ensure that as someone adds a new order they are give a drop down menu containing existing client names, and I can see how you can do this for a brand new table using the lookup data type. But I already have an Excel spread sheet containing client names. If I import this into my Access web app to create the client table, and import the existing orders to create the order table, I then try and change the data type of the client name (in the order table) from short text to lookup, it won't let me!! (If I create an empty client table from scratch, it lets me define the company name as a lookup data type - but I can't then import from Excel into this empty table)
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
