Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2004
    Posts
    21

    Post Unanswered: Is this impossible to do in access

    Good day to everyone,

    This problem has stumped for a while. Been doing this manually but I am sure this can be done in access but i just have no idea how.

    Legend:
    Purchase Order - PO
    Primary Key - PK

    1 POID(PK) can have many ProductID's
    Each of these ProductID's belong to a FactoryID

    Namely

    1 FactoryID can have multiple ProductID's

    Example:
    POID is POID3344
    has item numbers:
    1111
    2222
    3333

    Item Numbers 1111 and 2222 belong to Factory - FACTORY666
    Item Numbers 3333 belongs to Factory - FACTORY999

    I have managed to breakdown the Items for that particular PO by factory

    POID3344
    FACTORY666
    1111
    2222
    **report goes to FACTORY666

    and

    POID3344
    FACTORY999
    3333
    **report goes to FACTORY999

    The problem is I need to assign a unique FACPOID for each of the factories receiving that POID, in this case POID3344
    for example Factory999 will get a FACPOID Fact33
    which actually means that this factory has been given 32 PurchaseOrders before this purchase order.

    Currently I am doing this manually using a simple excel spreadsheet to keep track of how many PO's have been given to anyone particular factory.

    I have been however been able to use Pivot tables to identify how many PO's have been issued to each factory using the column count functions, however I have no idea how to extract that count, no idea where to place the FACPOID.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2004
    Posts
    67
    sotong,

    If I understand you correctly, for each POID, every vendor on that PO gets a FACPOID that is the text "Fact" with a number that represents how many PO's THAT PARTICULAR VENDOR has gotten so far. So the FACPOID for each vendor on the same POID may be different.

    Assuming the above is correct, my suggestion is as follows:

    1) Consider naming FACPOID to something else. It seems to get confusing when we go into the next steps. Maybe FactoryPOCnt (Factory PO Count)?

    2) Create a new table (called tblFactoryPOCnts for example) to hold the Factory PO info. Your table would have the following fields:

    (I've made some assumptions/guesses as to the names of your existing fields. Obviously, change them as appropriate.)

    pklngFactoryPOCntID - autonumber
    fkPOID - foreign key for POID from PO's table
    fkFactoryID - foreign key for Factory ID from Factories table
    lngFactoryPOCnt - count for number of PO's to the selected factory

    3) Include the new table tblFactoryPOCnts in your PO Form's source query, and drag the pklngFactoryPOCntID and lngFactoryPOCnt fields into the query grid.

  3. #3
    Join Date
    Jul 2004
    Posts
    67
    sorry...continued from above...

    4) In your PO Form, add both fields from Step 3, but hide the pklngFactoryPOCntID field (it must be there so you add records but you don't need the user to see it) and disable/lock the lngFactoryPOCnt field so it can't be edited.

    5) On the PO Form, in the properties of your FactoryID field, create an event procedure on the AfterUpdate event that queries or looks-up the number of records in the new table tblFactoryPOCnts where the FactoryID equals the selected FactoryID in your form; adds 1 and sets the lngFactoryPOCnt value.

    I have not done exactly this and tested it, but it is similar to code I've written in the past. The only catch is in the scenario where a user can delete PO's after they've been "counted." In other words, if you create a PO (#1) for factoryA, the next PO would be #2. But if you delete PO #1, do you want the next PO to be #1 again or #2 for that factory. The answer there determines whether or not you set Cascading Deletes on the relationship between tblPOs and tblFactoryPOCnts. If you want to reuse a deleted count #, then cascade deletes. If you want the second PO to be #2 even if #1 no longer exists, then do not cascade deletes.

    The big picture here is that you are creating a junction table that cross-references PO's, Factories and the Counts. You're then using the entries in this table to determine the number of PO's issued for a given factory, and potentially keeping a record of deleted PO's/counts even though the PO's are deleted (if you do not cascade deletes).

    I hope this is clear. It may look like a lot, but it really isn't. If you need more detailed instructions on any of this, let me know.

    Regards,
    David

  4. #4
    Join Date
    Jul 2004
    Posts
    67
    Some after-thoughts...

    Where I talk about Cascade Deletes, I should also discuss Enforce Referential Integrity (ERI):

    If you ERI but do not Cascade Deletes, you will not be able to deleted a PO.
    If you ERI and do Cascade Deletes, you will be able to delete a PO, which also delete the Count record and therefore will repeat a count # later on.
    If you do not ERI, than you will be able to delete a PO without deleting the count record, which will prevent reuse of a count #.

    Also, this method leaves you with a numeric count #, not a string like your example "Fact333". Wherever you want to display the FactoryPOCnt, display a concatenation of the text "Fact" and the field value, such as reportfield=("Fact" & [lngFactoryPOCnt])

  5. #5
    Join Date
    Apr 2004
    Posts
    21

    Lightbulb Got you Halfway

    First of all davidmound thank you so much for replying pronto.

    I get your bit of having the junction table. Makes a whole load of sense. The whole concept here I understand, however the execution is another story.

    I have no problems executing from 1 - 4

    Number 5 is a little problem for me. HOw do I create such an event procedure?

    I have little doubts on how this is going to work, as I said earlier that I am currently doing the Factory Count manually.
    How do I ensure that the older PO's that have been entered before be updated by this newly created table?

    The other thing is and it might somewhat sway the solution;

    not all the PO's are entered in the access system, because most of the older PO's have been maintained in Excel, in a very haphazard format. This would mean that the count of these PO's should (ideally) pick up where excel left off in terms of the number of Po's that the vendor has been given.

    Because I am lacking the knowledge on how to execute this, I am unable to test just how well this junction table works...
    One POID can have more than 1 item per factory.

    I like your idea of concatenating the FactoryNumber with the FacPOIDCount. Very cool.

    Thanks again for your time; very much appreciated.

    Jin.

  6. #6
    Join Date
    Jul 2004
    Posts
    67

    Talking

    Jin,

    I'm not sure from your message whether or not you want to give this a try.

    I can walk you through Step 5 (the event procedure), which involves entering VBA code (which I will give you & tell you how to do).

    As for the old PO's, we can handle that as well. We can either get minimal data into the system for the purposes of the junction table only, or (preferrably) we can change the plan slightly to check for the highest Count # rather than counting actual entries. In this last scenario, we would then simply have to make one entry for each factory with the last used count #.

    Let me know if you want to continue with this and I'll walk you through Step 5. Once 1-5 are working, we can later update the counts from your spreadsheet.

    -David

  7. #7
    Join Date
    Apr 2004
    Posts
    21

    Lightbulb I do want to continue!

    First of all, I definately want to go full access with managing all the purchase orders. Inadvertendly a solution for this problem is a must, and I do want to continue.

    I have done step 1-4.

    I need a walkthrough on step 5 if you could guide me a little, am a little lost on where to start.

    I like the idea of counting only the highest entries, to make sure i understand what you mean by this is, by manually entering the count? could you please elaborate

    Again thanks dude.

  8. #8
    Join Date
    Jul 2004
    Posts
    67

    Talking Here's step 5

    Quote Originally Posted by sotong
    I need a walkthrough on step 5 if you could guide me a little, am a little lost on where to start.
    1) Open the PO Form in Design view.

    2) Go to Properties of the FactoryID field, then the Events tab

    3) Click in the 'After Update' blank, click the elipses ("...") to the right of the blank, then double-click 'Code Builder'.

    (This will open the code editor. Your cursor will be between a line that starts 'Private Sub' and a line that says 'End Sub'. It is between these two lines that you need to enter your code.)

    4) Type the following (substitute your actual table and field names as appropriate):

    Code:
    varMaxCnt = (SELECT Max(tblFactoryPOCnts.lngFactoryPOCnt) AS MaxOflngFactoryPOCnt, tblFactoryPOCnts.fkFactoryID
    FROM tblFactoryPOCnts
    GROUP BY tblFactoryPOCnts.fkFactoryID
    HAVING (((tblFactoryPOCnts.fkFactoryID)=Forms![POForm]![fkFactoryID]));)
    
    [lngFactoryPOCnt] = varMaxCnt + 1
    5) Close the code editor and save the changes to the form.

    THAT'S IT! Here's what happens...After you update the FactoryID field, the event will be triggered and it will find the highest Cnt # for the Factory you selected. It will then set the Cnt # for the new record to +1.

    I like the idea of counting only the highest entries, to make sure i understand what you mean by this is, by manually entering the count? could you please elaborate
    No, we're not manually entering the count. We're just entering the last PO for each Factory. Let's say Factory111's last Cnt was 23. Enter that PO, go into tblFactoryPOCnts and manually change the Cnt # to the appropriate num. Then, when you enter a new PO for that factory, it will find 23 as the max and add one.

    Hope this works well for you.

    -David

  9. #9
    Join Date
    Apr 2004
    Posts
    21

    Red face A little problem

    Hey David, your steps are extremely clear and precise, however i cannot seem to get step 4 working.

    Is it because I am using access 2000 and you are using a lower/higher version?

    I've heard of incompatabilities. Sorry it is sounds like a real newbie question to you but I am very new to this.

    If I could trouble you to send me a simple access file with the relationships and concepts inside? Cause it would help speed up my understanding on how to get it to work.

    Only if it is not too much trouble though.

    Thanks gain.
    Jin.

  10. #10
    Join Date
    Apr 2004
    Posts
    21

    In Hindsight

    Hey David,
    I have just reevaluated my design structure, and I didnt realize that currently I am retrieving the FactoryID from the QUote SHeet table, something I didn't mention before or realized.

    The relationship shown are used for the Purchase Order Subform <- that's right I am using a subform for the Purchase Order.

    If you look at the ProductTable, I have added the FactoryPOID field there to link with the newly created table, therefore eliminating the requirement for the quote sheet table being involved in the Purchase Order Subform QUery.

    That make sense to you?

    Well so I have done the New Purchase Order Subform and the second attachement will show the newly created table and the relationships.

    However I cannot seem to add anything in the query; based on previous experiences, it means that there is something wrong with it. DO you know where i fluffed?

    Sorry to bother you with this problem, appreciate your time.
    Jin.
    Attached Thumbnails Attached Thumbnails subformquery.jpg   newsubformquery.jpg  

  11. #11
    Join Date
    Jul 2004
    Posts
    67
    Your FactoryPOCount table looks wrong. Maybe because in my previous instructions I didn't have your field names and the instructions got mixed up in the translation.

    The problem isn't with your query, but with the table/relationships.

    See my attachment and let me know questions.

    -David
    Attached Thumbnails Attached Thumbnails correctsetup1.jpg  

  12. #12
    Join Date
    Apr 2004
    Posts
    21

    FacPOID foreign key?

    Based on your relationship picture, i see that the FacPOID in the New table is actually a foreign key.

    If FacPOID is the foreign key in the newly created table, then where is the primary key?

    I cannot understand how the factory will be identified in this table if there is no FactoryID field in the junction table.

    Would it help if i pass you the entire relationship structure, cause it may be a little difficult to understand otherwise.

    Thanks
    -Jin-

  13. #13
    Join Date
    Jul 2004
    Posts
    67

    Red face

    Let's try this one more time through a text explanation....

    I think the way you name your fields and the way I name mine is getting in the way of the explanation:

    There are 4 fields in the junction table (shown in the order they appear in my attachment in previous message):

    Field1 is the primary key of the junction table. It is an autonumber because the data in this field in not important, it just identifies each record.

    Field2 is a foreign key that holds the id for the purchase order. It must be the same data type & size as the primary key in the po table.

    Field3 is a foreign key that holds the id for the factory record. It must be the same data type & size as the primary key in the factory table and the foreign key in the products table. It is not directly related to the factory table though, but instead is in an intermediate relationship with the factory id in the product table.

    Field4 is an 'unrelated' long integer field that holds the po count # for each factory-po combination.

    So, for every record in the junction table, there is a po, a factory and a count (and a meaningless record identifier).

    The relationship for field2 is 1-to-many where the junction table is on the many side (there can be many junction table entries for each po because it may represent many factories, but there can only be one purchase order for each po#).

    The relationship for field3 is many-to-many (there can be many products purchased from the same factory, and there can be many factories utilized on the same po).

    Does that help?

    -David

  14. #14
    Join Date
    Apr 2004
    Posts
    21
    Okay I think I just found out where we are different in understanding.

    Field 1 and 2 i am super clear. however :
    for Field 2 I am unsure if it is an issue, but my field 2 POID already has a link out from the Purchase Order table, as I already have another POID foreign key in another table called Purchase Order Details. Is this an issue?
    The purchaseorder details table doesnt seem to have any problems updating, but the junction table isn't

    "
    Field3 is a foreign key that holds the id for the factory record. It must be the same data type & size as the primary key in the factory table and the foreign key in the products table. It is not directly related to the factory table though, but instead is in an intermediate relationship with the factory id in the product table."

    the problem here is the intermediate relationship for the FactoryID, is not the Product table, it is actually through the quote sheet table. this is a problem right?

    Your patience is greatly appreciated.
    -Jin

  15. #15
    Join Date
    Jul 2004
    Posts
    67
    for Field 2 I am unsure if it is an issue, but my field 2 POID already has a link out from the Purchase Order table, as I already have another POID foreign key in another table called Purchase Order Details. Is this an issue?
    I don't think so. A record can be a parent for many children...a single field can be on the '1' side of several '1-to-many' relationships. If that's your concern, don't worry about it.

    The purchaseorder details table doesnt seem to have any problems updating, but the junction table isn't
    After we put the proper relationships in place, we'll see if the table updates.

    the problem here is the intermediate relationship for the FactoryID, is not the Product table, it is actually through the quote sheet table. this is a problem right?
    Aaahh, yes. You must follow my instructions grasshopper. The relationships must be as I described. If you use the wrong tables, the data won't flow.

    Do you want to send me a copy of the database?

Posting Permissions

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