Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help Need (2003)

    I've been asked to create a database for keeping track on the location of tools. The form will be basic with just the tool number and 4 tick boxes for the operator to tick where the tools are and the date of the transaction. They want to open the form search for the tool number, and see immediately where the tool is. The problem is that they also want to be able to be able to see the history of it's movements if neccessary. I know I could do this by using a subform etc, but I don't want to complicate the form too much. Has anyone any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    If you want to keep the history of where a tool is, you will need two tables:

    tblTools with the basic info about each tool. Primary key is an ID number, e.g. ToolNumber or something like that.
    tblHistory with the information about where a tool was on different dates. Linked to tblTools by the Tool ID number.

    A main form based on tblTools and a subform based on tblHistory is the "standard" way of displaying this. You can make the subform display the data in descending date order, so that the most recent record for a tool is displayed when the user moves from record to record in the main form.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    Thanks Hans. I thought that would be the only way, but wondered if there was a way of displaying the current movement.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Help Need (2003)

    As always, think through the tables you need, then how to display the info on forms.

    You clearly have at least two tables : a tool table , with a number and probably a description, and a tools location tables, with some ID field as a key, a toolID, a location and a date. It sounds like you have four locations to me, which I think would justify a locations table .

    So to display the location history, you could have a tool form with a locations subform, or just a continuous form based on tool locations, with a combo box in the header that allows you to filter by tool. If you set the order to descending by date, then the latest date would be at the top, but the blank new record would be at the bottom. If you didn't like you might need to pop up a separate form to add a new location record.
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    Thanks to you both, I've got it working and keeping an history. Just one more thing. Could I put some sort of code in the On Open property of the Subform, to go to the last recorded date?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    The On Open event of the subform only occurs when the main form is opened, not when the user moves to another record in the main form. As indicated by both me and John Hutchison, you can set the record source of the subform to a query that is sorted descending on the date field.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    Thanks, that works fine, but there's more. I've been asked now to create a report to show all tools that have been put on hold. I've tried creating a query to give me the max date, which will give me the current status, and another with all the details on, with the date fields being joined. The criteria was only where the status is on hold, but it doesn't work. Is this because I have ID as an autonumber?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    Can you post the SQL for the query or queries you have now, or post a stripped-down and zipped copy of the database, with an indication of which queries are involved?

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    Here's the SQL:-

    SELECT [Current Date qry].Tool_Number AS [Current Date qry_Tool_Number], [Current Date qry].MaxOfDate, [tblLocation Query].ID, [tblLocation Query].Tool_Number AS [tblLocation Query_Tool_Number], [tblLocation Query].Date, [tblLocation Query].Die_Store, [tblLocation Query].Wax_Room, [tblLocation Query].Award, [tblLocation Query].Award2, [tblLocation Query].Peppers, [tblLocation Query].Peppers_Location, [tblLocation Query].Other_Toolmakers, [tblLocation Query].Toolmakers_Name
    FROM (tblTool_Number LEFT JOIN [tblLocation Query] ON tblTool_Number.Tooling_Number = [tblLocation Query].Tool_Number) INNER JOIN [Current Date qry] ON [tblLocation Query].Date = [Current Date qry].MaxOfDate;


    I haven't bothered with the On Hold status yet, I just want to get the Max Date working first

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    Thanks. What exactly is the problem with this query?
    - You get an error message - if so, what does it say?
    - It returns incorrect records - if so, in what way? (too many, too few, none at all, ...)

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    It returns too many records, not just the latest date.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    What happens if you change LEFT JOIN to INNER JOIN?

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    Sorry about the delay, busy with other projects. Yes, that's worked. As usual, they want more. I have another table with tool numbers and locations, and they want the locations to appear in the form for each tool. The problem is, for every tool, there will be a history of the movement, but I want to tool location to appear. DLookup didn't seem to work.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help Need (2003)

    Can you provide a more complete description, or post a stripped down copy of the database with an explanation of what the relevant tables, queries, forms etc. are?

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help Need (2003)

    I've reduced the database as much as I can for it to make sense, and it's still too big to post. Basically the form is made up of two tables (tblTool_Number & tblLocation) and a query opens the file at the latest date, as you instructed me how to do. There is another table which holds the warehouse locations. What I want is for the warehouse location to appear in the Warehouse field on the form, for the correct tool. As you can see from the attatched image, using lookup produces an error.

Page 1 of 2 12 LastLast

Posting Permissions

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