Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Location
    San Diego
    Posts
    9

    How to create a query for logical combo??

    Or maybe table structure needs to be changed.

    I have a table named ProgramCodes and it may need to be redone.

    the fields in this table are:
    1. Guitar (item)
    2. Option (item)
    3. Code (code the user enters for the Guitar and Guitar w/ Option(s)
    combinations.

    So I've created a form and a sub form for the user to be able to enter the
    Codes per Guitar and Guitar w/ Option(s)

    The main form is just the Guitar item the user can search to find the Guitar
    itemto start entering the Codes and combinations by entering the Option items


    for the Guitar combo. The record source is from the Guitars table (which is
    just all the guitar items)

    The sub form is all the fields of the ProgramCodes table.
    Linked by the Guitar item.

    Please help me how to achieve the following
    as noted:
    Guitar is an Item
    Option is an Item
    Code is what the programmer uses for their understanding of how to program
    their machines to make the guitar with options if any.

    This field (Code) in the ProgramCodes table (and form) is a free-from and
    just a text file for the programmer (user) to enter the code(s) associated to
    the guitar and guitar with option combos.

    So someone purchases a guitar and select several options or not, there will
    be various Code (programming codes) to build that guitar is the guitar item
    is just the bare guitar (skeleton / shell).
    The options make up the different features on the guitar. The programming
    codes make these features function in their special ways.

    How can the Codes get entered into a table or tables if the current
    ProgramCodes table is insufficient so that when you query on, say 127R-RB,
    you get all the invoices with DC135 guitar item with ONLY RB as an option
    item.

    or if you query on an invoice with DC135 to see the programming code you get
    the result of just 125R and 135T IF and only IF that item on the invoice had
    no option.

    or if you query on an invoice with DC135 and the invoice has options 26 and
    RB that it'll only show 127R-RB and 136T-RB as my Code result BUT it can't
    show the Code for just the 26 or just the RB, it needs to show the combined
    Option's Code.

    here are the results that need to be obtained
    DC135 is a Guitar item:
    DC135 without any Options should be assigned Codes = 125R and 135T
    DC135 with RB as an Option and should be assigned Codes = 127R-RB and 135T-RB
    DC135 with 26 as an Option and should be assigned Codes = 125R and 136T
    DC135 with 29 as an option and should be assigned Codes = 125R and 139T
    DC135 with 26 & RB are Options and should be assigned Codes = 127R-RB and
    136T-RB
    DC135 with 29 & RB are Options and should be these Codes = 127R-RB and 139T-
    RB (it can not display any of the other codes above, it can only display this
    code for this combo)

    I hope i made sense. I don't want to complicate this anymore then it is by
    itself.

    The other tables with Invoice information is insignificant for what needs
    happen here with the Codes in ProgramCodes table.


    I currently use these:
    and the result is not exactly as needed.
    This of course only gives me the result when the Guitar does not have any Options. Which is ok, BUT i need the other Guitars with Options to show WITH the correct combinations so the correct Code for the combination shows in the result.

    ("SELECT Code FROM ProgramCodes WHERE Guitar = """ & [GuitarItem] & """ And Option Is Null ")

    using:
    SELECT GuitarProgramCodes.InvoiceDate, GuitarProgramCodes.InvoiceNumber, GuitarProgramCodes.GuitarItem, Max(Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar = """ & [GuitarItem] & """ And Option Is Null ")) AS Codes
    FROM GuitarProgramCodes
    GROUP BY GuitarProgramCodes.InvoiceDate, GuitarProgramCodes.InvoiceNumber, GuitarProgramCodes.GuitarItem
    ORDER BY GuitarProgramCodes.InvoiceNumber;


    and

    This doesn't show any Guitar with null Option and not showing the correct Code for the combinations of the Options.
    ("SELECT Code FROM ProgramCodes WHERE Guitar & Option =""" & [GuitarItem] & [Option] & """")

    using:
    SELECT GuitarProgramCodesDetails.InvoiceDate, GuitarProgramCodesDetails.InvoiceNumber, GuitarProgramCodesDetails.GuitarItem, Max(Concatenate("SELECT Code FROM ProgramCodes WHERE Guitar & Option =""" & [GuitarItem] & [Option] & """")) AS Codes
    FROM GuitarProgramCodesDetails
    GROUP BY GuitarProgramCodesDetails.InvoiceDate, GuitarProgramCodesDetails.InvoiceNumber, GuitarProgramCodesDetails.GuitarItem
    ORDER BY GuitarProgramCodesDetails.InvoiceNumber;

  2. #2
    Join Date
    Jul 2006
    Location
    San Diego
    Posts
    9

    examples

    Am having issues and will try this post in this format and see if anyone can
    help.

    ok, this is all the possible codes for AE185.
    AND depending on the Options selected for AE185 on invoices, the Codes will
    differ.
    The ComboID is the differentiator.

    Guitar Option Code ComboID
    AE185 185RR 1
    AE185 186RHT 1
    AE185 187RT 1
    AE185 38 185RR 2
    AE185 38 186RHT 2
    AE185 38 187RT38 2
    AE185 BB 185RR 3
    AE185 BB 186RHT 3
    AE185 BB 188RT-B 3
    AE185 38 185RR 4
    AE185 BB 185RR 4
    AE185 38 186RHT 4
    AE185 BB 186RHT 4
    AE185 38 188RT38B 4
    AE185 BB 188RT38B 4

    so for the Code that repeats in the ComboID is only because the Option(s)
    also requires that Code as well as the other Codes.

    for instance, if the invoice ONLY has Option 38 from the Options defined for
    Codes, then the result would be:
    Code:

    AE185 38 185RR 2
    AE185 38 186RHT 2
    AE185 38 187RT38 2

    AE185 BB 185RR 3
    AE185 BB 186RHT 3
    AE185 BB 188RT-B 3

    AE185 38 185RR 4
    AE185 BB 185RR 4
    AE185 38 186RHT 4
    AE185 BB 186RHT 4
    AE185 38 188RT38B 4
    AE185 BB 188RT38B 4

    Code:

    Invoice Guitar Codes
    123456 AE185 185RR 186RHT 187RT38 <-- say this
    invoice had Option 38 that was the only match
    234567 AE185 185RR 186RHT 188RT-B <-- because BB
    was the only one that matches
    345678 AE185 185RR 186RHT 188RT38B <-- because both
    38 & BB matches

    So guess the question is how do I write a query or function (VBA) to be used
    in a query to get the results for ONLY the matching options so the correct
    codes for just these options are displaying?

    It's difficult to me in figuring that out. how to make sure that the result
    does not show the ALL the codes that match but ONLY the codes in the
    combinations. Again, hence the reason why I got to the point of having
    Combination IDs to differentiate that.

    hope I made sense here. Not sure how else to ask the question other then to
    try and provide examples :-(
    let me know how else to explain if this is not helping.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-08-2013, 03:37 AM
  2. Replies: 0
    Last Post: 08-17-2008, 12:19 PM
  3. Create the link
    By accessman2 in forum Access
    Replies: 0
    Last Post: 03-13-2006, 01:16 AM
  4. Auto-Create a Table
    By Mxcsquared in forum Forms
    Replies: 3
    Last Post: 01-28-2006, 11:36 PM
  5. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 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