Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: Query for a noob

    Ok, I have a table for serialized inventory like this:
    Location|Model|Serial|TimeStamp
    Warehouse|HN9000 Modem|733090|11/23/2010
    Warehouse|HN9000 Modem|733050|11/23/2010
    Warehouse|HN9000 Modem|733757|11/23/2010
    Warehouse|HN9000 Modem|729664|11/23/2010
    Warehouse|HN9000 Modem|745765|11/23/2010
    Mike Merritt|HN9000 Modem|733050|11/24/2010
    Mike Merritt|HN9000 Modem|733757|11/24/2010
    Mike Merritt|HN9000 Modem|729664|11/24/2010
    Mike Merritt|HN9000 Modem|745765|11/24/2010
    JOB#blabla|HN9000 Modem|729664|11/25/2010
    JOB#blabla|HN9000 Modem|745765|11/26/2010
    The "Warehouse" location, as well as all technicians for the "Location" field are in a Technician table.

    We received 5 modems on the 23rd
    On the 24th, checked out 4 to Mike
    On the 25th and 26th, Mike installed 2 of them at custs houses.
    That leaves one left in the warehouse.
    That leaves 2 on Mike's 'truck'.

    I cant for the life of me figure out how to query it to come back with ONLY what the tech or the warehouse should have on hand. I did it in a txt file by finding a serial for a given tech, then searching the remainder of the file for another occurence of the same serial. If the another occurance was found, it ignores this occurance and continues. This way it only shows the last occurance of the serial and ONLY if the last occurance is located to given tech.(or "warehouse")

    Basically, I need the last record for every given serial for a given location IF it hasnt been applied to a job#.

    I know I could simply use one record and change the location, but then I lose my tracability by serial. Example:745765
    Warehouse|HN9000 Modem|745765|11/23/2010
    Mike Merritt|HN9000 Modem|745765|11/24/2010
    JOB#blabla|HN9000 Modem|745765|11/26/2010
    Last edited by thasatelliteguy; 11-29-10 at 14:31.

  2. #2
    Join Date
    Oct 2010
    Posts
    51
    yeah... read that a little closer. Sorry. Give me a second.

    Sorry about the wait and the multiple edits. I read the post again and had my question clarified. Try this

    SELECT Last(Checkout.Location) AS LastOfLocation, Checkout.Model, Last(Checkout.Serial) AS LastOfSerial, Checkout.TimeStamp
    FROM Checkout
    GROUP BY Checkout.Model, Checkout.TimeStamp
    HAVING (((Last(Checkout.Serial))=745765))
    ORDER BY Checkout.TimeStamp;

    Where Checkout is the table where this information is stored. Since Location is linked to other tables you may have to pull some joins in, but the technique should be the same if your joins are correct.
    Last edited by bdabaum; 11-29-10 at 15:06.

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    >>Basically, I need the last record for every given serial for a given location IF it hasnt been applied to a job#.<<

    Dunno why I worded it that way....

    Basically, I need the last record for every serial for a given location...

    Obviously, if it's been applied to a job#, then there will be another record with a location that's NOT my 'searched for location'.

  4. #4
    Join Date
    Nov 2010
    Posts
    6
    Ok, I sorta understand it, and it's close, but not quite there...
    I changed it to:

    SELECT Last(tblSerial.Location) AS LastOfLocation, tblSerial.PartNumber, Last(tblSerial.Serial) AS LastOfSerial, tblSerial.TimeStamp
    FROM tblSerial
    GROUP BY tblSerial.PartNumber, tblSerial.TimeStamp
    HAVING (((Last(tblSerial.Location))="Warehouse"))
    ORDER BY tblSerial.TimeStamp;

    Because I want ALL serial numbers still in that location. (Here, it's "Warehouse") But still getting too many results. For instance, 733090 is still showing, as it should. But so are the others, even though there are records after it showing the part checked out to another location such as 733664, which went to Mike, then a Job. If I put in "Warehouse" I should ONLY get back 733090. If I put "Mike Merritt", I should only get back 733050 and 733757.

    And it's showing duplicates in the same location(Warehouse) where this happened:

    Warehouse|HN9000 Modem|1500827-0006|733757|11/23/2010<scanned in
    Mike Merritt|HN9000 Modem|1500827-0006|733757|11/24/2010<scanned out, but not used.
    Warehouse|HN9000 Modem|1500827-0006|733757|11/26/2010<scanned back into the warehouse
    John Moore|HN9000 Modem|1500827-0006|733757|11/27/2010<scanned out
    DSS9999999|HN9000 Modem|1500827-0006|733757|11/28/2010<put on job


    Location|Description|PartNumber|Serial|TimeStamp
    Warehouse|HN9000 Modem|1500827-0006|733090|11/23/2010
    Warehouse|HN9000 Modem|1500827-0006|733050|11/23/2010
    Warehouse|HN9000 Modem|1500827-0006|733757|11/23/2010
    Warehouse|HN9000 Modem|1500827-0006|729664|11/23/2010
    Warehouse|HN9000 Modem|1500827-0006|745765|11/23/2010
    Mike Merritt|HN9000 Modem|1500827-0006|733050|11/24/2010
    Mike Merritt|HN9000 Modem|1500827-0006|733757|11/24/2010
    Mike Merritt|HN9000 Modem|1500827-0006|729664|11/24/2010
    Mike Merritt|HN9000 Modem|1500827-0006|745765|11/24/2010
    JOB#blabla|HN9000 Modem|1500827-0006|729664|11/25/2010
    JOB#blabla|HN9000 Modem|1500827-0006|745765|11/26/2010
    (I tried to make the table more accurate this time so there wouldn't be so much confusing 'translation' to my actual dbase)
    Last edited by thasatelliteguy; 11-29-10 at 16:04.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT Max(Inventory.TimeStamp) AS MaxTimeStamp, 
           Inventory.Serial, 
           Last(Inventory.Location) AS LastLocation, 
           Inventory.Model
    FROM Inventory
    GROUP BY Inventory.Serial, 
             Inventory.Model
    HAVING ((Last(Inventory.Location) In (select tech_name from technicians)))
    ORDER BY Inventory.Serial;
    Have a nice day!

  6. #6
    Join Date
    Nov 2010
    Posts
    6
    THANK YOU!! You are a god among men!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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