Results 1 to 8 of 8
  1. #1
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83

    Create new fields

    Need some direction on what to even search on to accomplish this.



    I have a table with a PK - Product_ID and a field mfg_issues

    One Product_ID could have up to 4 issues, so there are 4 lines in this particular query for that one Product_ID. If I add in PO_qty from another table I am overstating my qty by the number of extra rows I will have in my query.

    I would like to create a query with 4 columns named - Issue1, Issue2,Issue3, Issue4 and pull each of these issues into it's own column.

    Is there a name for what I'm trying to do?

    Goal is to have only 1 line with all the other attributes for each Product_ID in my query.

    Thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Assuming one product can have many issues, and that each issue can be attributed to many products, the PROPER way (in keeping with the spirit of "relational database") is to arrange your db with 3 tables, Product, Issue, and ProductIssue. This is a common example of a many to many relationship. In your relationship window, you will do 2 one-to-many relationships, enforcing referrential integrity. Your Product table, will have each product in it, listed once. your Issue table will then have your possible issues, listed once. Your junction table, ProductIssue, will contain 3 columns. 1, ID 2, ProductID (fk) 3, IssueID (fk). here you will have all the combinations. You will THEN query off this junction table.

    There are sloppy ways around it but you'd just be creating more headaches for you at the end.

  3. #3
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    Thanks for the reply. Had I built the tables, that is definitely what I would have done, but, unfortunately, these are Oracle tables that I don't control with no chance of my getting any kind of "normalization" fixes on them for quite some time if ever.
    The data changes constantly so not sure how to approach it. Is there a way to replicate your suggestion through other queries? Long way around I know but no choice at this point.

    Thanks!

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    What form is the table(s) currently set up? do you have:
    ProductID IssueID
    1 1
    1 2
    2 1
    2 2
    3 1
    3 2

    like that?

  5. #5
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    Exactly

    Prod_ID Constraint
    100 Funding
    100 Weather
    100 Timing
    200 Weather
    200 Misc
    300 Resources
    300 Personnel

    Would like to have

    Prod_ID Constraint1 Constraint2 Constrint3 Constraint4
    100 Funding Weather Timing
    200 Weather Misc
    300 Resources Personnel

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    EDIT: see this thread as well. https://www.accessforums.net/databas...cords-102.html

    I'm not the best in VBA so I can't give you the exact code but what I would think would end up happening is that you would do a For loop. Here's the logic I'd go with. Hopefully someone else can provide some code.

    For Each Product
    store each issue into another variable,
    write variable into next column
    when Product changes, start new record

    hopefully you can follow the logic

  7. #7
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    That's what my initial thinking was but all I could find were examples of functions containing dates and nothing like the data I was trying to work with. I will keep up my search, thank you!

  8. #8
    thart21 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    83
    I ended up finding some code that would concatenate the data in the field/rows into one row with commas in between the data. Works for now, but still looking for my original solution.

    Thanks

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

Similar Threads

  1. Replies: 5
    Last Post: 03-20-2010, 08:30 AM
  2. How do I create a name search ?
    By nightviperdark in forum Access
    Replies: 1
    Last Post: 11-23-2009, 09:53 AM
  3. Replies: 3
    Last Post: 08-10-2009, 08:33 AM
  4. Need to create a new db
    By ori in forum Access
    Replies: 5
    Last Post: 05-26-2009, 05:24 AM
  5. Create the link
    By accessman2 in forum Access
    Replies: 0
    Last Post: 03-13-2006, 01:16 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