Results 1 to 6 of 6

Thread: Help With Query

  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Arrow Unanswered: Help With Query

    Hi -
    I am trying to write a query that will give me all of the entries from one table and then add the lines from another table that have the same part number. The problem is that I cannot get the first table to show all line items even though I have a join that states to do this. Also, I know this is a problem but the table that I want to display all the line items will have duplicate part numbers. This is the way it should be. The parts table lists all the parts that should ship and the second table shows the parts that did ship. That is why there could be duplicate part numbers because possibly 4 of one item needs to ship and then I want to record the serial numbers of each item that did ship. If you could look at my sample database, you will find a part and order table and the query I have attempted is named report.

    Any help is very much appreciated!

    B&R
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    [QUOTE=B&R]Hi -
    Hi B&R,

    I d/l your database, but it's not compatible with my version. I have Access2000. Just a note, it sounds a bit like you're breaking the rules for normalization. It has been said often that it's not good to store Duplicate Data in two or more tables. You can have a LookupTable that lists all of the PartNumbers and then add something later to make each one distinct...i.e. like a SerialNumber or something. Then relate your main table to that one. In my program I have many of the same types of items, but add a serial number to make each different once I need to pull and ship one. That way I don't have to create that part number again. It comes from the other table automatically. If you can save your database in A2K I will be glad to look at it and see if I can fix it for you. Remember thought that DuplicateData makes MORE work for you than need be. BUT, each case has it's own quirks too. So what works for you is fine. Just passing some good tips I was taught.

    have a nice day,
    Bud ")

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from PART P
    LEFT OUTER JOIN
    (select *
    from ORDER O
    where orderID = 43) V ON
    P.part = V.part
    ORDER BY P.part, O.serialNum
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Posts
    139
    Robert -

    Thanks for your time and information. It seems like this should work but I copied into the query and I do not get any results?? I do not know what I am doinig wrong. Obviously, you got it to work. Could I ask if you might possibly put it into my test database and attach? That way I will get it right. Appreciate any help!

    Thanks
    B&R

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    This should solve your requirement.
    Attached Files Attached Files
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    139
    Robert - Thanks for re-working that query. It now shows some results. However, some lines show up multiple times. For example: the 90319022 shows up 16 times but only should show up 4 times. I believe adding the serial number to the result causes this problem. Is there a way to fix this?

    Thanks for all the help!!!!
    B&R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •