I am your typical office worker with intermediate Excel skills. I've just taken on the responsibility of tracking inventory for a small company where everything is being tracked through Excel in multiple workbooks and multiple formats and it's much too cumbersome with a large margin for error. I've never used a database program before and need to learn Access fast. I'm using the online help tool and "Access for Dummies". What I'm looking for is some advice on the order in which to perform tasks (tables, forms, etc.) that will help to avoid going back to re-do things that would have been created differently with some proper forethought. All records will be serial number driven and I will be tracking a serial number from the time it comes through the door to the time it's sold and shipped out. Various vendors require various reporting requirements/formats in Excel. Any advice and info on addtional resources for help would be deeply appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Beginner Access User Needs Advice 3
- Thread starter c21cwag
- Start date
-
1
- #2
A big task....
But with regards to what order to perform tasks.... TABLES must come first. Get your table design and relationships right from the off and it makes everything that follows much, much easier.
I recommend a high-tech approach of sitting down with a large sheet of paper and a pencil and mapping out all the entities, or nouns if you like, in your business scenario, e.g. customers, products, orders, etc. Under each noun heading make a list of all the characteristics you would assocaite with an instance of that entity. Then draw lines between entities to show how they join to one another (e.g. a customer relates to orders, orders relate to order lines, etc).
These nouns, their characteristics and join are the beginnings of your data model: tables, fields and relationship.
I know this is woolly... so better advice is to get a good book and work through some exxamples.
But with regards to what order to perform tasks.... TABLES must come first. Get your table design and relationships right from the off and it makes everything that follows much, much easier.
I recommend a high-tech approach of sitting down with a large sheet of paper and a pencil and mapping out all the entities, or nouns if you like, in your business scenario, e.g. customers, products, orders, etc. Under each noun heading make a list of all the characteristics you would assocaite with an instance of that entity. Then draw lines between entities to show how they join to one another (e.g. a customer relates to orders, orders relate to order lines, etc).
These nouns, their characteristics and join are the beginnings of your data model: tables, fields and relationship.
I know this is woolly... so better advice is to get a good book and work through some exxamples.
-
1
- #3
I agree - create everything with pencil and paper first.
I've taught over 4000 prople (that's my job) and maybe have meant one that actually learned the intricacies of Access by themselves, and that was over years of work. You cannot "learn Access fast". I highly suggest you take courses, possibly from your local Community College, or you'll be typing questions to these forums for the next few months.
A very important concept that Mp9 forgot to mention is that you must NORMALIZE your tables. Do not make the mistake that Excel Spreadsheets and Access Tables are the same. They look alike, and that's the problem I run into when I contract out to big business', so people "copy and paste" spreadsheets into tables. You Must Normalize. That's the very basis of a Relational Database.
You'll also will face many-to-many relationships. No Relational database likes these. You resolve them by creating a junction table.
Then there's the coding aspect. Do you use DAO or ADO? Can the queries be done with the interface or should you go straight to SQL?
They are quite a few more questions to think about.
If you open up the application Access, do not open a database, Click on File on the menu bar, click on New, you'll see a tab that has already built database templates. One is an inventory database. You just have to pick out your fields and type in your data and it's basically done for you. That might be a good place to look at.
Definitely get training.
I've taught over 4000 prople (that's my job) and maybe have meant one that actually learned the intricacies of Access by themselves, and that was over years of work. You cannot "learn Access fast". I highly suggest you take courses, possibly from your local Community College, or you'll be typing questions to these forums for the next few months.
A very important concept that Mp9 forgot to mention is that you must NORMALIZE your tables. Do not make the mistake that Excel Spreadsheets and Access Tables are the same. They look alike, and that's the problem I run into when I contract out to big business', so people "copy and paste" spreadsheets into tables. You Must Normalize. That's the very basis of a Relational Database.
You'll also will face many-to-many relationships. No Relational database likes these. You resolve them by creating a junction table.
Then there's the coding aspect. Do you use DAO or ADO? Can the queries be done with the interface or should you go straight to SQL?
They are quite a few more questions to think about.
If you open up the application Access, do not open a database, Click on File on the menu bar, click on New, you'll see a tab that has already built database templates. One is an inventory database. You just have to pick out your fields and type in your data and it's basically done for you. That might be a good place to look at.
Definitely get training.
-
1
- #4
The "trap" that I have observed in projects like this is that requirements are often stated as changes needed in the current system. The underlying mindset (even though it may be vigorously denied) is that the "new" system will be the current system with enhancements. If you do it right however, the "new" system will probably not be anything like the existing one because you are moving from an uncontrolled spreadsheet system to what should be a highly controlled RDBMS one.
For that reason, as mp9 says, start with the big, blank sheet of paper and define what the business is trying to accomplish without reference to what the current system does or does not do. From that derive the Nouns and Verbs of the system. "Nouns" are the things like stock items, Users, Orders, Sales, etc., and the "Verbs" are what happens like "Orders", "Sells", "Counts", etc. At this stage of the proceedings it is critical that you distinguish between what you want to accomplish and how you will actually make it happen.
[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
For that reason, as mp9 says, start with the big, blank sheet of paper and define what the business is trying to accomplish without reference to what the current system does or does not do. From that derive the Nouns and Verbs of the system. "Nouns" are the things like stock items, Users, Orders, Sales, etc., and the "Verbs" are what happens like "Orders", "Sells", "Counts", etc. At this stage of the proceedings it is critical that you distinguish between what you want to accomplish and how you will actually make it happen.
[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
