Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Hi All, can any one help with a delivery table?

    Hi Guys im new here and i apologise in advance if this has been answered before.

    i have a database that i am writing in access 2010 that creates purchase orders, what i am struggling with is a way to record the deliveries that are made against each order that we receive.

    i have the purchase order table "PurchaseOrders" and that's linked the the "OrderDetails" table that holds information about each order.

    I have created a table called "GRN" that I want to record the deliveries in but my problem is this.

    i have a query that calculates the amount delivered from the amount that we ordered so for example

    if i ordered 2 spoons and we only had 1 spoon delivered there would be 1 spoon out standing.

    i am struggling with a way of storing this information, what would be the best way to record the outstanding amount in the grn table.

    any help would be most welcome, ideally i would like a button on the purchase order form called GRN that when clicked opens up with a copy of the purchase order line items, when i put a figure in a field called "received" and click update, that information is put into the grn table.

    hope this make sense.

    Steve

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    There's no reason for a separate deliveries table. In the Purchase Orders detail table, you already have a field for qty ordered. You should also have another two fields for qty received and qty open. (There are other ways to do this; I'm just giving an example). When you receive a delivery, you should enter the details via a form. Let VBA edit the various fields in the table for qty received and qty open. The same VBA, by the way, will also tell you how much you owe the vendor for the supplies you just received. Etc.

    Sam

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think there is a good reason to haver a separate delivery table
    yu may need to query a delivery or an order with a supplier. knowign precisely waht was delivered as part of each delivery can be significiant.

    a supplier may also make multi order deliveries (ie the items delivered may be from separate/multiple orders, and it maytake several deliveries for an order(s) to be complete.

    auditors like GRN's and other forms of paper / psuedo paper trails.

    I'd have a table for GRN's which identifies all the products delievered as part of that GRN. you may be lucky and have the order number on the delivery note.. if so store that against the GRN... makes marrying up the delivery with the order. but bear in mind you may not know your order number at the point of delivery, depends on the quality of the suppliers documentation.

    .
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2012
    Posts
    2
    Hi guys

    thanks for the replys

    I am going with the separate table to record the deliveries but im having a real issue with how to go about this.

    i have a form that pull in all the orders from that supplier based on the purchase orderID,

    the problem im having is with the way of updating the grn table, would it be an update query were i update the grn table with the delivery details (part number, qty delivered and grn date and number) or would it be an append query were i had these into the table.

    im sure im on the right track but i have been looking at it now for so long, i can't see the woods for the trees.

    Steve

Tags for this Thread

Posting Permissions

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