How To Use Data From Uploaded Excel File And Display Chart Based On Query

Aug 12, 2015

I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.

However , I want to get the data from the uploaded excel file.

I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"

Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?

Query SQL: "PolyWrongRegInsCount"

Code:
SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP
FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING
WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC"))
GROUP BY GROUPING.INSTITUTION;

My Chart SQL :

Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;

View Replies


ADVERTISEMENT

Modules & VBA :: Retrieving Last Modified Date From A File Uploaded In URL

Feb 3, 2014

Vbscript/vb macro code for retrieving the last modified date of a file uploaded in a URL.

I am able to get the file but wanted to get the last modified date of it.

View 5 Replies View Related

Chart Based On Form Based Parameter Query

Mar 22, 2006

Hi all,

stuck on this, not sure if it can be resolved....

I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).

No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.

Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.

Can this be resolved?

Help most appreciated!

regards to all

K

View 2 Replies View Related

Can Form UseMicrosoft Excel Chart Instead Of Microsoft Chart Wizard

Mar 22, 2006

Hi all,

I'm trying to create a chart in a form, this is not a problem, however, the chart types available are a bit limited compared to if i inserted a Microsoft Excel Chart object.

I'd do that except I'm trying to create a chart based on a query.

Is there a way to make the chart wizard use the Microsoft Excel Chart object as its chart creator so I have access to the chart types available in that object?

any assistance much appreciated.

thanks all.

regards

keji

View 1 Replies View Related

Forms :: Display Pie Chart Which Is Not Linked To Table / Query?

Mar 27, 2014

I have a status form which I use to show users the progress of various routines as they are performed. It's pretty basic; just a textbox and a couple of coloured labels; one for the outline ('things to be done') and one for the progress so far ('things done').

I have a function which I call periodically during the runtime of the routine which passes as arguments the text to display in the textbox (i.e. a description of which 'thing' is being worked on at that time) and two long integers representing what is 'done' and what is 'to be done' (i.e. 3 'things' done out of a total of 7)

So if I can divide a function into 7 distinguishable 'parts', I would call that function 7 times during the life of the overall process to show the updated status each time.

The function redraws the labels (i.e. sets the width of the 'done' label as a proportion of the width of the 'to be done' label, based on the ratio of the two arguments) and repaints the form. So you get a nice animated progress bar which can be easily controlled by calling the same function and just incrementing the 'done' argument each time.

Now - and admittedly this is purely aesthetic and for my own curiosity rather than anything fundamental - I was wondering if it were possible to represent this progress as a 3D pie chart rather than a horizontal bar (label)?

For no other reason than I think it would look really tidy.

I know it's possible to add a chart object to a form but the chart wizard insists I link the chart to a table or query. In this instance, I don't want to do that; I merely want to draw a very basic pie chart based on the two values passed as arguments to the status function.

View 3 Replies View Related

Push Data From Current Record In Form Into A New Excel File, Using Excel Template

Sep 10, 2007

I searched the archive and didn't find quite what I was looking for, so..

I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.

I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.

Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!

A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.

I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.

Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject

View 1 Replies View Related

Queries :: Create Query To Find Certain Records Based On Data In CSV File

Aug 14, 2014

This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.

My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.

My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.

I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.

The plan being to delete out those that are identified by the query.

View 6 Replies View Related

Reports :: Using Transfer Spreadsheet Command To Output Data In Query To Excel 2010 Format File

Aug 18, 2015

I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "q_calldetails_tmp", "c: emp estoutput.xlsx"

It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.

View 3 Replies View Related

Importing Data From Excel Csv File

Aug 1, 2005

hi, i have struggled to import some data from an excel file into an sql database..
i have used phpmyadmin and certain code snippets but have failed miserably..

basically my excel file has lots of data, and within each cell, each bit of data is in single quotes...

eg.
'jonathan' '23' 'hardman' 'cheese'

there are no headings in the excel file (as in column or row titles) the data is just raw.
once i have made the table (with the appropriate fields and datatypes for the csv file) how can i import that data into a table using ms access???

View 2 Replies View Related

Importing Data From Download Excel File

Feb 10, 2005

Hi

My question is: how do I set up the table to minimize redundant data. I have several fields that match the column headings in Excel, so the data can be imported, but fields like user name, pick slot, batch # all get redundant every time I import. My file is getting unnecessarily large. I know splitting the one table into many is the right thing to do, but don't have a clue how to import the data than. any help

Thanks

View 10 Replies View Related

Exporting Data To Excel And Opening The File?

Mar 9, 2005

Hi guys, this might be a quickie, I did a quick search but couldn't find anything :/

Basically my database creates a table which some people would rather analyse within excel because they're not comfortable with access. I can get the table exporting to an xls file no problem, howeevr what I would like is for the database to export the file and open the file in excel at the same time so the user doesn't need to open up excel and find the file etc...

Any ideas??

View 2 Replies View Related

Forms :: Unable To Display Information Based On Data In Another Box

Nov 15, 2013

I am trying to get a form to display certain information based on the data in another box. I have to input some exam results and want to assign a level to them. So, if they score < 13, the result would read "Below Entry 3" if between 13 and 21 "Entry 3" etc If this was a spreadsheet I would use an if function, but I am not sure that I can do this here

View 14 Replies View Related

General :: Displaying Query Data In Pie Chart

Apr 8, 2014

I have some data that I want to display in a chart:

Code:
Theme Type Count(Type)
Blah1 1 5
Blah1 2 5
Blah1 3 8
Blah2 1 1
Blah3 2 5
Blah3 3 10
Blah4 1 111
Blah4 2 222
Blah4 3 333

I want to display that query data in a Pie Chart, one chart for each theme with a section of pie for each Type (with the value of the count determining the size). Unfortunately my mind has gone blank. Everything I try does not appear correctly, some queries also require an repeated entry of the parameters but even if I do that it doesn't display correctly. I think I need to write another query to the format:

Code:
Theme Type1 Type2 Type3 Type4
Blah1 5 5 8 1
Blah2 1
Blah3 5 10
Blah4 111 222 333

Is that correct and does that make sense? If it isn't correct how else do I do it?

View 10 Replies View Related

Display Data On Subform Based On Comboboxes In Main Form?

Jun 25, 2006

Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.

Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.

View 6 Replies View Related

Forms :: Display Data Based On Previous Record But Not All Fields

Jan 22, 2015

There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.

However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""

View 1 Replies View Related

General :: Data Altered In Transaction From Access To Excel CSV File

Nov 7, 2013

In Access column name is STKITEMNBR and data type is TEXT. 4/5 of data are numeric and 1/5 are alfa-numeric. One of data was 15E10 in Access, but was altered to 1.50E+11 when exporting out to Excel csv file. Because it was Stock Item Number it needed to stay the same as 15E10 in csv file.

View 14 Replies View Related

Modules & VBA :: Access Exported Data To Excel / File Error

May 28, 2014

I've vba in Report onformat the vba code does some data copying to External Excel file (premade by vba).Now on first run, i got success.But on opening the excel file, it showed blank space + Error"File already opened"

No White Spreadsheet is shown with data to see into file, i created new excel file in windows, and inserted the vba created/exported file as an Obj.Now Obj is showing correct and full data with spreadsheet as normal view.

View 4 Replies View Related

Modules & VBA :: Sending Report Data To Existing Excel File

Jul 15, 2014

in Access i have made a report with data and now my goal is that if i click on a button the data will be exported to a existing Excel file under the other existing data so in row A6. Is this possible true a VBA code?

View 2 Replies View Related

Queries :: Importing Data From Excel 2007 When File Path And Sheet Name Always Same

Sep 29, 2014

I have an access file in which have a table name dataupdated

I have an excel 2007 file(Datanew) which have a sheet name data

I want to update table dataupdated at regular period

How can I create a query to import data from excel while file path and sheet name is always same and other steps to import data is same every time...

View 5 Replies View Related

Modules & VBA :: Display A Pivot Chart

Nov 18, 2013

I have a need to display a pivot chart by clicking a button on my menu screen. At the moment my button will just display the query results and if I then click on View/PivotChart View my chart displays.

Code:
Private Sub Command316_Click()
DoCmd.OpenQuery "R06X - OOS Chart", avViewNormal
End Sub

View 4 Replies View Related

Display Msg Box Based Upon Query

Jul 28, 2006

I wish to display a message box, only if a query returns results. I would have it on the OlLoad portion of my form. The problem I am having is doing the sql in the vba portion of the code...it would look something like this:

sql = "select * from table"
if results of sql are not blank, then
msgbox("hello")
end if

Can someone help me please?

View 14 Replies View Related

Display Html/chart/image On Form

Nov 7, 2006

lets say i have a data field which contains hyperlink for a html file, can i display the content of the file from the hyperlink in the form when the form is loaded?

View 1 Replies View Related

Reports :: How To Display Message In Place Of Chart

Aug 11, 2014

I am currently using access 2010 and I have been wrecking my brain to figure out how to display message in place of my charts when there is no data. Currently, whenever the chart has no data to display it just shows a white blank space. I would like to replace that with a message.

View 12 Replies View Related

General :: Chart In Report Cannot Display In Percentage

Jan 20, 2014

I have problem that my chart in report cannot display values in "percentage" as it is in query no matter what.

Sample is in the attachment.

View 2 Replies View Related

Gantt Chart - Visual Scheduling Display

Jul 24, 2015

I'm able to create a gantt chart template in excel that will use date information to visually display the upcoming work. That's great, its exactly what I need. I'm unsure how to implement it.

The question is: Do I need to export data and have it generate these excel reports, or can I have it done in access somehow?

Either way that I do it I'm not sure where I'm going. I imagine its quite easy to export data to work with but I don't want to be spending time on access/excel creating a solution which isn't the best one.

View 13 Replies View Related

Query To Output A CSV Or Excel Like File

Feb 13, 2007

Hi Guys,

I have 2 tables

tblXRFResults

ResultID
SampleName
Date
SampleDate
a few others not necessary in this

and tblXRFResultsConcentration

ResultID (many records linked to 1 record in tblXRFResults)
Concentration
CompoundName

What I need to set up is a query that will allow me to generate a report that will give an excel like format, with the column headings being the SampleName, followed by the CompoundName(s) from the other table and the "rows" will be corresponding sample name and concentrations.
I am sure this is pretty easy, but I am stumped!

Any help would be greatly appreciated.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved