Results 1 to 7 of 7
  1. #1
    DTK0902 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Northern Ireland
    Posts
    10

    Question If value is true... Cannot be used.

    Hi All,
    First off apologies if this is located in the wrong section, new to this forum, but hopefully I can get some help here. Basically I am currently designing a database within Access 2010. The purpose of this database is going to be a medicine record system.
    I have a table for patients (Patients), A table storing details of medication administered (MedicineAdmin) (This contains a field PatientID to link this to the patient table) and a table for details on the specific medication (MedicineDetails), which again is then linked to the MedicineAdmin table.

    Within the MedicineDetails table there is a yes/no check box under the field heading "Finished" what I had proposed to do was that whenever this box is checked, either manually or via update query, that specific Medicine ID would no longer be eligible for use on a newly input MedicineAdmin record as it is finished.
    I had two possible solutions to this problem I had thought of, please feel free to offer any advice/feedback or tell me if they are ludicrous:

    1. Append Query- using an append query move the specific finished medicine (Selected by inputting details into input box) to a table PreviousMedicines, seems simple enough, however would the link between the medicine and previous records be broken?
    2. Some form of code? I am not sure if this is even possible through access but it would be something like a "if value=true ..." where it cannot be used if the box is checked.


    So this is my new dilemma and hopefully someone out there can help a newbie out.



    Regards,
    Daniel

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by DTK0902 View Post
    :

    1. Append Query- using an append query move the specific finished medicine (Selected by inputting details into input box) to a table PreviousMedicines, seems simple enough, however would the link between the medicine and previous records be broken?
    Yes, the link would be broken.
    Quote Originally Posted by DTK0902 View Post
    2. Some form of code? I am not sure if this is even possible through access but it would be something like a "if value=true ..." where it cannot be used if the box is checked.
    You should perhaps be able to use a query with criteria, to eliminate the "true" values.

    Can you elaborate on "finished medicine" ?

    Thanks

  3. #3
    DTK0902 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Northern Ireland
    Posts
    10
    Hi Recyan,
    Thank you for your speedy reply.

    What i mean by finished medicine is that the medicines which we have used and are now finished, due to the fact that our "Patients" are animals, we must keep the details (i.e purchase date, batch number, expiry date etc) so that is why we cannot simply delete the record once it is finished.

    "You should perhaps be able to use a query with criteria, to eliminate the "true" values."
    But this would need to be able to stop someone selecting the finished MedicineID whenever they are inputting a new record via a form.

    Regards,
    Daniel


  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by DTK0902 View Post
    "You should perhaps be able to use a query with criteria, to eliminate the "true" values."
    But this would need to be able to stop someone selecting the finished MedicineID whenever they are inputting a new record via a form.
    Till some one comes along,
    Not much in to forms, but I think you should be able to have the source for the Medicine field on your Form, set to a query, which gets only those Medicines, which are available for prescribing.

    Just curious, Do you have a separate master table for Medicines & another child table for different batches,... of medicines ?

    Thanks

  5. #5
    DTK0902 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Northern Ireland
    Posts
    10
    "but I think you should be able to have the source for the Medicine field on your Form, set to a query, which gets only those Medicines, which are available for prescribing."
    I think you have just lit the light bulb in my head with that one, I am almost sure you can set properties for drop-down boxes to be selected from a separate query! I must look into this.

    Medicines is stored in a separate table, however batch related information is just an ID field located within it.

    Regards,
    Daniel

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Daniel,
    There are a number of tutorials about comboboxes at http://www.techonthenet.com/access/comboboxes/ that may be helpful to you.
    Can you tell us more about your tables and relationships? Perhaps if we knew a little more about your application and plans, we could offer more focused comments.

  7. #7
    DTK0902 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Northern Ireland
    Posts
    10
    Hi Orange,
    Thanks for the link just had a look through it and bookmarked it for future reference, could come in handy!

    In terms of table layout it is laid out in the following way;

    Table: Animal
    Table: Medication Administration
    Table: Medication Details

    Relationships:
    Table Animal contains primary key "Animal ID", Table: Medication Administration contains foreign key Animal ID, Table Medication Details stores Medication ID as Primary key, and Table: Medication Administration stores Medication ID as a foreign key.

    Hopefully this gives you a rough idea of how it is all linked. If you would like to know anymore don't hesitate to ask.

    Regards,
    Daniel

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A TRUE Keyword Search
    By Meep in forum Queries
    Replies: 72
    Last Post: 05-13-2013, 06:45 PM
  2. Setting AllowZeroLength to True
    By AlexisW in forum Programming
    Replies: 4
    Last Post: 05-28-2012, 01:05 PM
  3. Yes/No True/False
    By DSTR3 in forum Access
    Replies: 5
    Last Post: 12-04-2010, 05:56 PM
  4. If any of the following are true
    By Steven.Allman in forum Queries
    Replies: 7
    Last Post: 08-30-2010, 06:10 AM
  5. True or false
    By tleec in forum Queries
    Replies: 1
    Last Post: 02-01-2008, 10:41 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