Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 98
  1. #31
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That input mask was set to save the hyphens so future inputs will have hyphens, however, existing values don't actually have hyphens. Data will be inconsistent.



    Now have to run UPDATE query to set value of SimStatus.

    Don't have database with me now and can't remember if the spreadsheet had duplicate sim numbers. If there are duplicates in ASE_Units table do a 'SELECT DISTINCT SimNumber FROM ASE_Units;' query. Then can just copy/paste that entire column over to the Sim_Serial table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #32
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Hmm well the hyphens seem to be working :/
    I know you've given me references and tried explaining the Update queries but I still don't get it. Can't get it to work
    if i just put "assigned" (Update toin SimStatus . It will change all the Status's to "Assigned" won't it? Then what about the records that don't have SimNumbers? - they need to remain as "Unassisted". Somehow

  3. #33
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Update Query Worked! Exactly what i wanted.
    Thank you

  4. #34
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Hi June,

    Desperately need your help again
    Somewhere in the DB a formula isn't working or has been damaged etc.

    I doubt if you can remember helping on this in the past but basically the problem is the updating of fields again. Such as:
    In the ASE_Units Form when a ASE Unit and SimSerialNumber is present. The SimStatus changes to "Assigned" and
    when the SimSerialNumber is Removed from the ASE Unit - The SimStatus changes back to "Unassigned".

    However this isn't working anymore. the SimStatus stays as "assigned"
    The Sim_Serials Table doesnt update with the New Status.

    I have a Sim_Serial Subform hidden on that form where the Status gets its update from. However something isnt working.
    If you go to the Sim_Serials table, the meant to be "Unassigned" sim is Still "Assigned". that goes for the Sim_Serial form aswell.
    I used the SimSerialNumber 11111111 as my Dummy testing.

    Can you help at all?

  5. #35
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Right,
    Sort of found an earlier update where the SimStatus works on the ASE_Unit form. However the Sim_Serials table nor Sim_Serials form updates.
    Please help?

  6. #36
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    All this updating to/from 'Assigned' and 'Unassigned' is really unnecessary. You can determine if a SIM is assigned to an ASE by a query. Review this:

    SELECT Sim_Serials.SimSerialNumber, IIf(IsNull([ASEID]),"Unassigned","Assigned") AS Status, ASE_Units.ASEID
    FROM Sim_Serials LEFT JOIN ASE_Units ON Sim_Serials.SimSerialNumber = ASE_Units.SimSerialNumber;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #37
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Hmm ok. I'll give this ago.
    Where do i put the code? Will this help update the Sim table aswell?

  8. #38
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You miss the point of the query. It does not update anything. It makes updating Sim_Serials table unnecessary. The SimStatus field can go away.

    It is just an SQL SELECT statement. Paste it into the SQL View window of a new query and run it. See the results.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #39
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    ok its done - what do i do with it exactly? does it go into the ASE_Unit form and have the Simstatus addressed to it? If so, how do i do that?
    The User would like to see the "assigned" and "unassigned" status to show if a unit/sim is attached or not for their own benefit.

  10. #40
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe I just don't understand what 'Assigned' and 'Unassigned' mean.

    1. ASE_Units_form RecordSource is ASE_Units table

    2. Each ASEID has a single SimSerialNumber

    3. 1 and 2 mean that the ASE_Units_form can display only Sim_Serials that are associated to an ASEID

    4. Therefore all the Sim_Serials numbers on the ASE_Units_form are 'Assigned' to an ASEID

    5. If the SimSerialNumber field is blank then no SIM has been associated to this ASEID

    6. No5 can be used to calculate a status: IIf(IsNull(SimSerialNumber), "Unassigned", "Assigned")

    7. The query I gave you can be used to identify which SIM_Serials have not been associated to an ASEID. Right now it shows that they have all been associated except for 2 of the 3 dummy records. You can probably delete those 3 and the dummy ASEID record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #41
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Thats correct. Does it make much sense?

    To add to that:

    -Sometimes a ASE_Unit will have a SimSerialNumber with it. Both are entered into the ASe_Unit_Form. The SimSerialNumber goes into the ASE_Units table and the Sim_serials table through the ASE_Units_Form.

    -Any Loose/Unassigned SimSerialNumbers will be entered into the Sim_Serials form.

    -If a Sim gets damaged, the SimSerialNumber is "cancelled" through the Sim_Serials form and history tracked.
    On the ASE_Units_Form if you look up the Cancelled sim - it will show the Cancelled status highlighted in Red with the ASE_Unit attached to it. (Showing it needs a new Sim). Delete the SimSerialNumber from the ASE_Units_Form, which changes the status to "Unassigned", Look up a Free/Unassigned Sim from the Sim table/report, type in the new Unassigned" SimSerialNumber in, the Status changes to "Assigned".

    Thats basically the Procedure of things. very seldom a cancelled Sim will be seen. More like replaced with another. but you get the picture..I hope.

    So the SimStatus is quite important for what i want it for.

  12. #42
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It makes sense from one point of view. User needs another flag besides the empty SimSerialNumber box. Wants to see the "Assigned" or "Unassigned" status. So be it. You can even use conditional formatting to make the box and/or its text bright red when it displays "Unassigned". Could just as easily use this visual cue on the empty SimSerialNumber box.

    With my suggestions, UPDATE queries are not needed. Also, the hidden Sim_Serials subform is not needed. Things are much simpler.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #43
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    ok, I didnt think the Update queries was neccessary. I used the subform to populate the Sim_table with the SimSerialNumber as well as it's status update. You got an easier method?
    Can you guide me through what to do from then on?
    Also is the Status able to change in the Sim table? I did it somehow before.

  14. #44
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you still want the status field in the Sim table we are back to the UPDATE sql action.

    Apparently I still haven't made my point clear.

    With my suggestions the Status field in the Sim table is not needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #45
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Right ok.
    If we do it your way...Can a report still be made to show the Different Sim Status's etc if we take the Status out the Sim table? and we'd still be able to tell if a sim is assigned or not?
    You're way is probably a much neater way of doing things

    But


    I just feel the way I have it at the moment is near complete. don't want to change it to much. Just want to finish it off asap if possible, with your help. If it can't be done, then look at your method?

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-13-2011, 08:45 AM
  2. Replies: 4
    Last Post: 05-28-2011, 01:20 AM
  3. Replies: 8
    Last Post: 11-12-2010, 10:55 AM
  4. Changing field data into labels on the y-axis
    By slaterino in forum Access
    Replies: 1
    Last Post: 09-28-2010, 03:15 PM
  5. Replies: 5
    Last Post: 08-20-2010, 06:40 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