Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query still shows removed items! (Access 2000>)

    Hi all,
    The DB I have attached keeps track of computer equipment assigned to a store. All stores have PC equipment that is assigned to them. From time to time the equipment either becomes faulty or is upgraded. The DB must keep a track history of the where-abouts of the equipment.

    At present I am trying to fix a query that that is supposed to show the stores CURRENT equipment list. The problem I have is that the equipment that was removed from that store is STILL showing in the queries result.

    I have my suspicions regarding the design relationships of the DB...and I think that this may be the underlying problem that is spilling over into the queries result. Could someone please help me in solving this problem. My thoughts are in a knot, and I can't think straight anymore.

    Attached is a zip file that contains a smaller version of the DB. Also in the zip is a Excel Spreadsheet that contains the result of the problem query. I have marked the records that should NOT APPEAR in red. (These records represent equipment that has been removed, but still shows in the query!!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    TIA and for assistance!
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    Is there any way in which you can see in the tables in the database which items are removed, and if so, how?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    > Is there any way in which you can see in the tables in the database which items are removed, and if so, how?

    If you open the AssetsJobList table, it shows the InsertSerialNo and RemovedSerialNo. In other words, this table is tracking the job. A consultant goes to the store and removes a piece of equipment (captured by the remove serial no.), and then if necessary installs the new piece (captured by the install serial no.).
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    OK, but how EXACTLY can I determine from this whether an item is removed (please remember that you are familiar with the database, we aren't)

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    Lets say a consultant goes to the store called Ashley Ward Office (as in the query!) The consultant will capture a new record in a form (for size purposes I did not include the form in the zip!). The form is designed to supply the Store name and the fields of the AssetsJobList table. The consultant will select the store from a drop down, and then add the equipment to the removed assets area (which marks it as removed for that job), or adds a record to the installed area (which marks it as installed for that job). See the image of the form below!

    I guess this is the design flaw that I mentioned in the starting post! There is no other way to know if an item is removed or not except to see if the install/remove field in the AssetsJobList table is null for that job!

    Can you advise if the sturucture /relationships must change to capture this better?
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    That is not necessarily a problem, but as you can see in the query, the RemSerNo field is blank for all jobs associated with the Ashley Ward Office (CDU=432), so according to the definition you just gave, the items aren't removed. What makes YOU decide that some of these items have been removed?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    Hans,
    Here is a new modified version. I changed the AssestsJobList table to include a field that specifies if equipment is removed or installed. I also created a new query that will record this info, but the query still has issues. If you attempt to add a new record into the query, it complains that the recordset needs to be saved first...???

    What is wrong now?

    Thx for your help!
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    I just noted that if you switch the Serial Number and the CDU field around, the query works. Field order is: Serial Number, CDU, ...

    OK...with this breakthrough...let me see if the DB dynamics are working now!
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    Why is the SerialNumber field blank in all records in (Copy Of) AssetsJobList?

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    Thats a new field that I created. I set it up to relate to serial number in the equipment table. I figured that I'd change the relationship to this so that I could specify the Serial Number and specify the "Action" (Rem/Inst)...with the date.

    Do you think this structure/relationship will work better? It now captures the status of the equipment!

    Any other suggestions?
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    You have a one-to-many relationship from Equipment to Copy Of AssetsJobList, i.e. there can be many jobs in Copy Of AssetsJobList with the same SerialNumber, i.e. one piece of equipment can be assigned to multiple jobs. I'd say that is not what you intend. Isn't is the other way round, i.e. several pieces of equipment can be assigned to one job? If that is correct, you wouldn't need a SerialNumber field in Copy Of AssetsJobList, but a jobNumber field in the Equipment table instead, with a one-to-many relationship the other way round.

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    Hans...I've still not managed to solve this problem. I've tried your suggestion of changing the way the tables are related. I guess I'm also not too familiar with this clients DB. I am going to recreate a small DB that will simulate this setup and play around with the joins and see if I can make sense of this relationship. I will post the DB and either get further comments or advice if needed. I think a small DB with my own dummy data will help me to think clearer.
    I will post again next week as I will not have access to WOPR until Monday! (I hope I would have solved the problem by then!)
    Thx for your help so far!
    Cheers
    Regards,
    Rudi

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    Hi all,

    I'm still fussing with this DB. I still do not have a solution. The attached DB is a simulated one with dummy data that I created to attempt to work out how to design the tables and relationships. I thought that I would be able to solve it if I just recreate it and add some data that makes sense. I have not come further as I just can't figure out how to relate the tables to do what I need it to do???!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Heres the scenario again:
    The DB holds shops (Locations) that contain computer equipment. This equipment is rented out to them. The (Equipment) table is a inventory table of ALL equipment that can be hired out! The (AssetJobList) table is a junction table that stores the movement of the equipment and keeps a history of that movement. (Movement is described as the installation or removal of equipment within the shops (locations) due to upgrades or faulty equipment.)
    NOW...here is my <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    At any time I would like to query for the CURRENT list of equipment at any shop. This list must show me only equipment that is at the shop at that time. (Equipment that was assigned to the shop that has since been removed due to faults etc...must NOT show!!!)

    Now to do this is my issue and frustration. Can anyone please help me solve this. If the DB tables must change...so be it. If the relationships must change...so be it. But how do I get to the query result. Your advice and support will be invaluable to me!
    TIA
    Regards,
    Rudi

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

    Re: Query still shows removed items! (Access 2000>)

    Is it possible that the same piece of equipment is issued to the same location more than once? For example, consider the following scenario:
    - installed on June 12
    - removed for repair on June 25
    - installed again after reparation on July 3
    If this can happen, the primary key in AssetJobList should include the DateInstalled field (which should never be blank - how can a piece of equipment be removed if it was never installed)

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query still shows removed items! (Access 2000>)

    >> The primary key in AssetJobList should include the DateInstalled field (which should never be blank - how can a piece of equipment be removed if it was never installed)

    Your judgement is correct. A piece of equipment can be removed and re-installed at the same location!
    I see your point. But if that is the case, is this how I will capture a removal record:
    I will have to query to locate the piece of equipment, and once found add the remove date in the found record! Once the equipment is fixed, I create a new record with the Shop, Equipment and Install Date (to satisfy the P/K). Is this more or less what you were thinking?
    Regards,
    Rudi

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
  •