Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Question Unanswered: A little help required Please

    Hi

    I work for a small charity and we have just starting to sell older computers to people on low incomes or unemployed,

    I've been asked to design a stock control / orders database as we are receiving donated computer equipment all the time.

    At the moment I have 16 tables that logs everything received from Base units, to graphics cards, to mice, everything has a unique number and code (1,2,3,4,etc, etc, DB1 – Donated Base 1, DM3 – Donated Monitor 3); it all has to be logged for audit purposes. All the data has been entered into theses tables.

    Now I need to add another table which tracks orders, something simple Like: Name, Contact number, reference number, and what the clients after.

    My problem is this, how do I link the order table to the other 16+ tables, so that when a clients machines has been built and ready to go out, all I want to be able to do is pull up there details and enter the ID's of the components used in there machine for the build and then those components are removed from the stock tables (first 16 in this case). This way I can keep an eye on how many bits we have left.

    There must be a way to do this but I’m not sure how, my knowledge off access is getting better but its not brilliant, so the simplest solution would be the best.

    If anyone has a better way of organising this database im open to suggestions because at the moment its a mess, also if anyone has got a simple stock control / orders db that I could have a look at it would be brilliant, it doesn’t have to be anything complex just something simple that works.

    Any help is greatly appreciated.

    Icerat

  2. #2
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Can anyone help??

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Create an inventory table containing an ID, parts description etc

    Create an order table containing an order id and customer id

    create an order detail table with order id as a foriegn key, and parts id as a foriegn id.

    subtract the sum of parts in order detail from the sum of parts in inventory to get current item counts
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Relationships

    This is a little more complicated than can be summarized in an email.

    To make this truly work your tables need to have relationships. But to do this you need to make sure your tables are structured the correct way.

    Does each table have some type of Primary Key (PK)?
    Are the tables in Third Normal Form?
    Do the tables that are going to be joined have a Foreign Key (FK) field included?
    Are these matching data types?

    If all of these questions are yes then you can simply go to the relationships part of Access for your tables and make the relationships between them, choose if you want to include referential integrity etc.

    It sounds like your new table will soley consist of FKs all linked back to the first 16 tables.

    HTH.

  5. #5
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    Looks like im going to have to start from scratch on this db as been looking around for sugesstions on haw to start this db properly.

    Could you suggest a way to get all 16 parts tables into 1 main stock table. Also need to join the stock and ordering sections together somehow.

    Any ideas how this could be achieved.

    Thanks

    Icerat

  6. #6
    Join Date
    Dec 2003
    Posts
    268

    MS Example

    If memory serves correctly MS has an inventory control DB example somewhere in thier support section. You could also look at NorthwindDB also. This has some good examples of how to do things.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    good suggestion - why re-invent the wheel.



    meanwhile, i was re-inventing the wheel so here it is:

    i was thinking of suggesting using one table yesterday but i didn't want to complicate your life. now i see you have come up with the idea yourself.

    1 table of Parts has to be more manageable than 16!
    ...and then you just need a second table with types-of-part so you can define mice or grafix cards etc.

    it looks something like this:

    tblTypes:
    typeID, auto, primarykey
    typeDescr, text 15, index unique

    ...containing:
    1, "CPU"
    2, "Grafix Card"
    3, "17in Screen"
    etc etc etc for your 16 different types of parts

    tblParts:
    partID, auto, primarykey
    typeID, foreigh key on tblTypes 'what type of part this is
    '...plus maybe other useful fields from one of your 16 tables
    'plus...
    partSold, yes/no, default:False

    tblOrders:
    orderID, auto, primarykey
    customer, text
    address, etc etc etc etc - whatever else should be in the "order header"

    tblOrderLines:
    lineID, auto, primarykey
    lineNum, integer 'your code increments this 1, 2, 3, 4 as you add line items...
    orderID, foreignkey on tblOrders '...to this order
    partID, foreignkey on tblParts '...with this part


    the "sell" process involves:
    making a new order in tblOrders
    ...and then for each line item:
    committing the part by setting partSold True
    adding another record to tblOrderLines


    SELECT * FROM tblParts WHERE ((typeID = 2) AND (partSold = False));
    gets you all the unsold grafix cards (i.e. reconstructs one of your 16 tables)

    etc etc etc

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi

    Thanks for the reply, im in the process of sorting that part of the db out, In the meantime though ive been working on the orders side of the db and trying to get that to work.

    Ive attached what i got so far, its not alot and its probably easy for you guys to sort, but im getting there

    When you open the client info table you can enter the clients name and then when you expand the table you can add who took the order and weather or not its been started as well as when the items needed by.

    If you expand that table you can select the product there after and the serial number of that product, (I havent been able to figure out how to get the prices and quanities into seperate fields in the table)

    I created a test for that shows the clients contact details and who took there order, but i cant figure out how to get below that another subform that shows the order info for the person who took the order, i would like to be able to click on a order in the top form and have it show me a break down of all items oreded with that person by employee. Ive seen an exampl of this in the NorthwindDB, the customer orders table.

    Im new to access and have lernt alot but keep running into problems so any help anyone can give is greatly appreciated, ive attached a copy of my db for you guys to look at and help if you can.

    Thanks
    Icerat
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Anyone able to help with this

Posting Permissions

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