Database Setup: Use Access Or Excel
Sep 5, 2006
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.
Suggestions
View Replies
ADVERTISEMENT
Sep 9, 2015
setup a data base in Access that collects information from the attached spreadsheet pictures from A1:FO26...Spreadsheet is macro enabled and updates automatically with data.have tried to setup Access data base but when Excel Tab updates it copies over the data that was last copied into Access.
View 14 Replies
View Related
Nov 7, 2007
Hello,
ACCOUNT_NUMBER
SHORT_ACCOUNT_TITLE
CONTACT_COMMENTS
CONTACT_TYPE_TEXT
ENTERED_BY
INITIAL_CONTACT_DATE
DATE_ENTERED
These are fields that are imported from Excel. I have to import 500-1500 rows each week which contain the data I need. Then I must, by using a form, go through these "Service Calls" and mark some that give the employee credit for the call and some that are unique calls. Meaning if they have serviced the same person 5x in the quarter I can only count that once.
There can be muliple people on a service call so I have these:
EMPLOYEE1
EMPLOYEE2
EMPLOYEE3
(Entered_By field contains the employee who recorded the call and they would have gone on the service call)
Then I have to record who they contacted regarding the account. The SHORT_ACCOUNT_TITLE will often differ from who they have contact regarding this account. So I have this field:
CONTACT
Then since muliple employees go on service calls together some may have already contacted this customer aleady. I have to keep track of employees unique customer contacts. So I have these fields:
CREDIT1 (checkbox for employee listed in the ENTERED_BY field)
CREDIT2 (checkbox for EMPLOYEE1)
CREDIT3 (checkbox for EMPLOYEE2)
CREDIT4 (checkbox for EMPLOYEE3)
Then I have to keep track of service calls regardless if they where unique ones or not. So I have this field:
ELIGIBLE
Then I put a notes section in so when I am filtering the data and finding what calls where good calls and need to make changes or whatever I can have a place to put that:
NOTES
Finally since some of these customer calls involve large amounts of customers (group meetings, etc) I have this field to type in a number of additional credit:
ADDITIONAL_CREDIT1
ADDITIONAL_CREDIT2
ADDITIONAL_CREDIT3
ADDITIONAL_CREDIT3
------------------------------------
So since I have to import Excel rows that contain the data I start with I am unsure of how to set this up and normalize it. I have attached my form I have used to find what fields I would need to show you more of what I am trying to do.
This form allow me to filter data that shows up in the subform. Then I can just double click it and bring up the record. Then mark the ones that count.
I am looking for any suggestions. Thanks.
View 12 Replies
View Related
Aug 24, 2006
I have a database for an event with 2 main tables.
Parents and Children of the parents with a one to many relationship.
I need to be able add the table number that each parent and child will be at (not the same table as parent). I could add another field for the table to each database but not all the entries are attending the event.
Could someone point me in the right direction as to the best way to design this.
WarrenB
View 1 Replies
View Related
Jun 7, 2013
I have recently created a database meant for managing inventory. I have started writing queries and building forms, and a lot of it is running smoothly, but I am having a few snags.
To give a brief background, I am currently designing a form in which a user will fill out when an incoming part comes into our company. The form NEEDS to ask the user for at least the PartNumber and the PONumber, but the way I have my relationships set up, I am not exactly sure how to design the forms (not the physical layout, but where the data goes). So when I have an incoming part, will I use the PartPOID to figure out which part number and PO number it is? I am not exactly sure how to make a form for the user that is not confusing.
Attached is a picture of my relationships and my database.
New Compressed (zipped) Folder.zip
View 14 Replies
View Related
Jul 2, 2015
I have a database whereby users already have to sign in with a username and password, using a form which them allows them to view a front page. The front page allows them to access data and add/amend it.
I now have to adjust this setup as we have directors who want to view the system, but we don't want them to amend it.
Is there anyway to change the current setup that I already have or do I have to scrap it and start again?
View 14 Replies
View Related
Sep 8, 2013
I'm developing my own personal finance database and the part stumping me on the design is how to setup the categories per transaction. For example a primary category would be "home" and the secondary category would be "furnishings". How do I set this up? I'm getting stuck on how to tie it into the transaction table.
I'm familiar with writing SQL queries, but this is really my first stab at database design.
The relationship diagram is attached. The transaction table and the category tables are to the right of the diagram.
View 10 Replies
View Related
May 30, 2014
I currently have two e-mail accounts on my computer. One is my default e-mail address with my name on it. The other e-mail account is a generic account with the department name.
I have a database set up to generate and send e-mails to our technicians notifying them when an order they placed gets put on back order. This database could possibly send twenty or thirty e-mails out each time it is run. My manager has requested that the database send the e-mails from the generic e-mail account instead of the default account. That way, when the field replies to the e-mail, they won't bombard my account.
Is there a way to tell the database which e-mail account to send an e-mail from?
I am currently using the below module to send the e-mails:
Option Explicit
Dim appOutlook As Outlook.Application
Dim namespaceOutlook As Outlook.NameSpace
Sub Outlook_SendMail(sEmailAddr As String, sEmailSubj As String, sEmailBody As String, Optional sAttach1 As String, Optional sAttach2 As String)
[Code] ....
View 2 Replies
View Related
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?
View 2 Replies
View Related
Feb 22, 2015
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)
View 6 Replies
View Related
Feb 9, 2015
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 am using Access 2007....
View 5 Replies
View Related
Feb 8, 2007
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??
View 3 Replies
View Related
Jun 1, 2006
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.
Is there a better way to make this happen?
View 2 Replies
View Related
Oct 24, 2013
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.
View 1 Replies
View Related
Aug 8, 2006
I've just been given the task of "fixing" a too-big Access database. It's actually about 250 tables in 29 .mdb files scattered over a filesystem. But I'm very green at Access (although I'm pretty OK at SQL in general). So here's my question: how the heck would you all explore/learn about this Access setup? Is there an automated way to cruise through all the .mdb files, get lists of all the tables, and even lists of field names? What about getting a list of the type & relationship of each field?I know I can get some of this by opening a file, right-clicking on a table, and selecting "Design View." But doing this 250 times sounds painful, and the properties window cannot be printed, anyway.Any tips about this?-Tony
View 4 Replies
View Related
Sep 17, 2013
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?
View 1 Replies
View Related
Sep 9, 2013
I am trying to do a select query in excel vba like the below and would like to put the result in one of the macro variable in excel vba.
lngAMT = accdb.execute "SELECT Sum(Summary.NQ) AS SumOfNQ FROM Summary HAVING (((Sum(Summary.NQ))<>0));"
View 6 Replies
View Related
Aug 7, 2015
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.
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryDepartment", selectFile, True - 1, "A1:C2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryEmployee", selectFile, True - 1, "A1:AE2"
Everything is working flawless except that the user has to select the file three times:
1 time for the tsv
2 times for the xls file
Is there a way that the user can select the file only once (tsv file) or at least only twice one of the tsv file and the other for the xls file?
View 8 Replies
View Related
Oct 5, 2012
I actually have an ms access database with the following format(assuming)
title1title2title3title4title5title6
1a1b1c1d1e1f
2b2b2c2d2e2f
3a3b3c3d3e3f
4a4b4c4d4e4f
for each row in the ms access database above i need to retrieve an excel in the format below
title11a
title21b
title31c
title41d
title51e
title61f
title12a
title22b
title32c
title42d
title52e
title62f
and so on....
Where I can proceed, what tool can I use??
View 2 Replies
View Related
Feb 2, 2015
I'm trying import some information from excel file into Access Database but I need have imported information formatted in specific way.
Import file is looking like:
City - Week / 1 / 2 / 3 / 4 / 5 / 6 / 7 / 8
London / 3 / 1 / 4 / 5/ 5/ 9/ 1/ 3/ 4
Chicago / 2 / 1 / 4/ 8/ 3/ 3/ 2/ 1 / 5
Paris / 9 / 4/ 1/ 7/ 8/ 9 / 1 / 1 / 2
And i need table in Access looking more like
City / Week / Value
London / 1 / 3
London / 2 / 1
London / 3/ 4
London / 4 / 5
and so on for each city.Is there any option that this can be done within DB or It would need be some kind of macro to transfer this into other format?
View 1 Replies
View Related
Jul 28, 2014
My goal in Excel was to combine them all into a single Pivot Table which I did. The issue is everytime I add a sheet I need to redo the Pivot Table, which would be a hassle as I will continually be adding sheets. I'm hoping that there is an easier way this could be done using Access. The individual sheets are emailed to me and then I put them into one Excel file, is there a way to take that Excel file with all the sheets and import it to Access and be able to sort the data as I would with a Pivot Table in Excel?
View 4 Replies
View Related
Feb 1, 2005
Hello,
I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.
I don't know much about access but I was hoping one of you could point me in the right direction.
View 3 Replies
View Related
Oct 18, 2006
Is it possible to attach a WP, Excel, or Adobe file to a form, possibly by using a control key? How?
View 6 Replies
View Related
Dec 9, 2013
I am opening a new mail message in outlook using a button in a form.I want to change the "from" email address to a specific inbox (NOT personal email). Even though the email address is switched, after I hit sent and check my sent items - the email is actually sent from my personal email address and not from the inbox email address i wanted it to be sent from. I have access/permission to sent emails from the other inbox.
Is there a way to make sure emails are sent from specific email addresses and not ONLY personal inbox.What I read online is probably because of some "network" connection issues. Is there a way refresh connection between access and outlook? I am not even sure what the problem is.
View 1 Replies
View Related
Apr 4, 2013
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
View 1 Replies
View Related
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
