none
Comparing records between two databases RRS feed

  • Question

  • Hi all,

    I've got a particularly complex issue that I'm trying to solve here. I have two tables on Access, one being "classes", and one being "students".

    The "students" table is a list of every student registered onto my registration/attendance program, each of which has a designated registration class (i.e. 1A), which means that my program will select and display the appropriate students with the appropriate registration letters. The "classes" table has a record of different class identification numbers (i.e. 1A1), for different subjects, which also contains the students who are in that class, one person per record.

    What I'm trying to do is match the names of the records inside "classes" with the names inside "students" - and display only the names requested from "students" that "classes" has given them into a datagridview on my software. Essentially, I need my program to search through the "classes" database for a list of names given from "students". I've tried numerous solutions but to no avail.

    Sorry if my question is quite vague, I'm particularly new to SQL/Database queries via VB - not to mention also that MSDN is not letting me post screenshots until my account is verified - however, your help is greatly appreciated. Thanks in advance!

    Monday, March 12, 2018 7:44 PM

All replies

  • Hi all,

    I've got a particularly complex issue that I'm trying to solve here. I have two tables on Access, one being "classes", and one being "students".

    The "students" table is a list of every student registered onto my registration/attendance program, each of which has a designated registration class (i.e. 1A), which means that my program will select and display the appropriate students with the appropriate registration letters. The "classes" table has a record of different class identification numbers (i.e. 1A1), for different subjects, which also contains the students who are in that class, one person per record.

    What I'm trying to do is match the names of the records inside "classes" with the names inside "students" - and display only the names requested from "students" that "classes" has given them into a datagridview on my software. Essentially, I need my program to search through the "classes" database for a list of names given from "students". I've tried numerous solutions but to no avail.

    Sorry if my question is quite vague, I'm particularly new to SQL/Database queries via VB - not to mention also that MSDN is not letting me post screenshots until my account is verified - however, your help is greatly appreciated. Thanks in advance!

    I'm trying to understand your database layout.  Is this something like what you have?

    Students:
    Name                Age            Gender          Etc
    Fred                  42              Male              whatever
    Wilma               41               Female          whatever
    Barney              38              Male               whatever

    Classes:
    SubjectId          Student
    1A1                  Fred
    1A1                  Barney
    1B1                  Fred
    1B1                 Wilma
    1B1                  Barney

    Something like that?

    Typically in a many-to-many relationship (one class has many students, one student can have many classes) you need three tables - one to define all the students, one to define all the classes, and a third to relate students to classes.  In the example above, there is no table defining a class so there's nothing to limit what goes in the SubjectId field and no other information about the class.  That probably isn't desirable.

    Chances are that this will be handled by defining relationships and then using a join in the query or loading all the data and navigating the relationships in memory.  But precisely how you do that will depend on the design of the database and the intended usage of the application.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Monday, March 12, 2018 8:17 PM
    Moderator
  • One thing I would like to add to Reed's reply is you need to define primary keys. having auto increment keys are index in ms-access which means quicker searches than with string fields which by default are not indexed. Joins are easier to setup for ad-hock queries.

    In the following which is complete overkill note each table uses primary keys. No matter if we are dealing with one or many tables setup primary keys.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, March 12, 2018 8:52 PM
    Moderator
  • Calvearn,

    What you show is almost the most simple related database stucture. 

    It is called Master Slave or Parent Child and a 1 to many database. 

    The principle to get the data is simple. You read the students and in that there is always an identifier for the Classes. You read always first your students and then get the class information with it. 

    The most simple solution for you is using dataadapter and an SQL statement with a join. 

    I don't have an exact sample in fact it is less than this

    http://www.vb-tips.com/OleDBParameters.aspx

    This bellow is about the select

    https://msdn.microsoft.com/en-us/library/bb208854(v=office.12).aspx


    Success
    Cor


    Tuesday, March 13, 2018 1:29 AM