Report Design. Layout Multiple Database Queries
Jan 9, 2007
Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
View 3 Replies
ADVERTISEMENT
Jan 29, 2014
I have sql queries in spreadsheet column and which needs to run against Db2 database daily and then update the result back to spreadsheet. This is tedious process and could there be a way to create a a macro using VB which performs the following?
1. connect to db2 database
2. take the sql query one at a time from every row and then run against database
3. Obtain the result and then update it back to last column of the spreadsheet.
View 3 Replies
View Related
Sep 4, 2013
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)
[Code] ........
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
View 8 Replies
View Related
Dec 4, 2008
I have a spreadsheet that tracks hours for employes and I need to get the data in a different layout to import to a database i'm building. The example spreadsheet has 2 tabs, the first is the format it's currently in and the second in the one I need it in.
View 4 Replies
View Related
Sep 18, 2009
My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.
I have attached a simplified example to try and explain what i would like to happen.
Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.
I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.
I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.
Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.
It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.
I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!
View 6 Replies
View Related
Jul 17, 2013
I have 2 sheet in MS Excel (MATTER & REPORT) . Database included at sheet MATTER. Report will be seen on sheet REPORT based on entry date. What formula used to produce report automatically.
View 4 Replies
View Related
Apr 3, 2013
I have a 2 sheets in a workbook by the name Entry and DataStore. I am entering the daily data in Entry sheet and then manually updating the data in DataStore sheet. Is there a macro to automate this.
I have uploaded the sample file with the expected output comments to the below link:
Free large file exchange service without size limits.
View 8 Replies
View Related
Aug 19, 2013
I have a report (roughly 4000 lines) which I need to rearrange for pivot/power-pivot use.
The structure for each row/record is:
col1 geography
col2 area
col3 customer
col4 product
col5 price
col6-65 monthly sales units - 5 years (columns labelled Jan 2009, Feb 2009 .... Dec 2013)
I want to rearrange the data as:
col 1-5 unchanged
col 6 month (data Jan 2009, Feb 2009, etc.)
col 7 sales units
Currently i have 4000 rows/records (each containing 65 fields). Iwant to end up with 4000 x 60 or 240,000 rows/records of 7 fields each. Is this possible through a data import wizard or VBA routine?
View 5 Replies
View Related
Jan 12, 2013
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
View 4 Replies
View Related
Dec 10, 2008
My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )
I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.
View 7 Replies
View Related
Mar 20, 2014
I'm new to macro and have tried copy from others who have posted before but it don't seem to lead to anywhere.
I'm trying to automated a column of web query (sheet1,column A), a batch of listing site with no tables. with the results in sheet2.
Looping part as well. Also, is it possible to just extract <title> from the page source.
Here's what I have so far.
PHP Code:Â
Sub Macro1()
'
'Â Macro1Â Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & Sheets("Sheet1").Range("A2") _
        , Destination:=Range("B2"))
Â
[Code] ........
Tried changing the destination from
Destination:=Range("B2")) to Destination:=Range("Sheet2!$A$2"))
But keep getting error to change destination.
View 1 Replies
View Related
Nov 10, 2006
In short I have a yahoo query that loop through about fifty or so stock symbols. The query data is in column A to G starting with: Date, open,high,low ,close Adj. Now I have my Destination set up like so “Destination:= Range("A65536").End(xlUp).Offset(3))” for 3 row space between is retrieval
The problem is my: xlAscending
Selection.Sort Key1:=Range("A65536"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Ascending only convert the first query to Ascending the rest of the data remains Descending, how do I get all my query data to convert to Ascending using Date.
View 7 Replies
View Related
Jul 13, 2008
I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.
I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.
View 12 Replies
View Related
May 14, 2008
I am running 1,200 queries in succession in excel through a loop (visual basic). The problem is that, somewhere around loop 60, my computer grinds to a halt. I tried putting in a pause function and throwing in an autosave, to no avail. I think it has something to do with the memory, and somehow clearing it. Excel must be holding onto results from prior queries, and just runs out of memory after 60 or so.
View 4 Replies
View Related
May 19, 2008
Lets say I have some web addresses in column A (Sheet1) and I want retrieve data from all those sites to another worksheet (Sheet2). Data from 1st site should be put to Sheet2!A1, from 2nd site to Sheet2!A51, from 3rd site to Sheet2!A101 etc.
There are some similarities with this thread: Dynamic Web Query From Cell Values, but I don't want the data to be on separate sheets and as my programming skills in Excel are rather limited, I failed to modify the solution given in there.
View 9 Replies
View Related
Jan 8, 2014
I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)
Dim Rng As Range
Dim x As Long
Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
End If
Next x
After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.
Here is an example of the data I have before the Macro with Columns.
Column B
Column C
Column F
Column I
Destination of order
booking ref
Size
weight of orders
[Code] ........
This is how I would like the date to look like after the macro.
Column B
Column C
Column F
Column I
Count
Destination of order
booking ref
Size
weight of orders
[Code] ..........
View 4 Replies
View Related
Jul 7, 2014
I have an excel sheet with a 'Home' and 'Data' page. I have imported data from an access query into the 'Data' page where I then push a button on the 'Home' page to run a macro on it. I have multiple queries in my database that I would like to be able to switch between in excel to run the macro on. Is this do-able without having to have multiple sheets?
View 1 Replies
View Related
Aug 7, 2006
I am importing a text file that has a lot more than 256 columns. The data will be updated weekly with a different text file. I know how to get past the 256 column limit when importing a text file like this. This link explains the method I am using to import the text file. [url] Since I am importing onto several sheets I get the dialogue box asking me to choose a file to import from for every sheet when I use RefreshAll. The same file that is used by the first sheet will be used by the rest of the sheets. Each sheet just imports a different section of columns from the text file. I want to avoid choosing the same file 30 plus times (once for each sheet) when I click refresh all. Is there a code that would allow me to choose the file for the first sheet, and the other sheets would follow suite?
View 9 Replies
View Related
Jan 29, 2008
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
Tasks List:
Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes
Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............
View 9 Replies
View Related
Jun 7, 2009
Each week our department receives a single Excel report that contains ~15,000 rows of data.
Column A of this report is populated with a store number.
Every time the report is received, we go through a manual process in which we have to break the report down into individual store numbers and then email the data to the branch managers for their particular store only (~40 emails).
I have attached two spreadsheets (1) Invoice Report and (2) Email List.
To clarify:
Send data for branch 124 to mike@xyz-company.com (column header + row 2 only)
Send data for branch 126 to susan@xyz-company.com (column header + rows 3-9 only)
Skip branch 140 as there is no data
Send data for branch 159 to tim@xyz-company.com and d.ortiz@xyz-company.com (column header + row 10 only)
And so on.
I want to do is to (1) create a report for each branch in the list (2) email the report to the specified recipients and (3) delete the created report.
View 5 Replies
View Related
Sep 19, 2007
I have a project that is quickly growing out of control.
I workbook made up of 14 worksheets. Oct - Sep, Summary and main.
I have been entering all my data in main, which is A - AB. Various types of data, dates, dollars, names, etc. I have been trying to sort the 'main' worksheet into the separate months based on a date in column B.
For example if the date in column B is 1 Aug, I would like the entire row copied to Aug 07 worksheet. Then in Aug 07 worksheet is the formulas to calculate the data needed for Summary page.
Pretty simple, except I cannot get it to do that. I have tried to pivot, auto sort, and a few various VBAs to no avail. One of my Googles turned up this site and many pointers have been found and are close, but most are focused on combining. The workbook is a tad over 1mb so I did not post it yet, but can if needed.
View 9 Replies
View Related
Aug 20, 2009
when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.
The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments
each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2
then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2
then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
[url]
I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label
and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.
YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.
Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website?
View 9 Replies
View Related
Apr 5, 2013
Below is a simplified/truncated version of a data set that I am using in a pivot table:
Client Asset Return
1 Port1 10%
1 Port2 12%
1 Port3 11%
1 Port4 13%
1 Port5 10%
[Code] .....
I have created a pivot table and I'm using report filters for both Client and Asset (obviously there are a lot more data points). When I filter on Client 1 I would expect to only see Port 1 - Port 7 available in the Asset filter however, I see every asset in the data set. I need to pick and choose using the Select Multiple Items check box without having to scroll through every single asset. Is there a way to easily do this?
View 1 Replies
View Related
Apr 21, 2014
I have to track how well location managers are keeping track of skill competencies.
We rate skills as...
0 – know nothing ,
1 – read training manual,
2 – check off by manager.
I need the managers to provide these ratings then I have to report what locations need more training. When an new employee is entered into the system, the manager should give them a target of 2 for each skill the employee needs to gain. If the target skill is 2 rating higher than the current skill level, I have a column named Delta that records a 2.
I have learned how to use the pivot table feature. I can make separate pivot tables using filters that show…
a) how many 2s each employee has for a target,
b) how many 2s each employee has for deltas,
a) how many 2s each employee has for a current level and I can show the date the 2s where achieved
What I need is to learn how to combine all of this information on one pivot table (or report), but the filters really seem to make a mess of it all when it is all together. adding multiple sheets to one Pivot table.
Screenshots...
Count of Targets
Count of Deltas=2
Count of Current Skills
!What I need to produce!
[Code].....
View 1 Replies
View Related
Dec 15, 2007
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
View 4 Replies
View Related
Jan 13, 2008
I came across a code on your website that looks across numerous sheets and stops at the first match. I have a similar need but only i do not want the code to stop when it has found the first match. I want the code to lookup from 1 column in 1 sheet to another workbook with 20 odd sheets, then want it to return a findings report or show all matches.
View 3 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Apr 14, 2014
I have database where I am searching for duplicates. The values I want to compare are not in the same column. For example:
A
B
C
D
E
[Code].....
I need to remove one of the 10 year old Yankee teams but not the 11 year old Yankee team.
View 2 Replies
View Related
Jan 21, 2014
This is a sample of what I am trying to accomplish (file attached). I have information in one worksheet (called MasterList) and a second worksheet called (RecordList). I want to take information from MasterList and RecordList and combine them to produce a report (Results). Assume the user does not have access to MasterList or Results.
The user would enter the UID in RecordList, which then populates information from MasterList. There can be multiple entries for each UID and there is no set number of entries (could be 1, could be 500)
The user enters the UID into RecordList, which populates information from MasterList. The user then adds in the additional information into the fields.
What I want is all the information from the Master List must be reported whether anything exists in RecordList or not. If there is information in the RecordList, display it and on a seperate line for each entry. Each UID then needs to be totalled (which can be done through pivot table later).
View 1 Replies
View Related
Aug 3, 2014
I have to create a report that captures the work of 2 different resources on each day of the month, the sheet i am working with has 3 tabs - Main (this houses the main report, with identical fields for each resource), ABC - for details pertaining to work done by the resource ABC ... and a tab called XYZ for details of work performed by XYZ. A resource can work on multiple projects and 3 different modules in one day.
So for each date, i need to bring in the details for columns .. Project Name upto the column # of FB's, for each resource that is for ABC and XYZ.
I have attached a file with sample data, it has the main tab with what it looks like before the data is pulled from the ABC and XYZ tabs ... and what it should like after the required data has been pulled from the ABC and XYZ tabs.
What i am looking for is the formula that i got to enter in the main tab in order to pull the required data from the ABC and XYZ tabs for each date mentioned in each of the columns for each resource (that is ABC and XYZ) in the main tab.
View 3 Replies
View Related
