ATTN: database gurus *HELP! URGENT!*

Discussion in 'OT Technology' started by Black Light, Feb 11, 2004.

  1. Black Light

    Black Light Guest

    How do I define that a certain field in table 1 can have multiple values from table 2. Where should I store this data if table 2 should be read-only? :hs:

    I assume that this data will be collected on a subfrom of the form with table 1 as a recordsource. Help me understand how the data should be related in terms of relationships and keys on each side :confused:
     
  2. Black Light

    Black Light Guest

    I'm thinking of using intermediate subtables somehow, but it's not very clear to me :mad: :wtc:
     
  3. Juggernaut

    Juggernaut Guest

    1) the field cant be a primary key, so use the lookup wizard to make it reference another table

    2) if you want... you can create a temp table and then do a dlookup("fieldhere") in an insert query to put the data from table 1 into temp table 1
     
  4. Black Light

    Black Light Guest

    I'll try to be more specific:

    Table 1 has "Also Related To" field. Table 2 contains all "Primary Categories". User must be able to select 1 or more items from table 2 and store that data so that it can be retrieved associated with Table 1's primary key field.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Table 1 can't have multiple values in a single field. The value can be chosen from the list of choices in table 2, but you can't select choice 1, 4, and 7 from table 2 as the field value in table 1.

    In the traditional example of customers and orders, typically there are 3 tables:

    Products
    Orders
    Customers

    Inside of products you would list all your products and prices and then autonumber a field called ProductID. Inside of Orders you would have quantity and product ID as fields, as well as a CustomerID and an autonumbered OrderID. Inside Customer you would have all of your customer fields (phone number, name etc...) and an autonumber CustomerID.

    Then you design your form you base it on the Customers table. Then you add a subform to that form that is based on the order table. The linking field is CustomerID. Inside that subform, you would create a combo box that extracted the ProductID from the Products table.

    For every customer you could have multiple orders and for every order you could have multiple products on order. The trick to linking all this information together is to ensure that each table has an identical fieldname that joins the two. In the above example, Customer ID is in both the Order and Customer tables, tying them together.
     
  6. Black Light

    Black Light Guest

    I'm pretty sure that I will need an intermediate table. I need a way to somehow associate it with Table 1 AND Table 2. Using lookup will create a combo box and it will store a single item, but I need to store multiple items. Do I make any sense, or is my brain completely fried? :hs:
     
  7. dimins

    dimins I'll bring the beers ... OT Supporter

    Joined:
    Feb 13, 2002
    Messages:
    3,234
    Likes Received:
    0
    Location:
    Long Island, NY
    What type of database are you working with?

    Let me see if I understand your issue, because to me it seems too simple. You have a TABLE1 which has a unique Primary key field? And you have a TABLE2 that also has a unique Primary Key field and you want a 1 to many relationship between the two?

    If I follow so far, I'd use a link table (TABLE3) where you insert the TABLE1 and TABLE2 primary keys.

    I don't know if that is what you're looking for.
     
  8. Black Light

    Black Light Guest

    Check my understanding:
    - my "Primary Category" table (table 2) would be like "Products" table
    - my "Issues" table (table 1) would be like "Customer" table
    - my "Selected Categories" table (table 3) would be like "Orders" table

    Correct? :confused:
     
  9. Black Light

    Black Light Guest

    It's a MS Access database. The client would prefer minimum code and as much as possible written in form of table relationships and queries (vs. doing most work in VBA code).

    Yes, table 1's primary key is "Issue Number" field. Table 2's primary key is "Primary Category Id". One-to-many won't really work here, since I must be able to associate everything with the primary key of table 1. I need an intermediate table (table 3) to hold data with a foreign key based on Table 1's primary key "Issue Number". Table 3 will be used as a subform related to the "Issues" form via the "Issue Number" key.

    I hope I'm making sense :hs:
     
  10. Juggernaut

    Juggernaut Guest


    Yes what you need is a LINKING table. This has the primary key of 2 different main tables that allow them to be able to reference each other.

    You can store multiple of the same type of information, as long as its primary key's are not the same. So with that said, try storing the information with a unique idenfier like a product number ( that's an autonumber), yet has primary key specifics into another table that just stores the information. A simple insert query should do the trick
     
  11. Black Light

    Black Light Guest

    This is probably something very simple, I'm just not seeing it clearly because my brain is fried :wtc:
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I don't see a third table being required, or a lot of coding either. Maybe I don't understand the question but I can't imagine what you're trying to accomplish would require a separate table to join the other two tables - you just need to have table 2 to have an identical fieldname of long as the autonumber field in the first. When you create the subfrom it will ask how to join the two and you select those two fields. When you add the items into the bottom, the joining field on the subform will automatically be filled in with the original autonumber field
     
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    As for a linking table, the only time I can think of using a linking table was actually a linking query which was because the structure of the two tables couldn't be changed and the linking fields were different variable types.
     
  14. Black Light

    Black Light Guest

    Ok, I did what u guys suggested (dual related keys), but it's seeing nulls in one of the fields during data entry and errors out :mad: :wtc:
     
  15. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    How bout you just tell us the structure of the tables, and what you are trying to do rather than confuse us with vague details.
     
  16. Black Light

    Black Light Guest

    Table 2 is read only :hs:
     
  17. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Okay then, then you're back to my original example - 3 tables - the product table could be read only and the order table selects 0 to infinity of products you want.
     
  18. Black Light

    Black Light Guest

    Wait! I got it working... well, mostly... Autonumber from Table 1 ("Issues") is not being passed to Table 3 ("Selected Categories") without making an entry in at least one of Table 1 fields. Any way around that? :x:
     
  19. Black Light

    Black Light Guest

    So far your suggestion is extremely close to actual solution, but I'm still having some minor issues :hs:
     
  20. Black Light

    Black Light Guest

    I got it! :bowdown:

    Here's how:
    - Table 2's "Primary Category ID" primary key was made Table 3's foreign key with a dropdown combo box
    - Table 1's "Issue Number" primary key was made Table 3's foreign key (to maintain relationship in subform)
    - Table 3 became a subform in "Issues" form (based on Table 1)

    Everything just fell right into place when relationships were fixed :bowdown:

    I :love: u, guys! :kiss:
     
  21. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    No - because the autonumber field has to have a value to pass right? It doesn't evaluate that number until you start editing the record. The way around it, although I don't know why you'd want to, is to simply enter a blank in one of the fields using VBA.
     
  22. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
  23. Black Light

    Black Light Guest

    I had to make the subform invisible until the Autonumber was triggered :hs:
    I could also have dummy data written then erased on form current event :dunno:

    If u can think of a better solution let me know.
     
  24. Black Light

    Black Light Guest

    neither entering blank field or hiding subform is acceptable... I need a different method :hs:
     
  25. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Then I'd say what you are asking for is impossible. You can't link the two forms together without a unique key on the first page, and that unique key isn't generated until you start entering data on the form. Unless you want go with the pain the ass method of an unbound subform and not linking it to your main form, your options are limited.
     

Share This Page