Results 1 to 3 of 3
  1. #1
    QuickJeff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    3

    What would you do? Simple for a expert!

    So I've decided to make the leap from Excel to Access. So much to learn, but in the long run it should be a better alternative. I keep bumping heads with myself in Excel as I expand the automation.



    I have a small window business.
    I have about 100 products, each one having up to 10 accessory options.

    Each product's pricing is specific to a unique measurement (window size). Here is an example of something like what I have been using with Excel. The "rows" are priced from window sizes.
    Click image for larger version. 

Name:	ExampleExcel.PNG 
Views:	18 
Size:	92.5 KB 
ID:	25101

    I'm plan on using irow and icol to find the coordinate value of the measurement to find my cost. Then calculating a mark up.

    In regards to designing the database:
    Am I better to break my products up into separate tables "Wood Windows" (QTY 25), "Vinyl Windows", (QTY 25), and "Aluminum Windows" (QTY 50)?
    Also, the options of each window can be colors, gases, films, coatings, etc. Would I be best to include the options on the same product table?

    My ultimate goal is to be able to compare pricing side by side and create a formal quote with different windows for different portions of the home or building.

    Thank you to anyone who takes the time to read this and reply!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Am I better to break my products up into separate tables "Wood Windows" (QTY 25), "Vinyl Windows", (QTY 25), and "Aluminum Windows" (QTY 50)?
    No - doing this means you are building data into the structure of your db which is against normalisation rules, instead have one table and an additional column for type of material

    Also, the options of each window can be colors, gases, films, coatings, etc. Would I be best to include the options on the same product table?
    Probably not, instead you might have a BOM table (bill of materials) - all depends if additional options could be added to in the future.

    At the moment you are jumping in half way, probably because you already have something in excel and in danger of trying to replicate excel in access. Access is not a bigger excel, it works completely differently. In excel, data and presentation go hand in hand, in access they do not. Excel data is stored and presented 'short and wide'. In Access it is stored 'tall and thin'.

    Step back and think how your business works, the process - how windows are ordered, who needs to know what, do you have stock or make to order? or a bit of both? etc. Write it out on paper and it will become clear how to design your tables and relationships. The objective is to store data only once.

    For example - colours - is white the same for all types of frames? or perhaps looks the same but uses a different recipe and has a different cost. Fittings - are these specific to particular type of frame of or actually have a different construction and cost.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Additional to Ajax"s comments, work through the tutorials at Rogers Access Library http://www.rogersaccesslibrary.com/forum/forum46.html
    Actually take the time to work through the tutorials. It will/should help you learn some basic concepts.
    Read about "Normalization"...

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

Similar Threads

  1. Microsoft Access expert
    By Christian1977 in forum Access
    Replies: 1
    Last Post: 07-28-2013, 03:20 PM
  2. expert of access please help me..
    By noel17m in forum Access
    Replies: 1
    Last Post: 02-20-2012, 09:57 AM
  3. Access Expert
    By golfit007 in forum General Chat
    Replies: 1
    Last Post: 08-14-2011, 11:01 PM
  4. Replies: 3
    Last Post: 08-02-2011, 02:44 PM
  5. Need Expert Help!! Thank you!
    By Antflash in forum Access
    Replies: 3
    Last Post: 08-18-2010, 05:09 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