I Need Recursive Query Structure
Jul 26, 2006
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.
My table is like that:
Product Row material
p1 r1
p1 r2
p1 r3
p2 r4
p2 p1
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.
View Replies
ADVERTISEMENT
Apr 4, 2006
Hi All.
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?
Cheers
Chris
View 2 Replies
View Related
May 17, 2007
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.
View 14 Replies
View Related
Jun 15, 2007
Hi, I have a very simple database with 2 tables:
table: animals
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.
Sasi
View 2 Replies
View Related
Feb 25, 2014
I've a table that looks something like this:
id, parentID, name
ParentId links to id.
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);
Ok that works but the result is quiet ugly
What I get is something like
Code:
id1...id2...id3...id4
1 20 50 51
1 20 50 52
1 20 60 53
1 20 60 54
Now I could use VBA to take a look at each column and store all unique numbers... but is there a more simpler way?
View 7 Replies
View Related
May 28, 2015
I need some VBA coding to do the following tasks
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
View 7 Replies
View Related
Sep 18, 2005
Hi
I am getting data in for payments at varying periods and need to convert all to weekly figures.
So I need something along the lines of
Select Case lngRentPer
Case 5
WeekRent = curRent/52
Case 3
WeekRent = curRent/4.33
Case 7
WeekRent = curRent/4
Case 4
WeekRent = curRent/13
But how do I fit that sort of structure into a query? Or do I put it all in code behind the report in the OnLoad event?
It is more complex in that there are about 20 expenditure categories and 10 income categories all with the same sort of periodicity issues.
Any help most appreciated
View 2 Replies
View Related
May 17, 2006
http://starwithin.org/test/table.jpg
I am creating a little database for members and there role, does this structure look write.
If so I can't seem to create a form that will allow me to entere member details and select a role name and link it to though the tables.
Would appricate your help advice!
View 2 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 1 Replies
View Related
Nov 3, 2007
Good evening all!
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software
2) Installation / Configuration
3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Any advice? Many thanks.
View 4 Replies
View Related
Dec 7, 2005
I have a database to record documents received on a project. 2 main tables, documents and revisions, each document can have many revisions. I have now been asked to add a section in for actions against each revisions, that is each revision can have many actions. Am having problems understanding how to add this table. The current database has several hundred records already and I do not really want to re-enter. Can someone help.
TableDocument: counter(primarykey,autonumber), DocNumber, DocTitle
TableRevision: counter, revision, DateRec'd
View 2 Replies
View Related
Oct 27, 2005
Hi all,
I am on my way to building my first relational DB. Is anyone able to look at the attached sample and tell me if I am on the right track?
The DB is being desined to record Repair information carried out on a machine. (TblRepair)
The DB will also eventually record refurbished machines (TblRefurb) and also many other situations like stock control, etc.(yet to be designed). Before I go on I just want to check:
> Is this roughly correct so far?
> If I create a new record in the existing form, why can I not enter both UnitID and EmployeeID? Relationship conflict?
> Should I be building forms on a query? (I think the answer is Yes, just need confirmation).
> Is it because of a problem with the query I cannot enter details correctly? (as per the above question).
Any pointers would be very welcome. PS Sorry its v.old A97!
View 2 Replies
View Related
Dec 16, 2005
Hi all!
I have created a database with auto-archiving features. I have "working tables" that are populated when the product is moved to the correct process, in this case when it reaches the test department. When the product is moved out of the relevant process, the database runs several queries which archive the relevant data and deletes the records from the working table.
There are two working tables in the test department, one for test failures and one for rework activities. The question at hand is WHEN should I populate the working table for rework activities:
Option 1: Populate the table when the product moves to the relevant process. This will create and delete thousands of records from the working table every day. Using this method will cause my table to bloat to 50,000+ records that are constantly updated, appended, and deleted.
Option 2: Populate the table when (and IF) the product fails a test. This will keep the table to a much smaller size (around 2500 records) but requires the database to run several queries in the middle of the data entry process, significantly slowing my program.
So which is the faster/better process: Filtering a table of 50,000+ records or appending the working table when you need the data?
View 1 Replies
View Related
Feb 14, 2006
Is there any way to keep track of a database structure in access? For instance, which query relates to which report? Sometimes I create queries that are no longer needed but if there are a lot (which there are!) it can be easy to delete one that's needed.
View 5 Replies
View Related
Mar 5, 2006
I created a database to track tardies and absences of my employees. I would like to be able to sum the number of absences and tardies for each person per month and graph it.
Per our attendance policy, 6 tardies = 1 absence. I need to take the total number of tardies that month/6 and add to the total absences that month to equal the total attendance for the month.
I need help. Can you please view my database and give me some ideas?
Thanks,
Jason
View 1 Replies
View Related
Nov 6, 2006
Hi everyone
I would appreciate some help redesigning a database structure.
Currently the database has a table holding students personal details. Linked to this table with a one-to-many link is a table holding subject reports.
Also linked to the student table are two other tables holding prior attainment and mentoring details. These table are linked one-to-one.
Personally I believe the two tables linked with one-to-one links should be merged with the students table to become one single table holding student data.
Is this right?
View 3 Replies
View Related
Mar 22, 2007
I'm trying to find a way to export the structure of a table in my Access database.
I created a new table and I want to put it in the database on my website. However, my site is constantly being used so I have no way of downloading the database, modifying it, and then uploading it again without losing some data.
I do have a database editor on my website that allows me to run queries. So If I can get a query with all of the CREATE TABLE information (all the fields, whether they are Allow Zero Length, etc.) then I can just use that.
I can't seem to find a way in Access 2000 to export the structure of the table.
View 3 Replies
View Related
Jun 11, 2007
I have 2 tables.
1 tables cointains field_name
2 tables contains data
I want to create a query that I could use the field_name from table 1 (loop thru table1 )to see if the field_name exist in table 2
thanks
View 2 Replies
View Related
Oct 28, 2004
Here’s a little scenario:
An engineer requests that “x” amount of computers are ordered for the company. The estimated costs of the system(s) are determined. The estimated cost of the systems is forwarded to a manager, and the request must be approved by a manager before the order is placed. Once the manager approves the request, the order is placed through the ordering company. After the ordering company approves the order, an order number is assigned and serial number(s) specific to each computer is associated with that order number. At this point it is possible to track the order status via existing programs using the assigned order number and serial numbers. After it is determined through the existing software that the order has been shipped, invoiced, and received by the Receiving department, I need to physically go pick up the systems and submit them into my Central Inventory. From the central inventory, systems will be checked out to employees as needed, checked back in when testing is finished, or transferred internally between employees......
View 1 Replies
View Related
Nov 15, 2004
Hi everybody,
Please, I really need some help with my normalised tables and relations:
1.AdmissionID
AdminYr
.......
2.StudID
SName
AdminID
.........
3. ParentContactID
Fname
StudID
4. CycleNO
cycname
.....
5. DisciplineID
Discname
CycleNo
StudID
.......
6.SubjectID
subName
DiscID
....
7. ExamID
examdate
StudID
8. Results
StudID
ExamID
mark
......
I have a feeling this relationship, is still a bit messy?
I also need to keep track of students daily class attendance, at the end of the tern, or year to find out how many students dropt out of a particular class etc. Is this a new table?
thanks in advance.
haag
View 3 Replies
View Related
Dec 15, 2004
I have been reading up on normalizing table structures. I have this database that I am working on, to work as a hiring database. It should hold information about people applying to the law firm I work at.
I am looking for advice on if this table structure is normalized and sound. I am also looking for specific help on creating good forms.
Thanks for any help that can be given!
ps. I am attaching a zip that has a jpg view of the tables and their relationships, as well as the db with the table structure.
I would love any and all advice. I want a very clear concept for this db before I do anything!
Thanks again!
:D
View 2 Replies
View Related
Feb 21, 2005
hello all
Consider i have an employee database.
And i want to keep a track and analyze all the training programs every employee has attended from the day he joined ( for both working and resigned employees).
The analysis part is based on division-wise, level-wise, year-wise, location-wise, trainingtype -wise etc..
I have a main employee database with his ID, name, location, age. And i have created seperate tables for his designation, dept, training program, year ,etc...THe reason for this is that the departments, designation , training programs available within the company get updated often. And I have created relationships between them.
When an employee resigns or leaves the company, i have to store the records, but I dont need to update it.
One option is ; whenever a employee leaves the company delete his related record and transfer them to to another table called OUT_Emp.
Just the delete the employee record from the Employee table. And keep the other relationships intact.
Or should i create seperate database and transfer all the tables from the main database, just for the employees who have retired, resigned or suspended. THis database will contain all the tables that the main table has along with the relationship.
I really suck at one liners :D
View 4 Replies
View Related
Apr 18, 2005
I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting with a database of church members. There are four different choirs and choirs share some members. Some members of some of the choirs are also not members of our church so I will have to place non members in the member table. I am new to database design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any detail work.
What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.
The table structure I have is as follows
CHURCH MEMBERSHIP DB:
MemberId Autonumber (pk)
FirstName, Text
MiddleName, Text
LastName, Text
DateJoined, Date
Phone, Text
Address, Text
City , Text
Zip, Text
EmailAddress
BirthDate, Date
Member, Boolean
CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
MemberId, FK
ChoirId, FK
FolderNo
RobeNo
Section
ChoirOfficeId, Fk
CHOIRS DB (This lists the various choirs in the Church)
ChoirId, pk
ChoirName, Text
DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)
MUSIC DB
CatalogId, PK
Title
Composer
Arranger
Type (Single Copy/octavo or book/collection)
PublisherId, FK
PublisherNumber
VoicingId, FK (From table with possible voicing)
NumCopies
UsageId, FK (Where in the service is it appropriate
Location, Text (Where in the filing system, or off site)
ClassificationId, FK (List of classification/genre in table so can update)
PUBLISHER DB
PublisherId, PK
PublisherName
PublisherAddress
PublisherPhone
PublisherWeb
PublisherContact
MUSIC CLASSIFICATION DB
ClassificationId, PK
Classification, Text (Christmas, Easter, general anthem etc)
To be able to track performances and plan services and performances I have the following table.
PERFORMANCE DB (This is to keep track of and plan the regular service)
PerformanceId, PK
Pdate, date (Date of Past/Planned performance. Possibly more than one per day)
ServiceTypeId, FK (From table of types of performances – morning service, evening, etc)
Location
Speaker
Pianist
Organist
Introit
Invocation
Anthem
Meditation
Benediction
(etc)
I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB. My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have and when I last performed them, what options for performance (usage and classification)
Would be grateful for your comments, Thanks!!
Rmiller
View 7 Replies
View Related
Apr 21, 2005
I don’t understand how to structure tables and relationships.
I want to build a form that allows the user to search for a postal service from a group of carriers.
User must be able to input into a form:
Weight in g, Kg or lbs
Insurance level required
Whether signature is required (Yes/No from drop down list)
Collection/drop-off options (tick boxes)
Each service has a different insurance level, some need signature; some services collect, and all have different rates.
I just want to know how to structure the database.
The main part of my question is what would be my main table be and what fields would be contained in it, and what foreign keys would be introduced to pull in info.
Would you have a different table for each service?
View 9 Replies
View Related
Apr 27, 2005
I was having a discussion with a friend of mine about this... and I couldn't come up with the best explination. Hopefuly someone here can point me in the right direction.
He has a table
Recipies(Id, name, item1, quantity1, item2, quantity2, item3, quantity3, item4, quantity4)
I don't believe that this is the proper way to create a table. What happens if you happen to come across a Recipie that needs 6 items?? or more??
After researching it, I believe it violates 2NF. Is this correct?
Instead I think a better structure would be
Recipies(Id, name)
RecipiesIngredients(Recipies.ID, Ingredients.ID, quantity)
Ingredients (Id, Name)
The problem my friends sees with this method is the fact that the Xref table will get giant.
In the example... Speed is of utmost importance, as the tables will most likely contain thousands of records and be queried against frequently.
Thanks for any input.
-Mike
View 8 Replies
View Related
Jun 11, 2005
I’m struggling to fix a database for my state agency. Here’s what the social workers need:
The agency holds several Foster Parent training sessions a year. Each session consists of 9 classes. We need to track ‘student’ attendance at each class. We also need to track class dates for each student.
The fly-in-the-ointment: ‘Student’ foster parents must attend the 9 classes, but they can fulfill this requirement over several sessions. They can take classes 1, 2 and 5 in Session 1, classes 3, 4, 7 in Session 2, and so on.
Here’s what I’ve created:
tblSessions
SessionID
SessionLocationkey ( to tblLocations; irrelevant to this post)
SessionStartDate
SessionName
tblClasses
ClassID
SessionIDkey
ClassTopic
ClassDate
tblAttendance
ClassID
StudentID
tblStudents
StudentID
StudentFirstname
etc.
The Attendance table is the junction table for the many-to-many relationship between Students and Classes.
Is this the correct structure? Thanks in advance for any advice. And thanks to Pat Hartman, The Doc Man, and SJ McAbney for getting me this far with the advice I found in researching this topic.
Sean
View 12 Replies
View Related
