If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
looking for direction... Access or Excel
Very much a newb with access and have the need to improve upon a db created
by another that has grown in popularity by the big guy (boss)... looking at it i thought excel was a better choice but seeing the value of queries i am rethinknig this. I am an intern going through a career change and have a real interest in leaving something valuable behind or make my self look like a real bargain and worth a full time gig. the issue is storage of test tube vials in a freezer. There are 26 totes A thru Z, each tote hold 6 boxes, and each box holds 81 vials. Rows A thru I and col 1 thru 9. The details of the vials is critical and we need the ability to search for a particular batch, lot and process. Excel made sense to me since there can be only one vial in one spot and once the vial is pulled the space is empty and ready to be refilled when needed. Setting up the fixed box positions its 12,636 positions. The other beauty of excel is that the vials go in as batches of 8 and we can add as many as five or six batches in a day depending on volume. So it makes sense to use the pull down and copy and paste features on the spread sheet. The big problem is searching for the specific sets of vials and not just a sheet of vials to look at. Laying out the sheet and page set up its 350 plus sheets of paper to veiw the entire inventory. Not practical on a regular basis. Now the access part. It's already in use and the big guy likes it because he can search what he needs us to pull and then he adds empty in the key lot field and leaves it for done. My concern is the total number if key figures is now up to 14,500 plus and will continue to grow. When he adds " empty" that satisfies the no duplicate rule but it concerns me the overall db will grow huge and lend itself to corruption down the road. i have created a better form for data entry with pull down lists to avoid bad data entry and to make it faster than keystroking all the data... select and tab works well on my form and i have it down to 10 steps from 15 entries. i hope that gives you an idea of the big picture... should i make a querry that deleted the data once he pulls the report which he then gives us so we can pull the vials or make him another querry to create a table to delete the results once per month? or scrap it all and go to excel and work with it? i was able to dump the data file to excel and back to Access so that is one less thing to worry about but i don't know where to spend the time? any suggestions are appreciated. -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
looking for direction... Access or Excel
As a rule of thumb, it you are mostly doing the math, Excel is the tool. If
you are storing and retrieving data, you want a database. Sounds like you are doing data. You are worried about 14,500 and growing. If that is the number of rows in a spreadsheet, you are much closer to having problems with Excel. Some very older versions of Excel could only handle 16K rows in a spreadsheet. Excel 2003 is up to 64K while Excel 2007 can handle a million rows. In constrast, Access can handle many millions of records in a table. Also if you get many rows in Excel, searching can start taking a lot of time. Whereas a properly normalized and indexed database can return data quickly using queries, forms, and reports. As far as deleting the data once a month, this is usually a bad idea. What if you discover a problem with the report after the data is deleted? What if you need to compare and contrast various months? Access, with the ability to hold multiple records, may be a better choice. The size of a database has little to do with corruption. Also a good database administrator should do frequent backups to other media. If a hard drive crashes, it doesn't matter if the lost file was a .mdb or .xls. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "jorgesalazar via AccessMonster.com" wrote: Very much a newb with access and have the need to improve upon a db created by another that has grown in popularity by the big guy (boss)... looking at it i thought excel was a better choice but seeing the value of queries i am rethinknig this. I am an intern going through a career change and have a real interest in leaving something valuable behind or make my self look like a real bargain and worth a full time gig. the issue is storage of test tube vials in a freezer. There are 26 totes A thru Z, each tote hold 6 boxes, and each box holds 81 vials. Rows A thru I and col 1 thru 9. The details of the vials is critical and we need the ability to search for a particular batch, lot and process. Excel made sense to me since there can be only one vial in one spot and once the vial is pulled the space is empty and ready to be refilled when needed. Setting up the fixed box positions its 12,636 positions. The other beauty of excel is that the vials go in as batches of 8 and we can add as many as five or six batches in a day depending on volume. So it makes sense to use the pull down and copy and paste features on the spread sheet. The big problem is searching for the specific sets of vials and not just a sheet of vials to look at. Laying out the sheet and page set up its 350 plus sheets of paper to veiw the entire inventory. Not practical on a regular basis. Now the access part. It's already in use and the big guy likes it because he can search what he needs us to pull and then he adds empty in the key lot field and leaves it for done. My concern is the total number if key figures is now up to 14,500 plus and will continue to grow. When he adds " empty" that satisfies the no duplicate rule but it concerns me the overall db will grow huge and lend itself to corruption down the road. i have created a better form for data entry with pull down lists to avoid bad data entry and to make it faster than keystroking all the data... select and tab works well on my form and i have it down to 10 steps from 15 entries. i hope that gives you an idea of the big picture... should i make a querry that deleted the data once he pulls the report which he then gives us so we can pull the vials or make him another querry to create a table to delete the results once per month? or scrap it all and go to excel and work with it? i was able to dump the data file to excel and back to Access so that is one less thing to worry about but i don't know where to spend the time? any suggestions are appreciated. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
looking for direction... Access or Excel
What Jerry said.
In addition, don't be trapped into "I know Excel, so I will go with it". You are, after all, trying to please the boss and stretch your horizons. The end result for everything you want will be better and you will have added to your skill set. If you need help as you go forward, this is the best place to get it. -- Dave Hargis, Microsoft Access MVP "jorgesalazar via AccessMonster.com" wrote: Very much a newb with access and have the need to improve upon a db created by another that has grown in popularity by the big guy (boss)... looking at it i thought excel was a better choice but seeing the value of queries i am rethinknig this. I am an intern going through a career change and have a real interest in leaving something valuable behind or make my self look like a real bargain and worth a full time gig. the issue is storage of test tube vials in a freezer. There are 26 totes A thru Z, each tote hold 6 boxes, and each box holds 81 vials. Rows A thru I and col 1 thru 9. The details of the vials is critical and we need the ability to search for a particular batch, lot and process. Excel made sense to me since there can be only one vial in one spot and once the vial is pulled the space is empty and ready to be refilled when needed. Setting up the fixed box positions its 12,636 positions. The other beauty of excel is that the vials go in as batches of 8 and we can add as many as five or six batches in a day depending on volume. So it makes sense to use the pull down and copy and paste features on the spread sheet. The big problem is searching for the specific sets of vials and not just a sheet of vials to look at. Laying out the sheet and page set up its 350 plus sheets of paper to veiw the entire inventory. Not practical on a regular basis. Now the access part. It's already in use and the big guy likes it because he can search what he needs us to pull and then he adds empty in the key lot field and leaves it for done. My concern is the total number if key figures is now up to 14,500 plus and will continue to grow. When he adds " empty" that satisfies the no duplicate rule but it concerns me the overall db will grow huge and lend itself to corruption down the road. i have created a better form for data entry with pull down lists to avoid bad data entry and to make it faster than keystroking all the data... select and tab works well on my form and i have it down to 10 steps from 15 entries. i hope that gives you an idea of the big picture... should i make a querry that deleted the data once he pulls the report which he then gives us so we can pull the vials or make him another querry to create a table to delete the results once per month? or scrap it all and go to excel and work with it? i was able to dump the data file to excel and back to Access so that is one less thing to worry about but i don't know where to spend the time? any suggestions are appreciated. -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
looking for direction... Access or Excel
Thanks for the feedback. I should have included that the software is all MS
2007 and that the back up issue has been addressed by the big guy. He trusts no one with it not even the IT guys...lol I spent the part of the day filtering with excel and seeing some limitations I spent the other part playing with querries and can really see the power. If leaving the key count grow means nothing bad provided the critical data is out there should never be a duplicate or a confusion as to what is in the actual spot even if its been used 20 times. Then this seems to be the way to go... access it is... now i created a split form to enter data, next project is to see how to copy that form and the tables that feed the lists into the old database... stay tuned... i may be back with some how to's on that... thanks again! Klatuu wrote: What Jerry said. In addition, don't be trapped into "I know Excel, so I will go with it". You are, after all, trying to please the boss and stretch your horizons. The end result for everything you want will be better and you will have added to your skill set. If you need help as you go forward, this is the best place to get it. Very much a newb with access and have the need to improve upon a db created by another that has grown in popularity by the big guy (boss)... looking at [quoted text clipped - 35 lines] less thing to worry about but i don't know where to spend the time? any suggestions are appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200907/1 |
Thread Tools | |
Display Modes | |
|
|