Results 1 to 7 of 7
  1. #1
    EdaxFlamma is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jun 2013
    Posts
    34

    PlantBreeding/Inventory: Join key not in dataset; Table linked to another table 3 separate times


    Good day all,

    Attached is a copy of my database (2007). I have tried to make notes in the tables as best I can.

    It's been a while since I have worked with Access and I'm working through setting up a personal plant breeding and inventory tracking database. In an effort to keep similar data types together, I am wondering if I have made my structure more complicated than need be since I have been running into some issues.

    My hope was to use table.TransactionID and table.TransactionDetailID to control all the additions and subtractions of inventory in my database.

    tableTransactionTypeID contains a large number of different transaction types I had envisioned these two tables being able to handle.

    Unfortunately, not every transaction (ex. harvesting a fruit from a plant [+1 fruit to inventory], converting that fruit into seed [-1 fruit, +X Seed]) will have a BillingID and BillingAddressID associated with it because it is an internal transaction. I do still want to keep the SenderID and ShippingID information so that I have a geographical record of where that transaction occurred. So in order to deal with that, I have pulled the billing information out into its own table. Also, given that any Client can be any one of the different parties in a transaction, all three IDs ship back to a common pair of tables (ClientID, ClientAddressID).

    One of the big issues I seem to be having now is developing forms for inputting multiple tables worth of data without using subforms.

    Based on what I have decided to include in my Transactions table, I know I will eventually need to use multiple forms to deal with each transaction type. Example: one for incoming invoices, one for harvesting fruit, one for repotting, etc.

    To start, I have been trying work on a form to log incoming invoices. From a functional standpoint, I had hoped to be able to set up a set of cascading comboboxes to log all the clients involved in a transaction and their corresponding addresses. These combobox selections would then fill in all the PKs and FKs in the background, and then display the relevant data on the form. From an aesthetics point of view, I was hoping for this relevant data to take the form of an invoice where each combobox selection would display sender, billing, and shipping information. I only mention this because it was dictating my decision making when it came to form design.

    As it stands right now I have two main problems:
    1. I keep getting an error along the lines of "Cannot add record, join key of table "TransactionBillingID" is not in the dataset," whenever I try to tack on the Billing information to a new record.
    2. I don't have a good way to display more than one type of ClientID on my form. For example, even if my combobox works, enters data into the PKs and FKs in the background, I haven't been able to display more than one group of data (FirstName, LastName, Email, etc.) at a time from either SenderID, BillingID, or ShippingID.

    This feels like a lot to unpack and a rather tall order so I appreciate your help in advance.

    Thank you for your time.

    Attachment 40837

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My suggestion is that you write an overview description of your business in simple, plain English. Start at a very high level and gradually add some detail. It will help clarify what you are dealing with and assist users in understanding what you are trying to do.
    Here is an example of the level of detail that would be helpful.

    ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address.
    Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
    An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.
    From your database it isn't clear what is what. I see Client, Client_1 and Client_2 --perhaps you could tell us what these represent.
    I also see SenderID, SenderAddressID, ShippingId, and ShippingAddressId in the TransactionID table which is confusing (to me at least). Are all these needed? is there some rationale for each of these?
    I would advise against naming a table and its PrimaryKey with the same name (TransactionId).
    You may be expecting too much of the Transaction table, but more info is needed.

    Good luck with your project.

  3. #3
    EdaxFlamma is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jun 2013
    Posts
    34
    Overview
    The database I am designing is for my plant breeding program that I am working on. For the present, I am working on tracking the incoming inventory. To do that, I will need to record what is coming in, who it came from, where it was shipped to, the billing information of the purchaser and the date it was acquired. I will also need to record specific information about the various items that arrive including the generation of an in house lot number (for me I was looking at the TransactionDetailID to serve that purpose). All item types will have different information that will need to be logged. An example of that would be seeds and soils. Both item types will need to be added to inventory, but soil won't have a genus and species associated with it. I am hoping to get through the inventory side of things before adding that functionality.

    Additionally, I will need to be able to track different transactional events other than bringing in outside inventory. For instance, I will need to track planting events such as planting seeds, repotting plants, harvesting fruit, and harvesting seeds from fruit. Eventually I would also like to be able to log outbound events such as sales and discards.

    For example, a transaction log might look like this (transactions separated by "---"):
    ---Raw inputs coming in (manufacturer address, contact info, acquisition date, etc would need to be logged as well)
    +25kg soil
    +3 seed pots
    +10 seeds
    +4 pots 3L

    --- Planting event (seed, soil and containers are removed from inventory to create a new item that needs to be added to inventory)
    -1kg soil
    -1 seed pot
    -1 seed
    +1Planted seedling pot

    ---Plant repotting event (plant gets consumed to make a new item as large pot is removed from inventory and old pot is added back)
    -1 planted seedling pot
    -1 pot 3L
    -5kg soil
    +1 seed pot
    + Fully planted pot

    ---Plant fruit harvest event (Plant produces fruit and the fruit gets added to inventory)
    +1 fruit

    ---Seed harvest event (Fruit is destroyed to obtain seed which gets added to inventory)
    -1 fruit
    +50 seeds
    ---


    To your question about the different Client tables - Those are all just one table (table.ClientID) that is serving to hold all the information for people/business entities. Its function is very similar to the employee tables that are common in many of the example databases out there. Because I might send product to one person, that person may also send product to me, and all the information stored is identical, my understanding was that it was most efficient to store them all in one table.

    My thought was for the TransactionDetail table to hold all the inputs and outputs and then I could calculate inventory by comparing regular stocktakes to a list of transactions of specific lot numbers.


    My issue right now is just getting the billing information into the form for recording the incoming raw materials.


    I'm not sure if that's detailed enough. If not I am more than happy to dig deeper.
    Last edited by EdaxFlamma; 02-07-2020 at 03:26 PM.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I don't have a whole bunch to offer. I really don't know the details. However, I started to lay out a data model and found I was getting too detailed in an area I didn't understand. So, I built a graphic showing some RawMaterials and Supplier, then used your Events and some things I saw you used in those events and tried to diagram the higher level processes in your business. After the 4 major events I saw that SEEDS were returned to Inventory and from Inventory could be sold to Customers.
    Each of the Event processes will require details (as you already have said) re returning Pot(s) to PotInventory, retrieving Pot from inventory for RePotting etc.
    If you can abstract (consider as a concept) each of your events and consider them as "products", then you could consider an Inventory of several Products as an aid to your data structure.
    I think the RawMaterials and Suppliers could be sufficient for Purchase Orders (acqusitions).
    Have to leave for a while. Here is the graphic. Just trying to get the processes identified so that the details can be identified and captured. Need to get a model, and test it/revise it, to get a plan for the physical database.

    Click image for larger version. 

Name:	PlantBreedV00.png 
Views:	15 
Size:	63.4 KB 
ID:	40838
    Last edited by orange; 02-08-2020 at 06:37 AM.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Tried to download the Attachment in Post #1, but get/got an "Invalid Attachment" error......

  6. #6
    EdaxFlamma is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jun 2013
    Posts
    34
    Quote Originally Posted by ssanfu View Post
    Tried to download the Attachment in Post #1, but get/got an "Invalid Attachment" error......

    it may be that its a zip file? not surePlantBreeding.zipPlantBreeding.zip

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I did some additional searching and found this model from Barry Williams' site. It may help with some aspects.
    Last edited by orange; 02-08-2020 at 08:26 AM. Reason: spelling

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

Similar Threads

  1. Autocreate new dataset in connected table?
    By Hyoryn in forum Access
    Replies: 2
    Last Post: 01-08-2017, 05:21 PM
  2. Replies: 4
    Last Post: 07-11-2016, 01:11 PM
  3. Replies: 3
    Last Post: 10-28-2014, 12:23 AM
  4. Using lookup data for table 1 mutible times in table 2
    By mbjazz in forum Database Design
    Replies: 5
    Last Post: 04-26-2011, 01:18 AM
  5. Update statement with linked table join
    By Guigui in forum Queries
    Replies: 6
    Last Post: 09-17-2010, 04:47 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