Should Use Excel Or Access For Creating New Database
Aug 30, 2015
I am new to Access and somewhat OK with Excel.I am working at a government institution, agriculture sector. We have field staff of ~150 doing different field ranges ~350. Some officers are assign to more than one field BUT no field is assign to more than one officer. (these fields belongs to different Districts, number of fields in each district is not the same)
we have four different programs namely new cultivation program (NPP), productivity improvement of existing lands (PIP), farmer capacity building (HRM) and post harvest handling (PHP). Each program has its activities lets say NPP1, NPP2, PIP1, PIP2,PIP3 etc. for an example NPP1 is land inspection, NPP2 is donating planting materials. these activities are predefined and sequential. (planting materials can not be donate without land inspection)
Officers send their progress to progress monitoring unit monthly which includes progress of each program and each activity progress for that month. My objective is to track, analyse, visualize officers progress.
These are the questions I have,As I am OK with Excel and NOT good with Access do you think I should use access for this due to any special advantage only access can give me.can I visualize data with Access?
I have attached the excel document in a zip file. Excel documents are not an authorized file type. This is going to be used for volunteers to input the shift and day they would like to work. I want to start by requiring volunteers to contact me and I will add them to the list of possible volunteers and I will give them a unique identification number.
When they contact me I will require all the information that is currently in the excel document (first, last, supervisor, ph number) I will then add them to a list of possible volunteers.
Then I would like a form they can access via sharepoint for volunteering what shift they can work. On this form it will have a box to input their unique id number and then select a shift from a dropbox (that has not been filled and auto populates). This will add them to the schedule.
Then I need a way to pull a report that shows all the volunteers and does not include their ID numbers.
As you can see the supplied document is full for March. I also have a second sheet that is blank for April.
I imagine there will be tables for
Volunteers (no duplicates in this list) Supervisors (1 supervisor can be over multiple Volunteers) Dates/Shifts (there are 2 shifts per day, there are 2 slots per shift)
I have written a piece of code in Access that creates a Excel work book. The work book is populated with the various data and then made visible to the user.
Everything is working perfectly apart from one minor problem.
When creating a new workbook excel automatically puts 3 worksheets in... If the code creates 1 or 2 sheets of data I want it too delete the sheet that is blank. This is easy to do although it always prompts the user for confirmation.
How do I delete a worksheet without the prompt?
I have hunted everywhere for the answer and can't find it anywhere, any help would be great.
Example of code:
Dim XL As Excel.Application Dim WkBook As Excel.Workbook Dim WkSheet As Excel.Worksheet
I am having real problems creating a Excel from Access. I have rea lots of threads, but none seem to address this problem.
OK, so I have windows XP, Access 2003 and Excel all running quite happily on my PC, when I copy Access databases with the verb ".Excel" in it, I can access it and update/extend as needed.
However, I have just created a new piece of VBA code in a new database and the ".Excel" extensions are not present.....
So I have
Sub openReport() Dim myXL As Excel.Worksheet Set myXL = createXlWorksheet ......... End Sub
Public Function createXlWorksheet() As Excel.Worksheet Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets.Add
Set createXlWorksheet = xlSheet Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing
End Function
And in some databases this works correcttly, but this one database,when I type the string "Dim myXL As.Ex" I do not get the "Excel" operator presented, it is just not in the list of object functions..... any ideas?? it's sending me mad!!!!
I've written this code in Access to create a pivot chart in Excel. All the code listed is good except for the last line. The last line creates another instance of Excel. I can't figure out what the correct syntax is.
Set xlApp = New Excel.Application xlApp.Visible = True Set wb1 = xlApp.Workbooks.Open("c:chi estpivot.xlsx" wb1.Sheets.Add wb1.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
Hi guys, i need help with a sample database, and wondered if anyone could tell me the entities and how to calculate the prices etc. If anyone could attempt starting the database off for me it would be appreciated too!!
Here is the spec!!!
You are required to produce and document a design that meets the requirements of the McDuffs Burgers scenario:
The corporate office of McDuffs Burgers has asked you to design a database to help track its restaurants and managers. The database is to help the management show the total annual sales of each restaurant and the performance of each manager, as measured by the totals annual sales of all restaurants for that particular manager. Each restaurant is supervised by a single manager, but a manager is also responsible for several restaurants. The company stores typical personnel data (name, salary, and so on.) for each manager as well as basic data for each restaurant such as the telephone and address of each restaurant, its size in square metres, and total annual sales for the last fiscal year. The company would also like objective ways to measure the performance of a manager such as the total revenue for which they are responsible, the average annual revenue per restaurant, the average annual revenue per square foot, etc.
The database should also track the orders that are placed by individual restaurants to the corporate office for various food supplies. Each order is associated with a specific restaurant, and of course, a single restaurant will place multiple orders during the course of a year. The company uses a standard set of product numbers, product descriptions, and associated prices that applies to all restaurants. Each order can specify several products, and a single product may appear in several orders. The database should be capable of computing the total cost for each order.
Deliverables 1. Entity definition for each entity. 2. Entity Relationship Diagram, which must show entities, relationships and membership. 3. Relationship definition for each relationship. 4. Relations (This must include for each relation the primary and foreign keys). 5. Data Dictionary.
Furthermore:
You are required to implement the design (produced in the first part of the assessment - McDuffs Burgers - Database Design), by designing and creating queries, forms, reports and any supporting code. Revisions may be made to the design in the implementation process.
You should note the management of McDuffs Burgers has little experience of database systems and wish to be advised on the information the system can produce.
They require example reports demonstrating the capabilities of the system to: - Aid the day to day operation of the business. - Provide appropriate management information.
You must also implement an appropriate user interface to the database easy to use.
I have MSAccess 2003 running on WindowsXP. I have multiple users sharing a single database. When more than one user opens the same database, a copy of the database is being created?? I don't know if this is a standard trait of Acccess or something else. Any explinations would be helpfull.
I'm not too hot at creating a database in Access, so I was wondering if there was anyone out their prepared to help me construct one.
This database is designed to search for property from various locations and from various price ranges, you can also pick the type of property, the amount of bedrooms and have the result in descending or ascending order.
For location just use: Location 1, Location 2, Location 3, and Any Price Ranges use: 30,000, 50,000, 70,000, 90,000, and 110,000 Type of property please use: houses, flats, and bungalows Bedrooms use: 1, 2, 3, 4 or 5+
Fill in any data you wish, I think this needs to have a query setup?
I am not sure where to post this question. I remember finding a forum just for Microsoft Access 2007 questions, but I couldn't find it again... Indifferent
Anyway, I have created a database, and have been testing it with my application. I have some data in this database. I was wondering if there was a way in Microsoft Access 2007 to create an empty copy of the database for release. In other words, I would like to create a copy of the database with all of the tables and columns, but without the data (rows) that I had entered as part of using my testing database...
Does anyone know if I can do this?
If anyone knows where this Access 2007 forum is, or resources that might answer my question, please let me know, as I would greatly appreciate that.
i am busy with creating a access storage database and need to calculate the number of days a vehicle is in storage, i have a [date in] field and a [date out] field. i need to calculate if [date out] is empty to use today otherwise [date out] - [date in]
I am a newbie at Access. I have uploaded an Access 2013 file and an Excel 2013 file to show what I am trying to do.
(I also incorrectly uploaded this question under QUERIES - since marked there as 'Solved")
The Access tables that I have created are meant to mirror the individual fields on the Excel spreadsheet.
On the Excel spreadsheet, I would enter data in the rows as the data becomes available.
The Access file contains the excel fields as tables. Some of the table fields consist of only one field and others have many fields.
I have created Primary Keys in all the tables. But I don't understand the process well at this point in time.
I need to know how to create proper relationships, and then, to create a query or a form to enter new data as it becomes available so that I can keep adding rows of data (query to enter and update data) and subsequently run queries to analyze the data
Need to create the correct relationships and a query that lets me update all the data for new cases, as an individual row (like on an excel spreadsheet) .
I am using an Access 2003 database to create a Word report that contains Graphs. We have since moved to Office 2010, and now the vba code will not select the graph and populate the data, i presume this is because the data is no longer in a datasheet but Excel itself.
I am trying to create an access db to monitor my expenses for my new Snack house.
I have created a "Main" table which will be holding all the daily expenses entries one the fields is called "Type" in which i formatted as combo box with the following values (vegetables , butchery , bakery).
Another field is called "Item" which should list the items based on the Type selection.
Knowing i have a second table in which i stored all types and their corresponding items , how can i force the filed "item" in table Main to simply display the ones i need based on the "Type" input.
I read in the book (Access 2013 inside out), one of the way to distributing access database is creating an application shortcut.
Now i have an Access 2013 file on my computer (with office 2013 and windows 7) other users have office 2007 and windows (XP) on their computers. now i want to give a copy of this file to other users without save as that to 2007.
I would like to know how i can do that with creating an application shortcut , if it is possible because in the book I could not find the way if there is?
I have just begun learning MS Access and have some information already in an excel spreadsheet that is getting too big and I am trying to decide if converting the whole thing would be beneficial or jsut a waste of time.
The issue is the current excel file is a workbook of about 150 separate worksheets that are all hyperlinked to each other. It does everything I want, but I feel like it is getting to big and cumbersome and really all I want is to add one sheet/record and have all the totals/averages/"reports" update themselves. That is why I think Access might be better. Currently it takes a good minute to update the file when saving. The Excel file is about 13mb.
The data is basically all times from a mens & womens cross country team. I have one form I take to each meet and record mile splits, places, final time, etc. Then I return and currently put those times into excel. I have 4 years of data for each runner. I also have 10 years of team data that needs to be able to be recalled usefully. My spreadsheet also looks through all past meets and keeps a list of the top 50 times for both men and women. And compiles every practice/workouts for the past 8 years, etc. Plus a lot of other sheets/forms/reports/.
I started a database and have one table for Athletes, one for MeetEntry. I also created a report for the meet entry to send to media. I then started working with relationships between different tables and became overwhelmed with how to best set up the entire database. I came up with a list of things and organized them into what I thought would be best suited for Tables vs. Forms vs. Queries vs. Reports. With the limited info, any ideas on setup would be appreciated. Maybe I just leave it in Excel and forget Access.
I think with the complexity, this is much better suited to my needs but it may be just a bit over my head. I do not know an VB so that is also an issue.
I have created an Access database that stores a significant amount of information about our products. I have an excel spreadsheet that performs calculations on that data. I have used the import data from Access tool in Excel to import the data. The problem I have is when the excel spreadsheet is open no one can modify my Access database.
Is there a way to have Excel open and still be able to work on the database in Access? When the user on Excel needs updated information they can just hit the "refresh" button and everything up to that point that has been done in Access will update. I do not want Excel to be able to manipulate or change any values in my Access database.
I need to open an Excel file that has been stored in the Access Database using the insert Object functionality of MS Access manually.
What i am aware of is that i cant just read the field containing the Excel File into a Byte Array and pass it to the Excel object in C#,as the file is wrapped in the OLE Wrapper used by Access while inserting the file in database.
I have tried locating the Header of Excel file from the byte array and read the file from there on but it is not working.
while (true) { if (0xE11AB1A1E011CFD0 == BitConverter.ToUInt64(byStream, i)) break; i++; } output.Write(byStream, i, byStream.Length - i-1);
byStream is a byte array into which i have read the Excel file from Database. I am locating the Excel file header in the byte stream and am writing the byte array to a file from that location.But on opening the written file it dosent work. Similar approach had worked in case of Images but now in this case.
Can some one please tell me as to how i can open the Excel File. Can I use Interop.Access object to achieve the goal??
I need some help on this one. :confused: I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets, type, model, locations, etc. The second spreadsheet has users assigned to them, phone numbers, etc. I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.
The way I have it setup now is through a linked table, then I use a “Make table query” to filter the data.
My problem is the filtered table has relationships set up that I have to delete then recreate every time I need to run the “Make table query” because it has to delete the old table first.
Everyday I have to generate a report in excel format and I need it to be added to ms access database. I was hoping to do this on a click of a button. Basically, since i have a new data everyday it should be added to the table.
I'd like to import an excel file but the data begin from cell "A10", above there is a "privacy text".Is it possibile import or link the excel data in an access table directly from the cell A10?
I have files that have extension of TSV which are text files but viewable in exel. I figured out a way for the user to click on a button in Access which does the following
1. Run Macro in Excel: The macro prompts the user to select the TSV file. After selection, macro opens the employee.tsv file in the excel (with excel being invisible) and saves it as employee.xls
Code: Sub SaveTSVtoXLS() Dim myPath As String Dim myString As Variant Application.DisplayAlerts = False With Application.FileDialog(msoFileDialogOpen)
[Code] .....
2. Imports the Excel file (employee.xls) into two tables: tblEmployee and tblDepartment using the following codes.
