Results 1 to 10 of 10
  1. #1
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    172

    Link tables

    Hi all. I am hoping for some help. I have spent several days trying to connect four tables. I can not get the forth table to work. I need tblInventoryMaterial to work with tblInventoryTime. I have tried linking them using a control (ctrlLinkTimeAndMaterial) using various fields in tblInventoryMaterial . I need to have several records in tblInventoryMaterial to match one record in tblInventoryTime. There are 3 crews so each crew will have material each day. I hope this is enough info for you. Below is a screen shot of the relationships. Thank you in advance.


  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    We do not see a screenshot.
    Groeten,

    Peter

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you post your relationships window with all tables extended to show all fields.

    There is no screen shot in your post.
    It would be helpful if you could tell us about "a day at work" to show us how these proposed tables fit with one another.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You have to upload any pics as attachments. Whilst the site appears to allow you to paste a pic directly, it is removed when you post the message.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    172
    Click image for larger version. 

Name:	Access.png 
Views:	22 
Size:	22.0 KB 
ID:	49922Click image for larger version. 

Name:	Untitled.png 
Views:	23 
Size:	35.4 KB 
ID:	49923
    The above are the screen shots. Let me know if you need more.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A database is typically used to support business. Businesses have a certain series of processes, and each process has some sort of business rule which is represented by relationships in a data base.

    As mentioned previously, tell us about your business, so readers can offer focused suggestions/advice.

  7. #7
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    172
    The biz is a parking lot striping company. I owned it many years. My daughter has it now. She is having trouble keeping tabs on her material. This form I am trying to build should help her match up time and material. I appreciate any help.

  8. #8
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    lawdy,
    Take a look at the attached database and see if that is the behavior you are looking for.

    Firstly, I added a primary key "InventoryMaterialID" to tblInventoryMaterial so that the field "fALngMaterialID" could become a foreign key that relates to the primary key of "tblInventoryTime," which is "fALngLinkToMaterial". This modification enables better management of the inventory data.


    Secondly, I added a helper textbox to the main form. This textbox can be made invisible and is designed to store the currently selected record in "sfInventoryTime". This value is necessary to link the subform control "sfInventoryMaterial" to the currently selected record in "sfInventoryTime". With this, each InventoryTime record will be related to different materials. I did not quite understand how the crews relate to each job, so I came up with that.


    Now, I hope those tables are parts of a bigger database where you have a table for things like Materials, Types of shift, etc. If not, then you should add those and follow a few other rules to have an easier time developing your database.

    Let me know how it goes.
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    So you have some business (a parking lot), where some workers are doing some jobs (At certain date, or starting from certain date, doing something there). For any of those jobs, some materials are used. And you need an overview, how much of those materials were used in any period, and how much of any of those materials are available currently. Is that what you need?

    When yes, then the database must contain:
    A table where all materials used for any jobs are defined. Like
    dblMaterials: MaterialID, MaterialName, ... (you can have any additional fields in this table which have a certain determined value for this particular material, like material type, or producer, or measuring unit, etc., in case there is a need for this);
    A table where all jobs done/started at certain date, are defined (it is possible to have recurring jobs too, but this will be more complex, and you do need additional tables then). Like
    tblJobs: JobID, JobDate, JobDescription, ... (In case all jobs are recurring ones, you can have a table like tblJobDefinitions: JobDefinitionID, JobDescreiption, and have JobDfinitionID instead of JobDescription here);
    A table where all crew members are listed. Like
    tblWorkers: WorkerID, Forename, LastName, EmployedAt, EmployedUntil, ...;
    A table, where is listed, which workers were doing certain job at some date. Like
    tblJobWorkers: JobWorkerID, JobID, WorkerID, ... (You can have here additional info about work of this worker - like worked hours, or start and end hours - when such info is needed. In case several workers were doing this work, there will be a row for every one of them);
    The most complex part of database will be keeping account of material quantities available, and used for different jobs. Generally you need a materials movements table, where all incomings (purchases, returned leftovers from jobs, etc.), and all outgoings (sales, work order details, scrapping, etc.), with movement date, and moved quantities, are registered. In your case, as the probably is no need for very detailed registering of work processes, I think you can split this info to 2 tables.
    The one will register all movements not tied to any of jobs. Like
    tblMaterialStorageMovements: MaterialStorageMovementID, MaterialID, MovementDate, MovementType, MovementQty (MovementType determines, was the movement for purchase, sale, scrapping, initializing, etc. You also have to decide here, is MovementQuantity always positive value, and MovementType determines, is the movement incoming or outgoing one, or user enters positive/negative values for quantities.);
    The second will register all materials used for jobs. Like
    tblJobMaterials: JobMaterialID, JobID, MaterialID, MaterialQty;

    To calculate available quantity of any material at certain date/datetime, you have to sum all material storage movements (incoming movements as positive numbers, outgoing movements as negative numbers) for this material until this date/datetime, and distract from it the sum of all quantities of this material in job materials table until same date/datetime;

    To calculate the used quantity of any material over certain time period, you have to sum quantities for this material in job materials table between start and end of this time period.

    To calculate, how many time any of workers was working over certain time period, you have to sum all working hours for this worker in job workers table, where job date is between start and end of this time period.

    Etc.

  10. #10
    lawdy is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jan 2013
    Posts
    172
    Ok Edgar
    , it really worked. This is part of a much larger db. And ArviLaanemets
    , I am studying you suggestions and will work the out. I think you both very much. You have solved my problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-03-2020, 10:32 AM
  2. Check Link to DB tables and re-link
    By alberigo67 in forum Programming
    Replies: 2
    Last Post: 08-11-2020, 09:52 AM
  3. Replies: 2
    Last Post: 06-08-2017, 07:32 AM
  4. How to link 2 tables
    By smba in forum Access
    Replies: 2
    Last Post: 02-29-2016, 08:32 AM
  5. One-way link between tables
    By Mikele di Sagitter in forum Database Design
    Replies: 1
    Last Post: 07-23-2010, 08:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums