none
Track the count of records assgined to a specific ID RRS feed

  • Question

  • Hello,

    In the Database I'm developing one of the activities is to assign new records to multiple possible "scan classes"  The Classes are limited to a set number of records per class instance.  Creating multiple classes with the same configuration has been completed in the host system.  What I need to do is to track the number of records assigned to each class instance and when the count reaches the limit move to the next class instance.  I am currently updating the instances via an update query based on a set of known conditions.  I then have to go into the records and count how many records have been assigned to each class, then compare this to the space left in the class instance (I have a separate table with the information about the classes including the instance number, the key configuration data and the count of records assigned to the class instance) and then either accept the changes or go back in and modify these values if the count of the records assigned to the class exceeds the allowable limit.

    I want to automate this process, but I'm not exactly sure of how to proceed.  Do I run a loop in VBA checking each record (I keep them in a temporary loader table for editing) after the query is done and then when the count gets too high have the VBA update the class with the next available class, or is there some other way to automate this that is cleaner?

    Thanks,

    Dan 

    Friday, June 8, 2018 3:22 PM

All replies

  • Hello Dan,

    What's the class? Is it a table? How do you design it?

    I would suggest you use some simple data to detail your issue more clear.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 11, 2018 9:32 AM
  • Terry,

    Good point.  I'll try to explain it without the jargon.  We have a number of unique groupings (we call them classes) that records can be assigned to.  Each grouping has an upper limit of the number of records it can support.  The database is rationalized with a location table.  This table hold top level generic information.  If is linked to another table which has information about the different areas where we can have the groupings, or classes (we call these instances).  These tables are linked by an ID field (autonumber in the higher level table and plain number in the lower level table).  This location/Instance table is in turn linked to the groupings/Class table in the same manner.  I need to capture the number of records assigned to each Class as well as the total records in each Location/Instance.  the records are linked to the top level table as well as the class table by 2 separate fields within each record.

    A good parallel to use is a college or university with multiple campus locations or buildings.  The campus location is the top level table and has basic information about the location.  The buildings within the campus location would be the next level table.  Information about the building including the number and size of each classroom.  The lowest level table would be a class schedule with size limits for each class.  The records are the students which are registered.  I need to know how many students are in each class (there may be multiple of the same classes running in parallel in different rooms or even buildings), as well as how many students are in each building.  My application of course is for something totally different (I am building taglist configuration files to load into OSISOft PI systems).

    Hope this clears things up a bit.

    Dan

    Wednesday, June 13, 2018 2:59 PM