Design Question - Duplicating Entries Anyway

[Kiley]

I am very new to Access, we are using Windows 7 64-bit with Office 2013 32-bit. We currently use Excel spreadsheets to track new business coming into the agency and business we've lost in excel. This has led to very poor reporting systems and so I am trying to build a whole new system in Access to use instead of Excel. This will allow for so much more control over what is put into the database and also, how it is put in, and to ensure that it IS actually put in... That being said, we have about 80 users in and out of this on a daily basis, and I am trying to do my best to cause the least amount of shock as possible moving from Excel to Access. Nobody here knows how to use Access, and so is it extremely unfamiliar territory, so I wanted to try and use the speadsheet form in Access to make it look as close to Excel as possible. I found though, using this style of form, that I am very limited on what buttons I can actually place visibly on the form, and that I cannot add a new customer from that form. Does anybody know if it is even possible to add a new customer directly from a form to your customer table, or do I need to do a 'customer search' form and if no customer is found, then have an 'add customer' form, before they can even enter the rest of the information that would normally follow on the excel row they'd be working on. Obviously I would want a customer table so as not to duplicate customer names, but the only way I have found so far to make it as quick of a workflow as possible is to not worry about duplicating names and just have that be a text field rather than setting up a table. Thoughts? Any insight is greatly appreciated. I have seen everywhere that duplicate information in Access is bad database design, and I don't want that, but I also don't want my employees to feel like it takes 10 more clicks to do something than if they had been using excel and resent having to use this new system.

[ranman256]

you need to move away from the excel 'look'. it wont always work for access.
your users don't need to know access if you build the forms. I have a startup main menu, users click clients.
a continuous form of clients show. edit existing, or add new buttons
edit brings up a single form of client detail, with its subform for entries.

don't let users run wild. give them point and click options only.
forcing them to enter data where you want them to.

[Kiley]

Thank you for the input. I had a feeling that I wasn't going to be able to get away with maintaining an Excel-style visually if I truly wanted the system to function for reporting the way we need it to. Did you by chance do an excel to access change-over? If so, did your employees/users take to the point and click vs spreadsheet style without too much issue (or complaining lol)?

[John_sc]

You may want to also look at adding a switch board. It will help isolate users from the complexities of Access, and prevent them from getting to areas that they should not be in (like the actually tables). You can restrict them to input forms you create, and when they hit submit the data is placed directly into the proper tables. You can also give them access to reports that you create, but lock down everything else. If you are not familiar with Access, I would suggest getting a reference book at a bookstore or library. It will be a valuable reference for you.

[Kiley]

Yes, I did think about that and am planning on locking everything up that I can. I thought it was really neat to be able to hide a lot of the navigation buttons and scroll bars and create your own and hide the nav table on the left that houses the forms/tables/etc. So I will definitely be doing that so that nobody can alter the information actually on the tables or delete anything or mess up forms/queries I've built, another reason I am really excited to switch this over to Access vs Excel I think the hardest part will just end up being the end-users getting used to a database style of records vs the spreadsheet input records they have been doing since the beginning of time. Not being able to look at a "June" tab and see all of the entries put into the system in June will take some getting used to. I know I can make a report for that, but it'll still take them some getting used to the concept.

[orange]

Access is NOT an advanced Excel. These are 2 different software products, based on different underlying models. You may have issues with Access if you have strong Excel skills. Unlearning some spreadsheet concepts could prolong adoption of Access/database.
See the links I posted in this thread for more info.

Good luck.

[Kiley]

That is a perfect way to describe the challenge ahead of me/us... unlearning spreadsheet concepts. It's definitely going to be a learning journey.

[orange]

For a quick, but great tutorial on database concepts through doing, here is a tutorial from RogersAccessLibrary. It will take 45 min to 1 hour. You have to work through it, but you will learn the basics and what you learn can be used with any database. It contains the Business narrative, Problem definition, Process to follow to get to a solution, Problem solution and the Hernandez method.

Work through it and post back any issues/questions.
Good luck.

[Kiley]

Thanks a lot! I read one of the articles and a linked blog from your last comment and it really does help give clarification on where to start, mentally. I was one of those people that thought I could just import our excel documents into Access and just go with it. But there is so much more learning, planning and analyzing to do before I even begin to build anything. I will definitely be reading through that. Thanks so much for all of your help and guidance!