none
Query Results from 3 differnent tables RRS feed

  • Question

  • Hi All,

    I was wondering if this is possible and if someone could point me in the right direction.

    I am using Visual Studio 2010 Express, and writing my code in VB.

    I have 3 shops that sends data to me all day, the data from each shop is stored in 3 different tables with in my Access Database.  What I would like to do is to be able to create a query so that I could search for say transactions for just today from all 3 shops and display the result in 1 Datagridview.  At the moment I have 3 Datagridviews to show the results but that makes it hard to compare the results at the same time.

    All the tables have the same coloum names and all cells are formatted the same, I am just not quite sure how I can do it.

    If someone could help me I would be very thankful

    Chris


    • Edited by Chris Yard Friday, February 10, 2012 10:15 AM Spelling correction
    Friday, February 10, 2012 10:14 AM

Answers

All replies

  • Hi,

    If you have 3 datagridviews, then what you can do is this:

    instead of having 3 datasets (or 3 datatables) filled separately by the fill method of your 3 tableadapters,

    just pass one and the same datatable through all 3 fill methods.

    You have to make sure all records are unique, so it may involve including the shop_id in the primary key of the datatable.

    Bind the one datatable to a grid and voilà...


    Regards, Nico

    Friday, February 10, 2012 10:35 AM
  • just pass one and the same datatable through all 3 fill methods.

    Hi Nico, Could you explain what you mean by this please.
    The Tables do have Primary Key of SHOPID

    Chris

    Friday, February 10, 2012 10:41 AM
  • well, I assume you have 3 datatables, and you fill them like this (somehow):

    dim datatable1 as datatable
    dim datatable2 as datatable
    dim datatable3 as datatable
    
    dataadapter1.fill(datatable1)
    dataadapter2.fill(datatable2)
    dataadapter3.fill(datatable3)
    
    ...
    
    ' would become
    
    dim datatable1 as datatable
    dataadapter1.fill(datatable1)
    dataadapter2.fill(datatable1)
    dataadapter3.fill(datatable1)
    
    ' with datatable1 bound to grid..


    Regards, Nico

    Friday, February 10, 2012 11:05 AM
  • Nico,

    What I am using to fill me Tables is

    Me.Shop1TA.Fill(Me.DBDataSet.Shop1)
    Me.Shop2TA.Fill(Me.DBDataSet.Shop2)
    Me.Shop3TA.Fill(Me.DBDataSet.Shop3)

    That would fill all 3 tables with every single record from all 3 shops, so are you say what I should do now is now do this.....

    Dim DataTable1 as DataTable
    me.shop1TA.Fill(DataTable1)
    me.shop2TA.Fill(DataTable1)
    me.shop3TA.Fill(DataTable1)

    then do my filtering in DataTable1 ??

    Chris

    Friday, February 10, 2012 11:30 AM
  • it would be:

    Dim DataTable1 as DataTable
    me.shop1TA.Fill(Me.DBDataSet.Shop1)
    me.shop2TA.Fill(Me.DBDataSet.Shop1)
    me.shop3TA.Fill(Me.DBDataSet.Shop1)

    mind you, this only gets you all your shops records in one table, thus one grid.

    The filtering and/or grouping can then be done on the grid.

    The most flexibility way of extracting business info is by creating a view on your access db, where you do a union of the three tables.

    Then you'd have a single view (which behaves like a read-only table) over all the shop's records.

    On top of that view, you could build other views (and/or reports) that return all kinds of aggregated values


    Regards, Nico

    Friday, February 10, 2012 11:50 AM
  • Hallo Chris,

    You have to do three things, 

    First you add a column to your tables which you fill with an expression, it is described here . 

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

    Then you give the tables internally all the same name (for instance a weeknumber

    http://msdn.microsoft.com/en-us/library/system.data.datatable.tablename.aspx

    Then you merge the tables in one table

    http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx

    That result of the Merge can than be showed in your DataGridView


    Success
    Cor

    Sunday, February 12, 2012 12:49 PM
  • Please use following code.

    private void GetTableNames(DataSet dataSet)
    {
    // Print each table's TableName.
    foreach(DataTable table in dataSet.Tables)
    {
    Console.WriteLine(table.TableName);
    }
    }

    Wednesday, February 15, 2012 8:06 AM