Results 1 to 6 of 6
  1. #1
    Waubain is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    25

    Add/Append multiple records at one time to a table

    I receive shipments of research medication. Depending on the study each "kit" can have a different number of medication bottles ranging from typically 1 to 12. Each kit has a unique number and within the kit each bottle has a unique number. One or more bottles from a single or multiple kits may be dispensed to a research person.

    Here is reason I am looking for a better solution: the person brings back the bottle/s and I need to put it back into the kit it came from. Since the bottle does not state which kit it came from I had to do extra work to track this info down.

    I built a very simple table=tblContents with two fields: KitNum and BottleNum which has made it easy to find the kit based on the bottle when the person returned it. The problem is entering the data. Today I got in 10 boxes with 12 bottles each which means 120 entries.



    What I would like to do is enter the KitNum once and then have multiple fields on the same form, so in todays case this would append 12 records at one time to tblContents. If one box had only 6 bottles then it would append six records. The number of bottle fields would either have to be able to expand or not add a record if the field was Null.

    I tried some Cartesian product queries, but got not no where.

    Any thoughts would be appreciated.

  2. #2
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Your kit number may have many corresponding bottle numbers,

    which points to the table relationship needed. Use a form/subform arrangement so that you enter the kit number on the form and the multiple bottles on the subform.

  3. #3
    Waubain is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    25
    Thanks for the reply. I will have to try it out, but what I cannot see how to do then is search for a specific bottle number. In the subform I would have a autopopulated kit number and then 12 fields named bottle1, bottle2,...,bottle12 for entering into the subform. I understand your suggestion.

    When the bottle now comes back and I go to the search form with two fields kitNum and a bottle field pointed to the subform, which field is the bottle field searching? There are 12 different fields. Thanks.

  4. #4
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    I didn't complete the picture. Sorry.

    Quote Originally Posted by Waubain View Post
    Thanks for the reply. I will have to try it out, but what I cannot see how to do then is search for a specific bottle number. In the subform I would have a autopopulated kit number and then 12 fields (NOT fields, records) named bottle1, bottle2,...,bottle12 for entering into the subform. I understand your suggestion.

    When the bottle now comes back and I go to the search form with two fields kitNum and a bottle field pointed to the subform, which field is the bottle field searching? There are 12 different fields. (Not a good idea.)( Thanks.
    I was conceiving a table-- Kitable which your main form contains, and another table--BotTable which is the basis for your subform. (You might have a third table tying the two together, depending on other info which you collect) . Kitable would have info specific to the kit, e.g. an autonumber ID, another number?, who supplied the kit.... The BotTable would have bottle number, Kitable ID, who it is assigned to, date out......
    Thus the bottles per kit could be open ended. You specify a table relationship where the instance of Kit ID is one to many between Kitable and BotTable.
    When the bottle comes back, the Kit ID will point back to Kitable.

  5. #5
    Waubain is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    25
    Quote Originally Posted by hertfordkc View Post
    When the bottle comes back, the Kit ID will point back to Kitable.
    I understand what you are saying, but when the bottle comes back I do not know the Kit ID, I only have the Bottle ID.

    When entering the shipment I want to list all 12 bottles at once not one bottle at a time 12 times. This is why I originally stated "12 fields". One for each bottle in the shipment.

    Thanks for your ideas.

  6. #6
    hertfordkc is offline 18 year novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    481

    Have you tied your thinking to a particular form?

    Quote Originally Posted by Waubain View Post
    I understand what you are saying, but when the bottle comes back I do not know the Kit ID, I only have the Bottle ID.
    Entering the BottleID would/should bring up the Kit ID.

    When entering the shipment I want to list all 12 bottles at once not one bottle at a time 12 times. This is why I originally stated "12 fields". One for each bottle in the shipment.
    I would use a form/subform with the kit info listed in the form portion and the 12 bottles listed in the subform, probably in datasheet view.

    Thanks for your ideas.
    Good luck.

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

Similar Threads

  1. Append table elements as new records
    By bkirsch in forum Forms
    Replies: 1
    Last Post: 11-16-2011, 01:55 PM
  2. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  3. Inserting multiple records into a table
    By New2Access in forum Programming
    Replies: 1
    Last Post: 07-07-2011, 09:18 PM
  4. Append Query: Time Stamp
    By AKQTS in forum Programming
    Replies: 3
    Last Post: 08-16-2010, 12:34 PM
  5. Referencing multiple records in a table
    By akbigcat86 in forum Programming
    Replies: 14
    Last Post: 07-22-2010, 01:30 PM

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