1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 project. Don't know how to structure this thing.

Discussion in 'Business Applications' started by Masta Squidge, Jan 26, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Masta Squidge

    Masta Squidge Thread Starter

    Joined:
    Jul 29, 2007
    Messages:
    707
    Hello again everyone. I apologize in advance for the long post, my primary question starts and ends at the bold font way down there if you wanna skip to it.

    I am going to start working on a way to combine all of our half a dozen excel sheets and separate, inconsistently named customer lists into one single file. My first idea is to use access as I have (extremely limited) experience with it. What I need to figure out is exactly HOW I am going to combine these excel sheets into one file.

    The idea here is that I need to be able to track; Customer name/address, what we pick up or deliver to them, and individual records for every stop we do at that customer.
    As it stands right now we have 6-7 different sheets that cover the various services we provide. Each one however is listed like vertically:

    Customer Name/Address
    *Price code(s)*
    Date of pickup/amount picked up

    As an example we might have something that looks like this:

    Company X
    CODE1234
    01.01.11 125
    01.02.11 250
    01.03.11 120
    (insert any number of pickups here)

    So on and so forth with each different customer starting immediately below the last pickup. New pickups are entered by inserting a row, thus shifting everything else down. This results in many hundreds, on some lists a couple thousand rows.
    The only way they are kept in order at the moment is that they are in alphabetical order. This is the way the previous manager did things in Excel. Needless to say it does not do us very good when attempting to obtain various bits of relevant information that is otherwise not explicitly stated in these forms.

    As for my question, I am not by any means skilled with access. The only way I can think to organize this information would to be to have a separate table for each customer. However we have close to a thousand customers in total, and I am not aware of the limits of access. If multiple tables, then how do you link them all to one customer table?

    Or, is there a way to list all of this data in one table while still being able to add any number of pickups? It would probably be close to 10 thousand rows at the end of a year's worth of pickups but I can't see that being too huge of a problem for a single user.

    I guess my question right now is more about the physical structure of the thing than anything technical right now.

    But as an aside, is there a way to generate a table with all relevant table links in place so that I can simple enter a customer name, address and pricing then press a button to generate a new table? I realize that is far more complicated but it is something that will have to eventually be done. Either using visual basic (which I am also slightly familiar with) or a form in access. I don't need specifics right now, but more of a yes or no on that.

    Ideally what I want to do in the end is write a small front end program in VB that provides a way to enter pickups to the correct table, make changes to customer information and enter new customer.

    The good news is that it will only ever be opened by one user at a time, on a reasonably quick computer so I am not too worried about it being overworked by a large network of employees as some people like to stress.

    The important thing here is that i have something that a person who is hardly even able to find keys on a keyboard is able to enter new customers or make changes when all is said and done. But also doesn't cost a ton of money.

    It doesnt need to be particularly secure, efficient or powerful. Just that all of the information is in one place and we can generate queries against all of it at once.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You need to plan your database very carefully as far as table structure and what data needs to be kept in what tables. Next you need to notmalize the data and not lump all data into a single table. For instance, you have customers with a given set of information...company name, address, phone number, etc. You can normalize some of that data in the company table because some of it will relate to other customers like the state field. You would create a static table of states that could be used for all of your customers and a reference can be set for each customer based off of a value in the state table. See where this is going? Then you have an orders table, since a single customer can have many orders you want to not lump this info into a customer table, it needs it's own table that creates a new entry for every new order and has a reference to the customer. Planning is key to getting a good functional database and the problem with using spreadsheet is that spreadsheets are not by definition normalized at all so importing that old data becomes problematic at best. Look up some online tutorials for creating and normalizing databases. There is far too much to learn to put it all down here.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Masta Squidge, as Rockin says a single table is not a good idea and Normalised data works much better to avoid duplication of data.
    I can create the database or help you create the database so that it works efficiently.
    I would need to know exactly what data you have now, whether you nedd any more and what you Need or would Like it to do. I can even suggest a few things.
    I have worked on a couple of Logistics databases before so I have a rough idea.

    By the Way Access is designed for multiple users (unlike Excel) so you if in the future you want more than one person working on it that is not a problem.
     
  4. Masta Squidge

    Masta Squidge Thread Starter

    Joined:
    Jul 29, 2007
    Messages:
    707
    Rockn... I dont know why that never occurred to me. I did something exactly like that in my access course.

    Derp.... brain fart.

    Our manifest sheets have unique numbers already. I guess ill get to work on putting an example together later on.

    As far as importing data... well lets just say that you are right about things not being normalized. Three different sheets with the same customer name on them arent all teh same thing.

    As an example, one place might be called "Truck Corp" and one sheet might say truck corp, another truck corporation, or another might say truck service. The guy who set this up thought it was a simple and effective way of doing it, and for what he used it for it was. But he was not consistent or accurate by any means.

    Well now we need more from the lists.

    So I put Customer info on one table. Assign each customer a number (arbitrary, but i have that in place already) because of similarly named customers. Then the second sheet would simply have the customer number, the manifest number and the amounts for each item for every entry.

    So then I could simply set up a form with a drop down menu or something of that nature where I can pick one customer from a list and have it display all of our pickups from that customer arranged by date, correct?

    Thats a relief lol I really spent alot of time just thinking about it as I do other things as to how to arrange it. The first thing I learned in my class was the one thing I totally forgot about.

    There is no reason i shouldnt be able to simply set up a form for entering new pickups/customers/making changes right?

    Im not entirely clear on how data entry actually works when using forms. The class i took was only a generalized type of "this is how it works" combined with a generalized visual basic thing. So not a ton of focus was given to details of both things.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The answer to your 3 questions is Yes, yes and yes.
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    To clean up the data in spreadsheets is fairly easy using find & replace althogh it is time consuming.

    Assigning customer numbers is easy if you use the autonumber as a primary key, but if you have already assigned customer numbers you can use them as a primary key to keep those number unique and allow for reference to data in other tables.

    Forms are a great way to keep end users from only entering the data you want them to enter and it is entered in a consistent manner.

    Have OBP give you a hand since he is more than willing and capable to help you out.
     
  7. Masta Squidge

    Masta Squidge Thread Starter

    Joined:
    Jul 29, 2007
    Messages:
    707
    The vast majority of stuff involving forms I can easily find online. I am familiar with them just not fluent. That and I don't like to bother people I am not paying for the help any more than I have to you know? Just kind of how I am. Though I do greatly appreciate the offer and will keep it in mind.

    Plus this is something I am more or less doing on my own time, so it may take a while.

    Thanks a ton for the fast responses. That was really all I needed to get sorted just to start out with. I am sure at some point I will be back here trying to solve some code related problem or something well above my head.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977115

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice