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?
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);
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.
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.
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
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions. The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export Spreadsheet Type Excel 8-10 Table Name (query Name) FIle Name (FIle location) Has field names No Range Blank ---- This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment] UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225 Ancillary Equipment, 401 Ancillary Equipment, 1787 Brush Unit , 1252 Brush Unit , 2519 Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787 Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Code: Function CohortQ(InputDate As Date) As Integer If InputDate = 0 Then CohortQ = 0 Exit Function End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows. If I put the function into the query,
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT FROM HeciFail1 ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions: 1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.
1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.
2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.
So I run cash flow for a business, and we export data from Oracle and insert it into an access database. I have to run about 25 queries, entering in the same parameters for each. We number each week of the year. So for say the first week in January, I would run the first query and it asks: Beginning Week, I enter in 1, then another paramter value asks me the ending week. I have to enter in these parameters for each of the 25 or so queries, and it becomes quite irritating. Each query has a number of columns, but I am only interested in obtaining the sum of one of the columns, titled Distribution amount. So I am looking for something that will run each of my specified queries, then spit out the total of the distribution column for each in a table like.
Query 1: Total Distribution Query 2: Total Distribution etc....
Is there anything that would allow me to do this, with entering in the week parameter once, say week 1 start, week 1 end. and it use those same parameters for each query?
I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.
This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
