Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Simple DB Help

  1. #1
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30

    Unanswered: Simple DB Help

    Hi, simple question: How can I relate (join) the quantity column in two different tables. The tables are already related but this field is not the primary key. Does this field have to be the primary key?

    Any help is greatly appreciated. For now, I am using the design tools of Access until I can gain more knowledge. Thank you
    Last edited by back2basics; 01-15-13 at 13:17.

  2. #2
    Join Date
    Nov 2012
    Posts
    10
    what do you mean by qantity column? And why do you want to link columns where both are not a primary key?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can create a relations between to column (even the same column in a table)
    a realtionship can exists on non primary keys (as it usually is when you join two tables where one side of the join is on a priamry key and the other a non primary key, a so called foreign key.

    but the two sides have to hold the same value, and although its not critical should be of the same datatype ie you can join on numeric columns, youd be dumb to try and join a numeric to a string/text

    however like claudiak Im baffled by why you'd want to join on quantity

    usually you'd join through a product number or similar type of value NOT an attribute such as quantity

    so to answer your question as shortly as possible

    yes you can
    but in practice you wouldn't
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    First, Thank you for your reply. I need help. Please allow me to explain my simple setup:
    3 tables currently exist:

    Table 1, employees: Fields: last, first, emp_id
    Table 2, assigned_materials: emp_id, material_name, employee, quantity
    Table 3, materials: Fields: ID, material_name, part_number, manufacture, location,quantity_on_hand

    Both Table 1 and Table 3 are related to table 2 where table1: emp_id and table2: emp_id are the primary keys and foreign keys respectively and table 2: material_name and table 3:ID are the primary keys and foreign keys respectively . This was done with the "add existing fields" wizard.

    The purpose of the DB is to track which employees has which materials( and how many). My problem is when the material is assigned to an employee which is table 3's purpose, the quantity_on_hand of table 2 should decrement the respective amount entered in the quantity field of table 3.

    When I related table 2 (materials) and table 3 (assigned_materials), I did not include the quantity field in the relation because they are not the same number. My understanding is that each table can only have one Primary key.

    How do I relate or join ( not sure) table 2 quantity_on_hand and table 3 quantity so that they decrement and increment with each other respectively?

    Thank you very much for your time.
    Last edited by back2basics; 01-16-13 at 12:15.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the moidel needs revisiting/refining
    from what I see at preent you'd have (at least) 3 tables
    an Employee table PK:ID (probably an autonumber)
    a materials table PK:ID (probably an autonumber)
    an intersection table which identifies what mnaterials are assigned to what employee
    that would have a composite primary key of
    EmpID and MaterialID both of which are foreign keys back to their respective 'parent' tables

    what Im not sure about yet is whether material is somethign like, say a book or tool or its something that you make. IE can the quantity go upp or down due to production in this db.

    Calcualting quantity on hand is usually what's called a derived field and is usually a no no in a relational database. instead you record the things that add or reduce stock and then add up those transactions to give the stock on hand.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Healdem, Thank you for your help. These are tools, big tools, like buckets trucks, drill trucks, trenchers, back hoes, road signs, .... we do not manufacture anything. These tools or materials ( in the long term when this is said and done) can be assigned to employees, work trucks or even work crews. The materials can be returned to our yard when work completed and therefore the material table will increase, they can be transferred to another crew (material table stays the same) or even obsoleted ( materials deleted).

    You are correct in your assessment above about the PK being autonumbered however, I did not use the autonumbered ID as the PK in table 1 and 2. The PK between table 1 and table 2 is emp_id on both and they are numeric. I did this because I thought it was redundant (perhaps I made a mistake. On the other hand, in table 3 the autonumbered ID is the PK and material_name is the foreign key and they are autonumber and text.

    When I try to create a separate relationship between quantity_on_hand of table 2 and quantity of table 3, Access prompts that the a relationship already exists do you want to create new? I say yes and enter quantity_on_hand and quantity and try to create new. I get the error " unable to lock..........because it is already in use".

    Refining/revisiting is Definitely or probably needed.
    Last edited by back2basics; 01-16-13 at 11:25.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    For me I'd have a table for equipment with an autogenerated key. there is no need for an employee id in this table as it contains details if equipment you have (whether thats owned, leased or whatever) that table has whatever 'stuff' youneeed for the APP, eg a machine description, whatever

    then I'd create an intersection table that knows about a specific machine being allocated to a specific employee, including the date and time it was allocated AND as a separate column the date and time it was returned. plus any other information that is relevant to that specific instance of an employee being allocated that piece of equipment

    you can easily find what equipment an employee has by looking at the intersection table for:-
    all rows with that employees ID
    AND
    EITHER a NULL (empty) returned date
    OR a ReturnedDate which is greater than, say, today

    you could make life easier for yourself by say storing a flag in this intersetion table that identifies if the machine is no longer allocated (and automatically set it when a returndate is entered.. simplifies the query enormously.

    to find what equipment is currently in use its the same as above without limiting by employee ID

    to find what equipment isn't in use is a bit trickier
    you need all equipment which has either
    NEVER been allocated to an Employee
    OR
    has been allocated but not yet returned


    to be honest the easiest solution is to keep the returned date emtpy (NULL) until its back in the yard

    or, of course you could look out of the window or ring up the yard foreman and ask
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    For me I'd have a table for equipment with an autogenerated key. there is no need for an employee id in this table as it contains details if equipment you have (whether thats owned, leased or whatever) that table has whatever 'stuff' youneeed for the APP, eg a machine description, whatever

    Agree, this is how table one is setup. I made a mistake above in my text with the field assignments of the tables. There is no emp_id in the materials (not needed).

    then I'd create an intersection table that knows about a specific machine being allocated to a specific employee, including the date and time it was allocated AND as a separate column the date and time it was returned. plus any other information that is relevant to that specific instance of an employee being allocated that piece of equipment

    Agree, this is what the assigned_materials table2 does. Table 2 is linked to table 3 materials ( by...ID and material_name); however I do need a return date, thank you. And Table 1 is linked to table 2 (by... emp_id and emp_id).

    you can easily find what equipment an employee has by looking at the intersection table for:-
    all rows with that employees ID
    AND
    EITHER a NULL (empty) returned date
    OR a ReturnedDate which is greater than, say, today

    you could make life easier for yourself by say storing a flag in this intersetion table that identifies if the machine is no longer allocated (and automatically set it when a returndate is entered.. simplifies the query enormously.

    to find what equipment is currently in use its the same as above without limiting by employee ID

    to find what equipment isn't in use is a bit trickier
    you need all equipment which has either
    NEVER been allocated to an Employee
    OR
    has been allocated but not yet returned

    Agree, but I still don't know how to manage the quantity_on_hand or available when I assign it to an employee. If I don't have it automatically reduce the amount from the materials table, my count and available stock will be off.

    Thank you for the flowchart, let me digest. I am an old timer. In my earlier days ( fresh out of school) I used to easily make DB3 and DB4 databases which then was simply Basic language. I understand the code and syntax of SQL but for the like of me, I can't figure out how to view and edit the code that this "wizard" is generating?? All these tools and automation for me makes it difficult to learn. I also don't know how to compile this DB to see how it runs with VB. Can you help here?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    erm that wasn't a flow chart, that was a very basic data model (with just primary keys and critical columns knocked up in SQL architect.

    you don't manage quantity on hand, you query the tables for what equipment is not allocated
    the systems knows about
    the equipment you want to track (as it must be defined in the equipment table)
    the employees
    and it knows what equipment has been allocated to whom for when
    ..so you can also run historical queries ferinstance "who had the JCB 3CX on the 5th of January 2013"

    Derived from that
    the system should be able to tell you what equipment ISN'T allocated. if you follow the method of ONLY allowing a return date to be set once the plant is returned it becomes very simple with the correct style of JOIN

    I would have expected there to be an allocation to Project aswell as employee
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    It seems I am going about this all wrong. I understand your approach to simply let queries and reports sort through the data in the material table as to allocated or not. but, what happens if I have 250 of the same item, i.e., lots of same tools? Wouldn't I have to enter that item 250 times in this approach?
    Last edited by back2basics; 01-16-13 at 14:21.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how else do you know what is allocated to an employee?

    as ever with questions posted on forums, its only an opinion. is 'your' or 'my' way correct, well ultimately its your way as it your application
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Yea, I agree. At least now I know how to track all the materials and keep an accurate count and location of every single item.

    I thought it to be simpler to use grand totals in the "master" or materials DB and run a macro or something to auto decrement the appropriate amount assigned to employees or crews.

    Thank you

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by back2basics View Post
    Yea, I agree. At least now I know how to track all the materials and keep an accurate count and location of every single item.

    I thought it to be simpler to use grand totals in the "master" or materials DB and run a macro or something to auto decrement the appropriate amount assigned to employees or crews.

    Thank you
    if that's what you want to do, and you don't need the detail then your way is fine. I don't make any claims at being an expert, but I do have a reasonable grasp of business processes and data modelling. my approach to your problme refledcts that, but if you don't need to know where a specific piece of equipment actually is, rather than, say Fred Smith has 3 if this, 2 of that, and 10 of whatd'ycallit. then thats fine. its your model representing your business processes.

    I tend to over complicate, because thats what most of my work is about, unpicking complex problems

    There is nothign stopping you allocating a number of X,y or z equipments to an employee, just don't store the quantity on hand

    if your model knows, say the number of JCB 3CX's it has (stored say in the equipments table AND by SUMming the number of the same equipmentID in the intersection table and subtracting you know how many JCB's are out in the yard.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Yes, both ways will work but no doubt a complete populated DB of all materials is the most accurate and best for several reasons:

    One problem I see in this method below ( which is not in the method above because an allocation field can be assigned to each material piece) is that the below method will not tell you if a material has run out or been totally allocated before you assign it. Only the report can tell you that and that is usually run at random.

    if your model knows, say the number of JCB 3CX's it has (stored say in the equipments table AND by SUMming the number of the same equipmentID in the intersection table and subtracting you know how many JCB's are out in the yard.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    its all in the data and how you manipulate it.

    you have a query which can tell you then number of materials assigned. in your form you don't allow more than the number of itesm to be allocated.

    there is nothing stopping you using the query to create a 'number on hand' as an unbound control and decreasing that as rewuired, handle any logic that stops that value going negative. ferisntance as soon as you load the form for a specific material calcualte the stock on hand, then when that control drops to 0 disable the users ability to allocate from stock

    get the data right first, then most of the rest is pretty linear. granted it aint going to be 'simple' but it will be straightforwards

    at some point you sill need to decide if you want to go down the record each item or record allocations approach. ie your original or the suggested appraoch
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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