Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Question Unanswered: Referenceing other related tables on a form

    Hey Guys,
    I am having trouble trying to find an existing solution to accomplish what I am trying to do (tried both dBforums and google). I have attached my dB stripped of all confidentail info, and a copy of the report with notes of what I am trying to acheive. To summarize I need to be able to reference a few fields in my report/query based on a related field.

    So my product stock table (tblStock) has all of my part numbers (ModelNumber), with product names (ModelName), and unit prices (UnitPrice). On my form I am only saving the quanity (Quantity0,1,2,...) and product name (ModelName0,1,2,...) to the PO table (tblPONumber).

    On my report (@ - Inventory Query for Report Printing) I am pulling the product name (ModelName0,1,2,...) and the quanity (Quantity0,1,2,...); but I would like for the fields I have created in the report to also pull the related part numbers (ModelNumber) and unit prices (UnitPrice). Additionally I would like for the unit prices (UnitPrice) to be multiplied by the quantities (Quantity0,1,2,...) reflectively, and then totaled in the Total field.

    And Finally I would like the totals to be summed into the Grand Total field at the bottom. Like I said I have attached a PDF presenting this is a more visual way.

    Hopefully someone can help me with this. I am a beginner access user... maybe an intermediate if I push it And would love the help and training from a more experienced user.

    Thanks!!!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Send a short example of your DB in Access 2000 or Access 2002-2003.

  3. #3
    Join Date
    Aug 2012
    Posts
    15
    I unfortunately cannot send my dB in 2000 or 2003 due to features from 2007 that are being used. It will not let me export the dB to an earlier version format.

  4. #4
    Join Date
    Aug 2012
    Posts
    15
    I really wish access had a better help feature built-in to assist with common tasks like this, I am amazed nobody else has never encountered a similar situation/request. Can anybody offer any advice?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to start with
    you have defined relationships but not RI (Referential integrity relationships)
    your entity tblPONumber looks suspect to me, anywhere I see columns named something0..something6 its nearly always a suspect design, one that is not normalised
    you have a table called stock, which in reality probably should be called product, but there is no RI link to the order (if there was then pulling across the product description and price would be easy). stock isn't usually a table in its own right, its usually a derived value (summing the amount of positive transactions (ie manufactured, bought in customer returns and subtracting negative transactions such as deliveries to customers, scrap, returns to suppliers and so on). there probbaly should be an entity called GRN or similar which details what items have been received from a supplier
    Im not to certain whether your PO are your purchases or your sales, Im guessing the former. but its not immediately obvious from your table/column names.
    there should be a table for Purchase order details (ie who placed the order, when , with which customer and so on
    there should be a table underneath that detailing what products/'Stock'. you shouldn't be repeating the modelname in your purchase order you should be pulling that from your product file. if you call the product something different to the vendor, then associate that different product name in the product/stock table. also I'd suggest you store the suppliers part number there as well.. suppliers dont' give a flying whatever about your part numbers (unless they are making the part on your behalf), they do care about their part numbers. internally your organisation probably doens't care about their part number but does care about your internal number. if you have alternative suppliers for the same product then you need to consider using an intersection table as a way of modelling a many to many relationship.


    so to summarise BEFORE worrying about report design issues, get your table design sorted out, understand normalisation.

    if the design is right then usually putting together reports is a breeze

    within a query you can use expressions
    eg select QtyOrdered * UnitPrice as LineItemPrice from MyTable
    or you cna do it int he report or form at run time.. set the value of a bound control to
    =QtyOrdered * UnitPrice

    incidentally I'd expect the purchase price to be in the purchase order detail table, NOT the product/stock table as the price will probably vary with each order and at sometime some muppet in accounting will probably want to know what the cost of items is.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2012
    Posts
    15
    Thank you for your help healdem! I originally had this set up with a subform, but could not get the fields to populate correctly on a subform. I have changed back to the subform setup, but am still lost/confused and unsure if I am doing anything right now. I am trying to limit certain products to certain vendors (to prevent the muppets that will be using this form) from assigning the incorrect products to a vendor. We are a small team only 6 employees, and we 'are' the accounting department. The whole purpose for these PO forms is by vendor request, since they want a 'formal' document from now on. We are purchasing the items from our vendors, so these are our PO's for them. The prices are fixed, and any change in product design price gets assigned a new product number so the price is ok in the same table and products. Now that I have made a few changes, and I hope I didnt make things worse; I cannot get the autopopulate fields to work. Is there a better way to accomplish this? I just want the product number and price to be auto selected based on the description that I select.. thats all. I've attached my current dB for reference to my mess xP
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    presumablkyyou buy items form specified suppliewrs (vendors)
    so I woudl expect vendors to be a FK in PONumber referring to a PK in Vendor

    You havent' defiend relaitonl Integrityu realtionships, merely relationships, that means you are missing one of the main ethos's behind relational databases where you enforce data integrity through Primary key (PK) and Foreign Key (FK) relationships.

    so you cannot refer to a vendor in say the purchase order without the vendor pre existing in the vendor table
    presumably there should be to FK's in PONumber
    one to identify the requested by and one to identify the created by

    tidy up your naming
    there are more naming conventions out there for db tables & columns than you can shake a tstick at.

    one is to name tables with a plural
    don't repeat the table name in the column name (EG I'd suggest you drop VendorID & VendorName as these are already defiend as vendor items becuase they are in table vendors. othgerwise you end up with
    tblVendorList.VendorName

    I'd suggest tblPoNUMBER becomes PurchaseOrders
    for tblPODetails suggest you rename it PODetails (there is no need to identiofy its a table by prefixing with TBL
    PODetails PK should be a composite key of the parent PurchaseOrders.No and an item number called say LineNo.
    if you know the only price you are interested in is the last price then your earlier design may be perfectly acceptable (ie price is defined as part of products), but in any event you do not need prices in both products and PODetails. However I would still advocate price in PODtails. as that price may vary with say quantity or supplier special offers.

    some reading
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2012
    Posts
    15
    Ok, thank you so much healdem, I have been learnign a lot!! Thanks!!

    I have attached the updated dB now, I still cannot figure out how I need to get the product description, MPN (manufacturer part number), and unit price to all auto populate when selected, maybe you can shed some light on what I am not seeing/getting.

    Additionally I cannot get the report to calculate the running sum for all of the totals on the report.

    Thanks for any help you can offer, I greatly appreciate it!
    Attached Thumbnails Attached Thumbnails PrtScr capture.jpg   PrtScr capture_2.jpg  
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2012
    Posts
    15
    Alright, quick update I figured out how to add an aggregate total. For anyone else with a similar problem, go to layout view, then select the field you want to total and then select Totals from the Records section on the home ribbon, then select SUM.

  10. #10
    Join Date
    Aug 2012
    Posts
    15

    Thumbs up Solved

    For anyone who has been following this, or who might need reference or even just want the dB I have found the solution. Please find the final dB attached. Feel free to use it however you want
    Attached Files Attached Files

Posting Permissions

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