Obtain Hierarchy Reports (direct And Indirect)
Jun 10, 2014
Any way to obtain all individuals that report to a particular person (directly or indirectly) through a query. A sample of the table is below:
tbl_Hierarchy
Enumber
FName
Mngr_Enumber
E111
John
E000
E222
Mary
E111
E333
Paul
E111
E444
Sarah
E999
E555
Sam
E333
E666
Tom
E111
E777
Karen
E111
E888
Jim
E666
E999
Deb
E666
For example I would like to see everyone who reports through Tom(E666). The result should yield:
E888
Jim
E111
E999
Deb
E666
E444
Sarah
E999
View Replies
ADVERTISEMENT
Nov 24, 2014
How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.
Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?
View 14 Replies
View Related
Nov 2, 2007
I'm using Access 2003 under Windows XP.
I have a very large linked table that is growing daily, and will soon exceed the 2Gb size limit of a single MDB file. So, my management have told me to host this table in their big Oracle system that does not have this limit.
However, there are many hundreds of Excel spreadsheets and tens of other Access databases on the network that explicitly use this linked table - so we do not want to change its referenced name and location if possible.
So, we thought we could keep the current linked-table's MDB file, and replace the local data table with a link to the replacement Oracle table, so that everything would look the same, and nothing external would need to be changed.
Unfortunately, Access does not let you link to a linked table - you have to link to the actual table directly.
So, we thought that we could fool it by writing an Access pass-through query in the current linked-table's MDB file that would SELECT * from the Oracle table - and name the Access pass-through query the same as the original table.
This all works fine if you open the pass-through query or write a test SQL query against it - though I suspect we may hit the 2Gb problem anyway, if it tries to make a temp table.
However, there seems to be a "feature" in Access that when you try to make an external link to that query, the Linked Table Manager says that "The Microsoft Jet database engine cannot find the input table or query 'MyTableQuery'. Make sure it exists and that its name is spelled correctly".
But, the query is definitely there and definitely spelled correctly. I'm copying and pasting the location directly from the address bar in the Explorer window, so I'm not using the wrong database. And, it must be possible to link to queries, else it wouldn't refer to queries in the error message.
I've even tried remaking the database using the undocumented /decompile MSACCESS.EXE command switch to be sure there aren't any dud references lying about.
Can anyone help?
Ron
(Access 2003 under Windows XP)
View 4 Replies
View Related
Jul 1, 2006
Hello
I dont know if this is possible in MS Access (2003) but this is what I want to do...
I want to create a database, each record has about 8 fields.
Usual basic stuff for a typical DB so far...
BUT! One of the fields is the category info, say "Item 1"
Usage:
Now another 5 records are made and are childen of "Item 1" and called..say "1a" (..b,c,d,e)
Item 1d record has 300 sub nodes to it.. say "Item 1d1" (2 to 300). And so on to eventualy make many records but linked by a huge tree like structure (like Treepad DB).
Finding Records.
Now, to find say "Item 5c22f5" I just want to see 1 drop down list, I then choose "Item 5" from it.
Then a second drop down list appears with..say 8 child nodes of Item 5, I choose "Item 5c", 3rd drop down appears, I choose "Item...22"
etc, etc. till I get to my "Item 5c22f5"
Now, while sellecting each node, some nodes in other brances contained hundreds of items but I never want to see them unless I go through their brances.
Adding new records:
eg: While working on "Item 5c22f5" I decide to add a record which will be "Item 5c22f6", I want to simply add it within the same branch but not be expose to the hundreds of other nodes/records.
Then I also would like to now add a sub of the current and call it "Item 5c22f6a". and also be be confided to the current branch. Or if I just go in 3 brances deep & see 5 levels each etc.
Finally, each record will have a field called "Notes" but I want rich text formating (like in word pad would be fine).
Maybe the ID tagging fields could be 3x on each record to show forward, current & back nodes???
What ever it takes.
I hope I've explain this OK.
I am new to MS Access so if it cant do it, I dont want to spend time learning it for nothing (at least not now).
This (if sugcessful) will be my universal, mind mapping info base. A baby I've been dreaming of for years now. Like Infohander meets TreePad meets Advance Data Managment (ADM) meets FileMaker.
Not availble anywhere in anyform as I know.
Thanks in advance. (sorry about the spelling)
View 3 Replies
View Related
Jul 25, 2013
I have some requirements whereby there are many reports that are needed to be given to the employer. I had an idea the other day and I wanted to run it by you guys. The reports can be categorized in a hierarchy (like everything else in this world of course):
=> Type
=> SubType
=> Name
Here are some examples of what I have currently:
Types => Finance, Customer, Services
SubTypes => Invoicing (Finance), Proposals (Finance), Customer List (Customer), Recent Customers (Customer), Service Types (Services)
Name => Print Invoice (Invoicing), Print Proposal (Proposals), etc, etc...
What I was thinking of doing is creating a few tables with relationships based on this, then one form from a query (that joins all the tables together) that serves as the report generating form. I would drive the cascading combo boxes by a bunch of hidden fields on the form itself.I think this would be much better than just writing a bunch of SQL statements behind combos on an unbound form. Wouldn't it?
View 6 Replies
View Related
Oct 24, 2013
I am trying to create a named hierarchy based off a table from an Oil & Gas Program ARIES. The code below is as far as I could get:
Code:
SELECT dbo_AC_ECONOMIC.PROPNUM
, dbo_AC_ECONOMIC.SEQUENCE
, dbo_AC_ECONOMIC.SECTION
, dbo_AC_ECONOMIC.QUALIFIER
, dbo_AC_ECONOMIC.KEYWORD
, dbo_AC_ECONOMIC.EXPRESSION
, (SELECT COUNT(*)
[Code] ....
The results are as such:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1
TEST1.......4................2.................TAG ............."..................22................ ..1
TEST1.......4................3.................TAG ............."..................22................ ..2
TEST1.......4................4.................TAG .............SHRINK.........1...................1
TEST1.......5................1.................TAG .............ATX...............5.................. .1
TEST1.......5................2.................TAG .............ATX...............5.................. .2
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1
TEST2.......4................2.................TAG ............."..................22................ ..1
The desired Results:
PROPNUM...SECTION...SEQUENCE...QUALIFIER...KEYWORD ...EXPRESSION...TEST...KEYWORD2
TEST1.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST1.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
TEST1.......4................3.................TAG ............."..................22................ ..3........GTC/GAS_3
TEST1.......4................4.................TAG .............SHRINK........1...................1.. .......SHRINK_1
TEST1.......5................1.................TAG .............ATX..............5................... 1........ATX_1
TEST1.......5................2.................TAG .............ATX..............5................... 1........ATX_1
TEST2.......4................1.................TAG .............GTC/GAS.......03.................1........GTC/GAS_1
TEST2.......4................2.................TAG ............."..................22................ ..2........GTC/GAS_2
First I will give background on the table and fields. Then I will explain the overall goal for creating of the TEST and KEYWORD 2 fields.
BACKGROUND
PROPNUM: UNIQUE ID
SECTION: Set of data responsible for a certain function EX: section 4 - expenses, section 5 - interest, etc.
SEQUENCE: The order of the sytanx in that propnum's section
QUALIFIER: Qualifies multiples set of syntax per section to differentiate other work (NOT REALLY IMPORTANT for the query)
KEYWORD: A specific word that the program recognizes and treates the expression according to the key word * the quotes keyword is a continuation line and represent the keyword above it*
EXPRESSION: are the variables that are treated by program according to the keyword
OVERALL GOAL: The main goal is to have a spreadsheet of variables used by the program to calculate it's end result. Which means KEYWORD & EXPRESSION by PROPNUM. THE PROBLEM is that the only way to tell that a quote keyword belongs is by having the sequence and section lined up. So my solution is to rename the quote keyword with the primary keyword and a number.
View 2 Replies
View Related
Dec 6, 2013
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having a hierarchy, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will be a distinct record.
As fas as data entry is concerned, I would like the user to not have to re-enter the same information over and over when entering multiple species entries at the same sampling station, on the same date and so on up the hierarchy.
View 2 Replies
View Related
Mar 5, 2008
I built a form that feeds data to tblOrders. In this form I have a button "Send", that must send a message to person from tblPersons.
I have a problem to get an "email" from tblPersons.
Any assistance will kindly appreciated.
Code:
Private Sub btnSend_Click()
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
mess_body = "Dear " & PersonID & "," & vbCrLf & vbCrLf
With MailOutLook
.To = PersonID ' <-- here
.Subject = "Order Accepted"
.Display
End With
End Sub
Attached also tables relationships.
View 3 Replies
View Related
Jul 3, 2007
Hello, I am having problems with one of my forms.
I use a very simple database (4tables), basically I have a table for customers, and a table for sales. When I want to enter a new sale, I have to input the customer's ID and the total amount for the sale. But since I can't memorize all of the custemer's IDs, i have to go and manually check the customer table to get their ID.
What I want to do is to get the customer ID from the same form I use to enter the sale, by just typing the customer's name.
How can I do this? thanks!!
View 1 Replies
View Related
Feb 23, 2007
I have a database that's almost finished. I've built a few forms for the major parts of the database, but some other data is only changable through the tables directly. The remaining tables (without corresponding forms) are pretty small and shouldn't require changing that often.
Assuming I put user-level restrictions on the database, would it be okay to allow certain users to directly add/edit/remove entries to these tables?
My instincts tell me I should make a simple form for each table, but I'm looking for the path of least effort.
View 3 Replies
View Related
Dec 7, 2005
I want to create a combo box that links direct to a table, rather than going through a query. Then when you select the record from the list it updates a text field to show the memo field of which the selected reccord is related
Can any one help
I tried using a query to do it, however queries only ever allow a maximum of 255 charcacters in the fields
basically the code looked like this
cboAfterUpdate()
me.txtfield = me.cboselection.column(1) ' because the data was in the seccond column
end sub
but as you can guess this only allows 255 characters to come accross from the memo field
View 1 Replies
View Related
May 15, 2007
I currently have a form in Access 2003 which is filled with quite a lot of data. Each new form need to be approved by some people, and I send them an email through Access to ask it.
Is it possible to have a direct link to the form filled with the particular data they need to approve without creating a website?
View 3 Replies
View Related
Jun 24, 2013
I use shell to run .exe file eg 7za.exe. How can i direct the output to a text file.
The following don't seem to work
shell "7za.exe > " & chr(34) & "c:log.txt" & chr(34)
or
shell "7za.exe > c:log.txt"
Problem has nothing to do with wait to complete process.
how to direct out to a file via VB.
View 3 Replies
View Related
Jun 21, 2007
Hi..(",o) anyone can HELP?
I'm currently using MS Access 2000...
May i know how can I translate the following TABLE
Doc# Team# Status
D1 TA Done
D1 TB WIP
D1 TC WIP
D1 TD Done
D2 TA Done
D2 TB Done
D2 TC Done
D2 TD WIP
to a QUERY reflecting in the following format?
TA TB TC TD
D1 Done WIP WIP Done
D2 Done Done Done WIP
Your help will be greatly appreciated!
View 2 Replies
View Related
Dec 23, 2013
How can I obtain the value of a record from my subform in datasheet view?
Ideally I could double click in the "cell", for lack of a better word, and grab that value to pass through to another function.
On double click event? What then?
Edit: I suppose the user could select the row that the value is in as well. As long as I could then find the field I need from within the row. That might make things easier.
Edit 2: Sometimes I post before doing proper research. I've figured it out. Here is a quick example. The below code references an unbound subform called "Main_AU" where the field I want is "Description". The MsgBox is used to confirm that the code is grabbing what I want it to.
Code:
Dim test As String
test = Main_AU!Description
MsgBox test
View 3 Replies
View Related
Feb 7, 2014
I'm trying to obtain all of the information in a text field for a query.
The user entered information and used a return (enter key) to enter information on a new line within the same field. The information after the return is not captured in the field in the query. Is there anyway I can capture all of this info?
View 3 Replies
View Related
Feb 3, 2015
I have a field [CurrentDay] of type Date/Time. The values for this field are entered through the selection from the date picker. However the time of the day always come along with the date - even if it is not seen. This creates a huge problem when making a query based on this field because no results will be returned unless the time is included with the date in the criteria.
The other part to the problem is that I cannot find a built in function in the expression builder to generate the current date without appending the time - now() certainly does not do that - even if the time is not seen it is there.
View 3 Replies
View Related
Jan 1, 2013
I'm trying to summarize the value from multiple fields in a table and the total value will be updated on a different table as per highlighted below (taken from Northwind Web Database).
View 3 Replies
View Related
Sep 4, 2013
I got a field which is defnined based on a query result to ease the user input. However, some input are not in the query list, if I input data directly to the field, ACCESS complained I must choose item from the query result. Is there any way that the user can either select from query result or direct input to that single field?
View 1 Replies
View Related
Feb 5, 2013
Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
View 13 Replies
View Related
Oct 27, 2012
I am trying to obtain totals from two columns in the list box into text boxes on the main form, but my third argument is not working as expected.The source of one of the tex boxes is:
Code:
=DSum("Airtickets","T_Training_Participants.ProgrammeID=Me.lstParticipants")
I want to sum only amounts of the records that equal or belong to a selected programme (ProgrammeID) in the bigger list box above.
View 2 Replies
View Related
Jun 3, 2014
I'v looking for since a couple months a go to make a report direct from access form using crystal report but i havent found it yet. I'v tried this code and its giving me errors. " run time error 1004 method range of object _global failed "
how to make a report using crystal report direct from ms access as front end application ? is it possible to use crystal report ?btw i use database sql server 2008 and MS Access 2007 as my frontend application.here's the code that i'v found and gives me an error
Dim CR As New CRAXDRT.Application
Dim rep As CRAXDRT.Report
Set rep = CR.OpenReport(Range(" ??? ")) * i getting error in this line, what should i do to fill it ??
rep.ParameterFields(1).AddCurrentValue "Boston"
rep.ParameterFields(2).AddCurrentValue "Cars"
rep.Database.Tables(1).SetLogOnInfo "tool", "db_tsel"
rep.ReadRecords
rep.PrintOut promptUser:=False, numberOfCopy:=1 ' promptUser:=True doesn't work
View 2 Replies
View Related
Oct 2, 2013
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
View 5 Replies
View Related
Jun 15, 2015
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
View 2 Replies
View Related
Feb 18, 2014
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies
View Related
Jul 11, 2013
I am still trying to get a hang of development in access 2010.
I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.
How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.
View 4 Replies
View Related
