I have a large spreadsheet I need as the basis for an Access database.
The spreadsheet contains... Company Number, Name, Address, etc...
The Company number is consistant and always the same..
However the Name and address is different... eg LTD v Limited, ABC House v 34 High Street etc....
how I can prepare the data and get into access?
I am posting a report to the web, in a pdf format. the issue is I have data that is hidden under conditional formating in which if it meet the criteria the font is white and background white. This is great since it shows up blank on pdf. However if a slick person takes the pdf highlights the page and transfers it to a word document, he can highlight that area change the font to black and see the data. Is there a way to hide the data prior to creating a pdf.
View 6 Replies View RelatedIm using ms access 2010 and this program compatible with previously ms access.the error show is type mismatch (error 13) on
Code:
Set rbs = CurrentDb.OpenRecordset("SELECT MSysObjects.Name" _
& " FROM MSysObjects WHERE MSysObjects.Type= 1 And MSysObjects.Flags=0" _
& " and MSysObjects.Name='" & n_tb & "'")
Is it possible to load the data in input from by double clicking on data from list box ?
I have single form on which both input form and read-only form is present. as i used visible property to display or not accordingly.
User enters the data from input form. (It has been done)
User go onto read only form where combo box and list box. from drop down values load into list-box. (It has been done)
Now double click on any record then it should re-directed on input form with loading the data in editable mode for updation purpose. ???? (How this step will be done) ?
Need to get these into Access from an excel spreadsheet (located on sharepoint). I'm using the spreadsheet fields to create the table fields in Access.
View 4 Replies View RelatedI have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.
So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?
This has been a nightmare all around. Because I can't get 40 licenses for Access I'm stuck doing transferspreadsheets every day, which has worked out mostly, but I'm reaching the absolute limits of my knowledge. I can't even think straight and need help with a solution.
Here is the table data I'm bringing in (in a simple way)
Editor Client ResearchHits HitsSent AltEditor Pruned Edited
J. Doe Coca Cola 1000 500
J. Doe Coca Cola J. Schmoe 200 100
This data is brought in from two imports of data (there is actually a date column too that I didn't include). Now, what I need to do is have it subtract the "Pruned" from the "ResearchHis" and the "Edited" from the "HitsSent" while only showing Coca Cola and J. Doe once... is this possible?
Thanks.
I have an export function below that will export my table "Test" to an Excel Spreadsheet.
However I want it so i can choose where that data in the "Test" table will go in the Excel Spreadsheet i.e. I want to export all the data in to Cell "B2" of the SpreadSheet - at the moment it will export all the data into "A1"
Any help or ideas?
Private Sub Command3_Click()
'Export function
'EXPORTS TABLE IN ACCESS DATABASE TO EXCEL
'REFERENCE TO DAO IS REQUIRED
Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database
'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "E:CSCLDMSLDMSDatabaseAppLDMS_Spec.xls"
strWorksheet = "WorkSheet1"
strDB = "E:CSCLDMSLDMSDatabaseAppLDMS_IFF_APP.mdb"
strTable = "Test"
Set objDB = OpenDatabase(strDB)
'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
objDB.Close
Set objDB = Nothing
End Sub
Can an Excel spreadsheet reference an Access Table for it's data? Sort of like a vlookup, but instead of referencing another spreadsheet, I'd like to pull data in from a database.
View 1 Replies View RelatedI have users who use a form to enter data. One of the fields is "DateRange". The users tend to enter records in groups. The group of 10 records will all have the same "DateRange" As they start a new record is there a way that the "DateRange" field can copy from the prior record?
Any help would be great!
Hi
I am building a forecasting database that will have known amounts entered for various projects. If a project has no known future amount (lets say for next March), I want my query to calculate a projected number by taking the most recent equivilent month (for this last March,which may be a number or even zero) and applying a growth factor.
I thought to use Dlookup in this way, but it is not working:
Amt: IIf(IsNull([Amount]),DLookUp([amount],"data_table",DateAdd("yyyy",-1,[data_table]![date])=[data_table]![date])*[growth],[amount])
I have created "placeholders" for the non-existant months in a prior query (i.e. this formula should replace null cells w/ projected values only if there is no actual value)
I would appreciate any suggestions very much!
thanks
I'm trying to build a query that will normalize some graphical data along the x-axis. To do this I need all the peaks to be at the same point. I'll just talk in generic terms of FieldX and FieldY for the X and Y variables (resp)
In my mind the steps require me to
1) Determine FieldX at Max(FieldY)
2) Determine difference between FieldX and arbitrary normalization point (probably 100k)
3) Shift FieldX at all points to put Max(FieldY) over 100k (its a log scale so I'll be dividing)
Steps 2 and 3 are easy, but step 1 is giving me grief.
Ok, so I know how to find Max(FieldY), easy enough. Now how do I find FieldX? My first thought is a complex series of subqueries. At some point in the past, where I was smarter than I am today, I created a query that would do numerical integration. Looking back at the query I can't understand it, but I think the principle will be similar.
I have a main form "FrmTimesheetEdit" that I open in edit mode to review data in a query that uses two tables TblEmployee and TblTimeSheet. The TblEmployee has two fields called EmployeeID, and Employee. The Tbltimesheet table has a field "Weekendingdate" and "EmployeeID".Prior to loading the mainform I launch a form "frmEmployeeReportRange" that allows me to specify via combo box the Employee I am interested in and also the time periods I am interested in being "Beginningdate" and "EndingDate" These in turn are criteria for my query that supplies data to the main form "FrmTimesheetEdit" Everything seems to work ok.
However the first time I open the main form It open a form/window saying "Enter parameter Value" for EmployeeId, BeginningDate and Ending date respectively. Then the subform loads "frmEmployeeReportRange" which allows me to specify Employee plus beginning and ending dates. I select Employee from the combo box say "Emp1" and specify Beginning and Ending dates of 1-Jun-2013 and 30-Jun-2013. I then make the form invisible and the mainform opens up.I do this but get no records. If I close the main form "FrmTimesheetEdit" and reopen it the subform pops up as expected. It is preloaded with the data I entered the first time i.e. Emp1 plus 1-Jun-2013 and 30-Jun-13...If I leave the dates the same but select the next employee "Emp2" and them make the form invisible the mainform opens in edit mode as expected but with the records and dates specified for Emp1.
Bottom line it would appear that although the filter data is specified for the query that feeds the main form it is not requerying prior to opening the form. I have tried having the "frmEmployeeReportRange" launched as a VBA event on each of 'On Open' , 'On Load' and 'On Current' but no matter where I put it the results are the same in that the mainform is not requerying the data prior to opening.
I have some data in an array that I need to normalize, remove duplicates, and import.
Original Table
Every record in the array has a person, all but a few have an address, most have a phone, and some have an email.
Person----- Address ----- Phone ----- Email
Tom ----- 10 A Ln ----- 789... ----- e@a
Sue ----- 20 B Ln ----- 256... ----- _____
Sam ----- 30 C Ln ----- _____ ----- _____
Dan ----- 40 D Ln ----- 478... ----- _____
Jan ----- 40 D Ln ----- 567... ----- e@d
Stu ----- 50 E Ln ----- _____ ----- _____
Syd ----- ______ ------ 224... ----- _____
New Data Structure
I want to group the data by HouseHold; which Address will serve to define for this import.
tblHouseHold
hhID
tblAddress
adrID, hhID, Address
tblPerson
prsID, hhID, Person
tblPhone
phnID, hhID, Phone
tblEmail
emlID, hhID, Email
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset
Dim rsHH As DAO.Recordset
Dim rsPhone As DAO.Recordset
Dim rsEmail As DAO.Recordset
Dim rsAddress As DAO.Recordset
Dim rsPerson As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
[Code] .....
I have been tasked with producing a Service Catalogue - essentially, a list of services offered by IT to our business customers.
The Catalogue is a repository of information related to the service - such as, Service Info, Business Info, Server names and location, DB info, Batch and Online times, Network Info (and the list goes on).
Obviously, these are high-level categories. Within each of these (for example. Business Info) there is more granular info such as, Business Owner, Supported Business Area (there can be more than one) and Business Criticality.
Given the data and the fact that some category items may have one or many values for any given field (DB names for example), is this something worthy of a DB?
I have tossed this around in my head and I can't get away from the notion that I will require multiple tables with untold fields (yikes - scary thought!)
I have attached a rough DB but before I put any more time and effort into it, I want to know if I am on the right track.
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
I have the following xml script in my table and I want to make the 3rd button be a image that is saved in my c drive.
Code:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad1">
<ribbon startFromScratch="true" >
<tabs>
[code]....
I found the following getimage script but am not sure how to apply it to my button, I have seem various examples on sites but just can't get it to work.
Can I somehow set the icon path like C:Image1.png
Code:
Public Sub getImages(control As IRibbonControl, _
ByRef image)
Set Image = LoadPicture(getAppPath & control.Tag)
' Maybe something like
' Set Image = LoadPicture(c:image1.png)
End Sub
Simple vba code that allows me to import all the content in a specific folder into an Access attachments field? I have search but all I am seeing is code to import outlook attachments.
View 1 Replies View RelatedI am having problems with an append from a spreadsheet into a pre-populated table. I am using a macro to do this.
The table is in the middle of a hierarchy of tables. For example:
table 1 - Audit - key: AuditNumber & AuditName
table 2 - Process - key: AuditNumber & AuditName & ProcessRef
table 3 - Risk - key: AuditNumber & AuditName & ProcessRef & RiskRef
table 4 - Controls - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef
table 5 - Control tests - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef & TestRef
The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.
I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.
The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.
The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.
I have:
- tbl_company (containing company info)
- tbl_employee (containing employees info)
- tbl_cim (containing working contracts info, related with both tbl_company and tbl_empployee)
In the tbl_cim i have starting date of the contract, working time per day (in hors, according to the contract), ending date of the contract, etc.
I need:
- once a month i have to generate a "excel-like" sheet containing employees (rows) on a selected company, days of the month (1-31) i choose (columns) and hors worked on each day (according to the working_time from tbl_cim). The working hours i have to be able to modify.
- the days before start date of the contract have to be empty; the days after end date, also.
- the saturdays and sundays have to be marked ina different color (cell background).
I ask:
- what tables i have to create additionally?
- how can i create a form for this infos?
- is there a way to do this with less vba as possible?
In my country, Romania, this type of "spreadsheet" is called a "pontaj" and companies have to do it every month. I would like to step from Excel to Access with it.
The spreadsheet I've attached contains a ton of calculated cells and linked data. The people who created and utilize it aren't necessarily interested in changing the way they use it or populate it each quarter. That being said, my task is to somehow create a way that it can be sent to/seen by each person differently. First I'll point out that the 3 columns that are blank normally contain names, I deleted them for obvious reasons. The first of those columns, B, contains unique names for each agent in the firm, these people need to ONLY see their own data. The second column of names are basically managers, they need to see the records for each agent that falls under them. The 3rd column of names is irrelevant for these purposes.
Now if all this data were in access, and everyone had access to it I could easily just make records visible or invisible based on user name and I wouldn't be here right now. But they can't and that's not an option. I honestly don't know if this should be done solely in Excel, in Access or a bit of both. Currently this spreadsheet gets emailed to each manager and they have to review the data with each agent.
The first tab, worksheet, is basically instructions. It would be nice if those were images on the page and then all of the data were below that so that the agents could see them together and understand it better. As I'm sure you'll see it's a pretty complicated system so they have a hard time understanding it.
Note: There are actually about 3,000 records, I've deleted most of them for size purposes.
I'm in the process of importing an excel spread sheet from an external source (SharePoint).
I have the link for the file and have edited it from the Https:// to projectspace. intranet.sharpoint etc etc.
The problem I'm having is before importing the data into a new table in my current db I get an error
"The File "projectspace.intranetetc etc" does not exist.
I am using IE8 and the version of Access is 2007.
I have been dabbling in excel to get data from an online database however I would like it to come straight into access instead of coming through excel.
In excel it is quite easy using a web query however I have not been able to find anything that can do the same in access. Is it even possible or will I need to use excel as the stepping stone?
The data comes from this place: [URL] ....
i have to import sales figures from a branch to head office. the import facility in ms access 2.0 only allow for 1 table although the data that needs to be imported needs to be done into two tables. how would i be able to do that appending the data to a query already setup to bring in the data. it is just to import the data from the spreadsheet to the two different tables.
thanks melanie
I have a linked spreadsheet. Access is not pulling certain records from the field DOD. The code I used is as followsSELECT final.[SSN P ], final.TXPD, final.[TC-530], final.[TC-150], final.[ DOD ], final.[ DOB ], final.[SSN S ], final.[TC-421], final.[TC-420], final.[TC-424], final.[TC-540], final.[LFRZ-RFRZ], final.[TC-590], final.[TC-591], final.[TC-594], final.[TC-599], final.[TC-290], final.[TC-291], final.[TC-300], final.[TC-301], final.[TC-976], final.[TC-977]
FROM final
WHERE (((final.[TC-530])="TC-530")) OR (((final.[TC-150])="TC-150")) OR (((final.[ DOD ])="dead")) OR (((final.[ DOB ])>1929 And (final.[ DOB ])<1986)) OR (((final.[SSN S ]) Is Not Null)) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-540])="TC-540")) OR (((final.[LFRZ-RFRZ])="-AL")) OR (((final.[TC-590])="TC-590")) OR (((final.[TC-591])="TC-591")) OR (((final.[TC-594])="TC-594")) OR (((final.[TC-599])="TC-599")) OR (((final.[TC-290])="TC-290")) OR (((final.[TC-291])="TC-291")) OR (((final.[TC-300])="TC-300")) OR (((final.[TC-301])="TC-301")) OR (((final.[TC-976])="TC-976")) OR (((final.[TC-977])="TC-977")) OR (((final.[LFRZ-RFRZ])="-ALR")) OR (((final.[LFRZ-RFRZ])="-L")) OR (((final.[LFRZ-RFRZ])="-LBR")) OR (((final.[LFRZ-RFRZ])="-LBRK")) OR (((final.[LFRZ-RFRZ])="-LR")) OR (((final.[LFRZ-RFRZ])="-LRF")) OR (((final.[LFRZ-RFRZ])="-LRKF")) OR (((final.[LFRZ-RFRZ])="-LW")) OR (((final.[LFRZ-RFRZ])="-O")) OR (((final.[LFRZ-RFRZ])="-OLR")) OR (((final.[LFRZ-RFRZ])="-V")) OR (((final.[LFRZ-RFRZ])="-VL")) OR (((final.[LFRZ-RFRZ])="-VW")) OR (((final.[LFRZ-RFRZ])="-W"));
For some reason the DOB and DOD fields are not being recognized. Please help.
Hi there,
I have a spreadsheet which has a large amount of data. It is organised by database structure. I'm aware of primary keys etc but what I want to avoid is creating each field as new.
For example I would like to copy the fields from excel and paste them to create feilds in access. I'm aware that I would have to manually input any special parameters. This would save me a couple of hours.
Any help would be appreciated.
Thanks