Using Access to record reloading data.

[jimbo88mm]

I just uploaded a video to youtube showing how I use Access to keep track of my reload data. I remember seeing a couple of threads on organizing data in the past and figured some might like to see this.

[youtube]http://www.youtube.com/watch?v=nQBkXvePsaQ&feature=plcp[/youtube]

I'd be interested in knowing what you guys think.

 

[seanhagerty]

Upload the file and we can look through it.

 

[Beau]

I use Access too. There is nothing like a DB for finding hidden relationships.

I really need to make a better way to track barrels. I should probably change my velocity table to be based on individual barrels instead of upon individual guns.

 

[Outrider27]

Since I have Access, the question is somewhat academic, but why an Access database instead of a spreadsheet setup, like Excel? One caveat: I'm not a big fan of MS Access, as personnel experience has shown it to be a bit of a minefield for the unwary.

 

[jimbo88mm]

Since I have Access, the question is somewhat academic, but why an Access database instead of a spreadsheet setup, like Excel? One caveat: I'm not a big fan of MS Access, as personnel experience has shown it to be a bit of a minefield for the unwary.

I addressed this a little in the video

This started out as a project I was running in parallel with a class on Access I was taking at a local community college (The database track went Excel>Access>Oracle 9i). It was a way to take what I was learning and apply it to something more interesting then the topics being covered in the class.

I kept using the database because it turned out to be very practical.

In comparison to a spreadsheet like Excel, I get two advantages:
1) Establish relationships between different tables
2) Easily create and run queries

While you can achieve similar goals in excel with lookups and pivot tables, the database works better IMO.

I originally planned to take this further and add forms and reports but never got there.. maybe one day I'll get that part done

 

[jimbo88mm]

Upload the file and we can look through it.

Lets see if this works..

This file is for Access 2007 so you'll need 2007 or later to open it.

The database was too large to attach to the forum so I zipped it up.
The forum then didnt seem to like the zip file so I changed the extension to .txt.

In order to use this file you will need to first rename the extension from .txt to .zip. Then unzip it.

If you are running windows7 or Vista and can't see the extension (i.e. .txt or .zip) then you need to go into folder options and uncheck the hide extension checkbox on the view tab.

 

[jimbo88mm]

Here is a copy of the same DB only saved as a Access 2002/2003 format so anyone running an older version should be able to open this. Same steps to open apply as previously posted.

 

[Outrider27]

Thanks for the save for "older" versions. The first time I ran into Mocrosoft's version of planned obsoleasance was when the beta for a $70+K programming project was dumped on me to test and the first thing that I noticed as a problem was that it wouldn't open . That was because this was 2006, and I was still using Access 97, which came with my MS Office 97 Pro package. So I had an unexpected business expense - buy Access 2003. It appears that whenever MS feels they're running a little behind revenue projections, they pick one of their programs and change the file system so that you have to go out and spend $150-$300 on new software because the new one isn't backward compatible.

I'll take a look at it soon; it will be nice to look at an MS Access application that actually works out of the box - that will be a new experience for me, lol.

 

[Beau]

It's real easy to permanently fuxor your data in excel too. You are only one sort and one save away from useless data.

 

[jimbo88mm]

I'll take a look at it soon; it will be nice to look at an MS Access application that actually works out of the box - that will be a new experience for me, lol.

Doh.. the pressure is on, lol

I think you'll find this one to be a pretty amateurish attempt. I had planned to build out forms and maybe a report or two but never got around to it.

It's real easy to permanently fuxor your data in excel too. You are only one sort and one save away from useless data.

Yep.. it doesn't take much does it. I'm always making extra copies.

 

[Steve Blair]

It's real easy to permanently fuxor your data in excel too. You are only one sort and one save away from useless data.

[br]
I have a 37 Mb Excel spreadsheet that started in Lotus 1-2-3, migrated to Quattro, then to Excel through many versions. It has never once corrupted. It includes external references, VBA code and many pages of calculations relating to load statistics. Nonetheless, it is backed up every night in versions and any nightly backup can be retrieved over the previous six months. While a Unix sysadmin and IT Manager at a major defense contractor, I do not recall a user asking for recovery of an Excel spreadsheet. Many Word docs, but not Excel. [br]
What we did have problems with were clueless users and Access. Here is the typical life cycle of a user-generated Access database. [br]
1. The user lies to IT and claims that it is for: (pick one) one-time use, a small group of people, the data doesn't matter if it is lost, etc.
2. Said user constructs the "database" using Access' default Jet engine. They call it "Jet" because it will eventually run out of gas and crash.
3. Said user cannot distinguish Codd from cod, couldn't identify Fourth Normal Form if it would save his/her soul from hell, and has no knowledge of relational database theory and practice.
4. Having been built on quicksand, this thing slowly smolders until the day it crashes and burns. By now, it has, of course, become mission critical.
5. The steaming pile is dumped in IT's lap and told to "make it work." [br]
The above is why IT professionals do not like Access and prevent its use when they can. Access can be made more stable if SQL Server Express is used as the database engine and Access is used as the development tool and front end. SQL Server Express is available free from Microsoft at the link below. [br]
http://www.microsoft.com/en-us/download/details.aspx?id=29062 [br]
If you do not understand any of the above, you should not be mucking about with a database.

 

[Nomad47]

That must be why all my load data is in excel.

 

[Beau]

Like any tool, you have to know how to use it.

When I was in IT, I did have people ask me to fix their Excel spreadsheets. Most of the time they couldn't tell me when the results actually became false because they didn't know when they screwed it up. You'd go back so far and eventually get to the point where they said "F. it" because the volume of data they had to re-enter was too much. Then we'd move the "app" to Oracle or Access if it was small.

Most of what we are talking about here is real small. My DB has been in use for ~10 years and still has less than 2,000 records in the deepest table and it doesn't require any automation beyond manually run queries. I worked with one company porting a bunch of 500+ megabyte Access '98 DBs over to SQL Server. That was needed!

When you develop an app in Access, you put data in one .mdb and app layer stuff in another. When you hit ~1/2 million records, then you consider SQL Server on the back end, depending upon the performance of the queries you are running.

Of course, even if you are running Oracle you back up on a regular basis. It's just part of IT. But I've never had to recover my .mdb from backup.

Normal Form isn't hard to learn. You just have to keep it in mind when you are building tables. And if you can't figure out how to write queries for things like Min, Max, ES & SD, it's easy enough to dump into Excel.

 

[jareese]

I just came across this, this is absolutely PERFECT! Thank you, thank you, thank you! I was looking at making one myself, but then I saw this, and viola!

 

[DRNewcomb]

I could not load the database in my ancient Access 2000. It seems to load in Libre Office but right now the program is hung up.

 

[memilanuk]

If a person is looking to set up their own, and doesn't want to pony up the $$$ for M$ Access, another option might be LibreOffice Base. For a simple example like used in this thread, it should be pretty easy to setup. If you want to get into more complicated schema and/or use some forms to pretty things up, check out this video tutorial series on YouTube.

The stock/original backend 'lite' DB for LibreOffice Base (hsqldb) isn't much better than the one that comes with Access (Jet), so you may want to consider a split setup with a dedicated DB program, whether SQL Server Express (free for personal use) or an open source option like MySQL or PostgreSQL (also free).