Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New to Access - Dive Shop Database

    We are trying to set up a database for all our customers. We think three tables - Contacts, Equipment Info and Qualifications. I have copied the template contacts three times and renamed them to give me my three basic datafiles but do not see how to create the forms associated with them. Also how do I link the three tables in the best way?

    Thanks in advance and sorry for the simple questions but I have no book and the tutorials are a bit thin.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you building a database for a shop, and you have very little experience with Access then you have a big job in front of you. I strongly suggest you get a good book, or do a course in Access or find some on-line tutorials.

    First you need to be sure you have the design of the tables correct. To provide any help with this we need to understand what you want the database to do, and for that we need to understand what the Dive Shop does. ( we could probably make a guess at that but our guesses might be wildly wrong.)
    So tell us what data you want to store in the database, what type of transactions you need to process (if any) and what types of output you need to produce. And what fields do you have in each of the 3 tables you have so far?

    Questions about creating forms, and linking the tables can come later.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jul 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dive shop database

    Thanks John.

    We are looking to record all details of those working in the shop and visitors. I have set up five tables - contact, qualifications, equipment, current visit, dives this visit.

    The first three remain static with the information being entered once and rarely changed. The current visit table has details like arrival date, leaving date, hotel etc. The dives this visit should be more like an excel table with the date, the dive site, the cost of the days diving and a running total for this visit.

    All the tables should start with an ID number, first name and last name. I would like that when the ID number and names are entered in the first table they are automatically entered in the other talble. I have linked the four subtables with the contact table over the ID number but have to entered all the details separately in the 5 tables.

    The fields so far are:

    Contacts = ID, last name, first name, email, home phone, cell phone, address, city, post code, country, notes, dive shop contact person, attachments.
    Qualifications = ID, last name, first name, dive association, highest qualification, certification number, other qualifications, course being done (Yes/No), Course, last dive, total dives, notes, attachment.
    Equipment = ID, last name, first name, own equipment (Yes/No), own mask (Yes/No), adaptor (Yes/No), BCD, Wetsuit, Fins, Booties, Weight System, Weight Pieces, Notes, Attachment.
    Dives this Visit = ID, last name, first name, arrival date, departure date, Hotel, Room Number, Notes, Attachments
    Current Visit = ID, last name, first name, date, dive site, dive cost, running total

    I have worked out how to enter lists etc but with the last table I am lost.

    Thanks again.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    There are a couple of principles to remember in designing these tables.
    • do not store the same pice of information more than once
    • do not store anything that can be calculated (or worked out) from other data that is stored.

    So the only place you store the name of a customer is in the Contacts (customers table) This table has a Key field (customerID) that identifies a person.
    If any other tables want to say that something was done by/belonged to etc a customer, you included only the CustomerID in that table, not the customer name (this is referred to as a Foreign Key).

    Total dives and last dive can be worked out from the Dives and Visits tables, so should not be fields in the table.

    Tables are your raw storage of data. They are not the way the data is viewed. Once the correct table structure is designed, a later step is to organise to see what you want where you want it.

    tblCustomers = CustomerID, last name, first name, email, home phone, cell phone, address, city, post code, country, notes, dive shop contact person, attachments. (is Gender or DOB important info in your business?)
    tblQualifications = QualificationID, CustomerID, dive association, highest qualification, certification number, other qualifications, course being done (Yes/No), Course, notes, attachment.
    tblEquipment = EquipmentID, CustomerID, own equipment (Yes/No), own mask (Yes/No), adaptor (Yes/No), BCD, Wetsuit, Fins, Booties, Weight System, Weight Pieces, Notes, Attachment.
    tbldives = DiveID, CustomerID, arrival date, departure date, Hotel, Room Number, Notes, Attachments
    tblVisits = VisitID,CustomerID , date, dive site, dive cost, running total


    The reason for having Qualifications in a separate table for Customers is to allow for multiple qualifications to be entered for one customer. with the field you have, it is not clear that you intend to do that. You may be able to just add the Qualification fields to the customer table, and drop that table.

    I am not sure about the Equipment table. If it is just a static record of the equipment for a customer, the fields can be added to the customer table. If you want multiple records for each customer, then it looks to me that the records should be linked to a specific dive. I don't understand the current setup.

    I don't understand the last two tables- visits and dives. Is the idea that (over time) a customer will make multiple visits, and on each visit they will undertake a number of dives? If so many of the fields seem to be in the wrong table. But I just don't understand these two tables. If my explanation was correct, the Dives table would not have a customerID field..instead it would have a visitID field.

    RunningTotal does not sound like something you should have in a table. It be should be calculated somehow, but just how depends on just what it is.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jul 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Questions

    Hi John
    Thanks for all the help.
    A word to my thinking.
    1. The Contacts data is only used before the person arrives here in Thailand to go diving and for correspondence thereafter.
    2. The Equipment data lists the sizes of the various bits of equipment we lend the diver each day to go diving and shows whether he has any of his own equipment which we would store in the shop and put out for him each day. Therefore I was planning to put in drop down lists of the various sizes.
    3. The Qualifications data shows his level achieved before coming to visit us. It shows his highest level and all other qualifications. The specialities can be important as they let him dive on enriched air or deep etc. The last dive refers to the last date on which he went scuba diving as this tells us whether he is out of practice or not. If it is a long time since his last dive we would go into the water with him and review his skills. The number of dives is the total number of times he has been under water as this is a good indication of the level of competence and skill he will show under water. These figures will be changed each time he comes to visit us, but only then.
    4. The Current Visit data is only entered once each time he comes to visit us but should be static during any one visit.
    5. The Dives this Visit was seen by me as a way of tracking the dives he does with us on any one visit and the running cost. Each day he may dive one or more times with us and we record the date, the dive site together with the maximum depth and time spent under water. From this we can calculate what he owes us. The price depends on the dive site, whether we go by boat, whether he hires equipment, whether he does a course, how many dives as discounts are given after a certain number. This table would fill up during a visit which can last months and then at the end of his stay with us and after payment be printed out and cleared ready for the next visit.

    Accepting what you say, that I should only put the names in the contacts table and then use the Diver ID for the link to the other table. How do I make the names appear in all the other tables as we will use them (both first and family) to identify who we are referring to when we look at the other tables. Clearly the Equipment table will be looked at every day we go diving and the Dives this Visit table filled out after every day's diving. The others only occasionally.
    You are right DOB is important and I will add it to the Contacts table. Gender not so and can be implied from the name.
    Read your comments yet again. Can I design Forms with information from various different tables?
    Is there something special about an ID field? If so how to I create it?
    Would probably print out the Equipment table for use by the people setting up the equipment before each days' diving, so need the names on the printed sheet.
    The reason for the five tables was that I found the form very crowded when I tried at the beginning to do it all in one big table and as said the information is used with different frequencies by different people.
    Sorry to bombard you with this but your help is greatly appreciated. I have been doing some online tutorials but they don't seem to answer my queries.
    Chris

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Since it is very early morning in John's location, I'll chime in with a few thoughts on general design.
    • The trick with doing forms and reports and showing the customer name on them even though it is only stored in one place is to use queries which join the Customer table with the other tables you are displaying information on the form or report. Queries are a fairly complex subject when you get into them, but the Query Design tools let you do them visually to start with and make them easier to understand.
    • ID fields are typically number fields, more often than not Long Integers, and where the ID is the Primary key it is often an AutoNumber field. If it is a Foreign key that is use to point to a record in another table, then it must have the same characteristics as the Primary key in the table being pointed to, and would not be an AutoNumber.
    • The other comment is on the clearing of the "Dives this Visit" - why not simply relate that set of records in a "Dives" table to the Current Visit record so you have a permanent record of what the person has done?
    Hope this helps.
    Wendell

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Tables are the "behind the scenes" data store. They are designed in a way to be computer friendly rather than user friendly. Users deal with the data via forms and reports, so a lot of work is needed with them to transform the appearance of the data into something user friendly.
    Three techniques are used to hide the ID fields and show names instead.
    • Queries (as Wendell said)
    • Combo boxes
    • Forms and Subforms

    I won't go into how to do these things yet.

    The first priority is to get the table structure correct, and I don't think we are there yet. Getting it right is hard work. Doing it face-to-face involves hours of probing and questioning, and (usually) several iterations.

    Just because you enter data at different times does not mean that it cannot go into the same table.

    It sounds to me that (broadly) the structure should be Contacts...Visits..Dives. or maybe Contacts...Visits..Days...Dives?
    A person makes one or more Visits, and each Visit includes one or more Dives. or
    A person makes one or more Visits, and each Visit includes one or more Days and each Day includes one or more Dives.

    Qualifications: When someone arrives you enter data. If they come back another time do you enter a whole new lot, or just update the previously entered data? In either case I don't think you need a qualifications table. You either add the qualifications fields to the Contacts table or the Visits table.

    Equipment Is this done for the day or for the dive? or are they really the same thing? Again these fields can be added to the relevant table, rather than using a separate table.

    The idea of providing drop down lists (called Combo boxes) is a good one. It is normal to create additional tables to hold the values you want to appear in these lists.
    Regards
    John



  8. #8
    New Lounger
    Join Date
    Jul 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John, hi Wendell
    Took your advice and tried the Query Wizard for the First & Family names but only ended up with a list of all the names rather than what I wanted which was on entering the Contact ID number to get the two names automatically in the new other table (Equipment etc.).

    The Qualifications table is filled out once, when we first make contact with the diver either by email or when they come into the shop. Thereafter the information remains static with occasional updates as someone progresses slowly through the different aspects of diving. Lots of people are just happy diving and never go any further in terms of Qualifications, although they dive a lot and clearly gain experience.

    Equipment is merely a record for us. So that when the diver arrives at the shop in the morning for a day's diving the right sizes of the various bits he needs are already laid out. For that day and the days thereafter the information is essentially static. As such the information is rather like qualifications in that once in it changes little.

    It is the responsibility of each diver to keep a log of the number of dives he has done, the place, depth etc. For us the interest is only for the current visit. Diver A comes to us with 40 dives and the last dive in April 2011. We would record these two facts in the Qualifications sheet.

    If he then stays and does 20 dives with us over a period of three weeks we will record the date of each dive, the dive spot, the depth, the time under water on that dive and the cost of that day's diving and the running total. On Diver A's departure date we print a record for him and an invoice based on the 20 dives done. After that it may be two years before we see Diver A again and when he returns he may have dived in many other places so that we would have to update the total number of dives and last dive date in the Qualifications table. But the information about the 20 dives he did with us over the three weeks is of no value to us once he leaves so I planned to get rid of it after printing a hard copy for the bookkeeping.

    What are your thoughts on reading the above?

    Sad to say that I am off tomorrow to Indonesia to go diving from a Liveaboard so although I will be able to read your comments I will not be able to reply or make any more progress until the end of July. Thanks very much for the help and I hope I can come back to you when I get back.

    Chris - Gone Diving.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •