Results 1 to 11 of 11
  1. #1
    GolfNut39 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    5

    Sports Gear / Stick / Player Inventory

    What a great forum. I will post this to see if I can get a little advice as been watching a million Lynda.com tutorials but its not sinking in.

    I have made this all in excel but I really want to learn access as I think it is way better for what I need. I will watch tutorials planning to construction again but see most of you are the experts and could maybe steer me in the right direction.
    I think I need the following tables as per the attachment I think?
    1. Player info: Name, contact info, what size gear they want and stick preference.
    2. Specific stick data, how many we have now and how many we need to order, etc leaving space for new players.


    3. What type of gear we have in stock, required, delta, how many to order.

    Reports:
    1. Numbers and Name of players in each stick flex / curve / hand (helps to apply decals to stick with name so are ready to hand out ahead of time)
    2. Number or LH, RH or goalie players
    3. How much worth of $ we have in stock and on order of everything.
    4. A place to write down date when a player breaks a stick and gets a new one to track balances left of each stick flex / curve / hand (sample of my excel file also attached)
    5. A page with each player with photo they can sign to accept all gear sizes and stick requirements, skate hollow,etc.
    6. Any of your suggestions

    Stuck at home self isolating from corona virus so have lots of time to play with this.

    I can send excel file if that helps.

    Thanks to anyone that replies in advance....John

    Click image for larger version. 

Name:	tables maybe.JPG 
Views:	26 
Size:	163.0 KB 
ID:	41353Click image for larger version. 

Name:	Stick Usage.JPG 
Views:	24 
Size:	103.5 KB 
ID:	41354

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your biggest potential trap is using your Excel brain to design a db. That will only cause you grief, and the deeper you get in, the worse it will become and the less you will feel like dumping everything and starting over. If you have not studied normalization that is your #1 task. Understanding it is your #2 task. Let me give one example of where I get the impression you don't fully understand it (assuming I understood details of your post):

    You want to put fields for what equipment a player uses, along with their name, phone number, etc. WRONG.
    Let's say there will be 5 equipment fields. For every one of them that a player doesn't need, you have no data. Worse, if you decide to add a 6th type, now what? Add a field, fix all your queries, fix all your code, fix all your reports? The equipment is not an attribute (field) of a player (entity) - it doesn't belong in a player table. You would likely have a table for equipment thus every new type added becomes a record not a field. You would have your player table with player info. To relate equipment to player, maybe a tblPlayerEquip table, which is commonly referred to as a junction table because you have a many-to-many relationship there.

    Because of the foregoing I didn't study your entire post because I think you're in danger of heading down the wrong path. Don't forget jobs 1 and 2!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    GolfNut39 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    5
    OK will take that advice and look at it again tomorrow. Thanks for taking the time to help...John

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In case it helps, here's the standard list of links I pass on to novice Access users. You may get more worthwhile suggestions from other forum members. These particular ones will help in ways you might not even be able to imagine yet (thinking of the ones on naming, for example).

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

    What you might want to do is post back later with info on your proposed table schema. That's often done by creating the tables and then their relationships and posting a pic of your relationship window. You might have to make a lot of adjustments, but it beats doing it all wrong. Or you can post a pic of the schema if you have the app that creates your model. There are some free ERD tools around, but I confess I've never used them so I can't comment on that.

    Good luck with your project!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    GolfNut39 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    5
    Thanks for those links. I think I learnt a fare bit, not to the point of totally understanding but unfortuantely I am a visual learner so have to go through all of your links pretty slowly. I appreciate that you say it may take a few attempts so won't feel I will get it right away so for me good to keep trying. I am kind of stuck at next point but have made many changes.
    My big struggles are:
    1. The sticks. This is biggest area of equipment and expense as each player will go through 6-10. Darn I forgot to add model to my tables as this may change part way through the year when a new model comes out. Model may have to link to price as well as price may be different for every model.
    2. Goalie sticks. Should I just put them in sticks or have a table made tblPosition and from there does it link player or goalie sticks?
    3. tblSkates is an info only. It needs no connection with pricing. Just a nice to have when sharpening skates. Just trying to learn as I make decals for bottom of skates with logo, number and hollow anyway.
    4. Prices are guesses as new pricelist not out yet and having their own table easier to change when that happens?
    5. Not getting too far ahead but where will I keep track of quanity in stock, required, balance, ordered, arrived,

    Need to take a break as confusing myself. Appreciate your time...JohnClick image for larger version. 

Name:	2-Structure.JPG 
Views:	23 
Size:	46.9 KB 
ID:	41361Click image for larger version. 

Name:	2-Tables.JPG 
Views:	22 
Size:	92.7 KB 
ID:	41362

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Micron provided some reading -
    Reread Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

    My thoughts - Consider:
    Click image for larger version. 

Name:	Img1.jpg 
Views:	19 
Size:	95.6 KB 
ID:	41365



    Click image for larger version. 

Name:	Img2.png 
Views:	22 
Size:	169.1 KB 
ID:	41366
    PK stands for Primary Key
    FK stands for foreign key (- links to PK)


    So this falls in the "many changes" arena......

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This point seems to have been missed
    Worse, if you decide to add a 6th type, now what? Add a field, fix all your queries, fix all your code, fix all your reports?
    because while you didn't do it in the player table, you still did it. Again, adding a new value for an attribute (type of equipment in this case) will require you to add a new field to the table. This must be avoided at all costs - can't be over-stressed.

    You are still using your Excel brain so you don't understand normalization yet, although I believe you likely read up on it. If what I posted doesn't paint a clear picture, find other sources that drive the points home for you.

    ssanfu, thanks for your usual great assistance. I'm spending most of my days lately doing a wood working project so I only seem to be trolling in forums early and late in the day. In case you're interested in what that project is, take a look here

    https://www.access-programmers.co.uk...a-gent.310154/

  8. #8
    GolfNut39 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    5
    Thank you for taking the time to show me this properly. It connected a lot of dots for me and I can see where I was going terribly wrong as Micron was explaining of actually putting the data in. I need to watch a whole database be built as I am always trying to solve future problems and still don't get where the data will be so I will do that and get back to you.
    - I totally get what you have highlighted in RED.
    - PlayerID was my poor attempt to make a relationship from tblPlayer and I get that with your example now.
    - Black was my poor excuse for and autonumber
    - I had PlayerstickID because looking ahead will also need GoalieStick which will be like Paddlesize eg 26: LH or RH. On that which table or does it matter at this point will I need to have which way the player shoots?

    I could send excel file if it would better explain what I am trying to say but I see more I need to figure this out.

    More learning to do. Again appreciate your time. Your examples helped so much. Need any decals or vector graphics help let me know to show my appreciation. Thanks John

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I had PlayerstickID because looking ahead will also need GoalieStick which will be like Paddlesize eg 26: LH or RH. On that which table or does it matter at this point will I need to have which way the player shoots?
    Not that I've ever done a db such as this, but I would suspect that handedness is an attribute of the player. What will likely take some thought will be equipment. A table for equipment in general would contain equipment types (skate, glove, mask, stick) but it's the attributes of the equipment that present a challenge. Sticks may be right or left hand, but skates are not. So do you have a table for each equipment type, or 1 table for equipment and a table for attributes to join them? The latter would permit adding records as new equipment may come into force, and so would the attributes table allow the addition of a record to cover the attributes for that equipment. I think this part is going to be one of the more challenging aspects of your db.

  10. #10
    GolfNut39 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    5
    Yes good point. I am certainly am not the one to have good advice on this. All I can add is what each item requires. For example:
    Gloves: 13" 14" and 15"
    Skates: Just info as they tell me what to sharpen them at 1/2" 5/8" etc.
    Helmets: Small - Medium and Large
    Pant Shells: Small - Medium, Large and Extra Large
    Bags: Player or Goalie
    Face Shields: New "Clip" (comes with hardware to apply to helmet then shields are interchangeable) or "replacement" Where just shield and easyly changes out.

    Sticks are whole new ball game:
    Curves: P28, P92, P88 and option to add a different one if need be.
    Flex: 70, 77, 87, 95 and again option to add a different one if need be. The are Bauer flexes and if league goes to CCM it would be 65, 75, 80, 85, 95 etc.
    SSanfu had a great point for stick length as we have tall players that like them ordered taller so: HEIGHT: Regular or extended
    and the old LH, RH or Goalie issue. As you say I did originally had that under tblPlayerInfo as it is an attribute that will always stick with the player.

    Sorry did yard work yesterday as supposed to rain tomorrow.

    Cheers..John

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    With equipment of various types you can go for several paths - every one has it's own pro's and contra's:
    1. You can have for every type it's own table, with specific properties for every type registered. As follows, you'll need a separate player-equipment junction table for every type, to register all equipment given out to players. And you need a separate registering form for every type to register new models. In case a new type of equipment is introduced, you have do redesign whole app;
    2. You can have a table where you register equipment types, and a table, where you register equipment models (tblEquipmentModels: EquipmentModelID, EquipmentType, Description). You have a single player-equipment junction table to register all equipment given out to players;
    2.a. You have equipment model properties tables for every equipment type (tblEquipmentOfSomeTypeModelProperties: TypeModelPropertyID, EquipmentModelId, Property1, Property2, ...). You'll need a form for every equipment type to register it's models properties. Whenever a new type of equipment is introduced, you must add a new form to register properties of models of new type;
    2.b. You have a single table for properties (tblEquipmentModelProperties: ModelPropertyID, ModelID; PropertyName, PropertyTextValue, PropertyIntValue, PropertyDblValue). For every property, one value is not null, other values are null. This allows to register properties for new equipment types without need to redesign the app.

    As about equipment in stock, the classic approach is storage table, where you register all equipment movements (it will be a bit tricky when you use separate tables to register equipment of different types) tblStorage: StorageRowID, EntryDate, EntryType, [EquipmentType], EquipmentModelID, Qty). There are separate EntryTypes for purchase arrivals, giving equipment out to players, retrieving equipment back from players, scrapping obsolete/broken equipment, inventory corrections etc. (The field Equipment Type is needed, in case you go for path 1.) You either group EntryTypes as incoming and outgoing, or you quarantee that for all incoming entries Qty>=0 and for all outgoing entries Qty<=0. You can always calculate the balance (quantity available in stock) for every model at any date simply summarizing all entries in tblStock/deriving sum of outgoing quantities from sum of incoming quantities.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Settings Gear" symbol for command button
    By GraeagleBill in forum Forms
    Replies: 6
    Last Post: 02-15-2016, 03:37 AM
  2. Replies: 1
    Last Post: 02-01-2016, 07:21 PM
  3. Compare player results
    By Andyjones in forum Queries
    Replies: 3
    Last Post: 05-19-2012, 05:59 AM
  4. Changes to database don't stick
    By Nonz in forum Programming
    Replies: 3
    Last Post: 09-27-2011, 07:16 AM
  5. windows media player
    By ksmith in forum Programming
    Replies: 3
    Last Post: 09-20-2011, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums