+ Reply to Thread
Results 1 to 16 of 16

Access VBA or SQL to create a query that has matching data from 2 tables

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Access VBA or SQL to create a query that has matching data from 2 tables

    Hi,

    i have 2 tables now in access:

    table 1 has 8 fields: office employee id, office employee name, description, account id, account info, period date,
    table 2 has 4 fields: employee id, employee name, account id, date, account period date

    office employee id and office employee name (table 1) will always be the same with employee id and employee name (table 2). they have different field names but it contains same info. (e.g: john sanders will always have the same name and same employee id in table 1 and table 2)

    account id is basically a string of numbers xx-xxx-xxx-xxx-xx.

    what im trying to do is update table 2 in relation to table 1. table 1 currently has 1000 people and table 2 has 5000 people. table 2 contains a list of people who are AND are not on table 1.

    I ONLY need people who are on table 1 (e.g. if person A is on table 2, but not on table 1, then i dont want him on table 2 anymore).

    the new table 2 would only have a list of people who are on table 1. the fields are going to be the same (employee id, employee name, account id, date, account period date)..

    afterwards, i want to add the records on the new table 2 to an existing table (human resources data). i think the word im looking for is "append" on access.

    can anyone help me with this?

    there's no "record macro" option on access so im at lost here...

    thanks!

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Create a Make table query something like this will show only the values that are in table 1 from table 2

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Hi,

    I received an error message saying: Syntax error in FROM clause.

    Thanks!

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519
    What are your actual table names?

  5. #5
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    it seems like its working. i just need to put [] for table names. so how about when i want to add the records on the new table 2 to an existing table?

    Quote Originally Posted by mike7952 View Post
    What are your actual table names?

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Ok,
    If you already have a table that you want to insert these records into then try this without creating the new table. Replace NameOfTableToInsertNewRecords with the table name to insert the new records.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    I got an error message

    "Reserved error (-30002); there is no message for this error"

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Is the Table you are appending to have an ID field as Primary Key and set to auto number? Are the all fields the same from table 2 to the appending table?

  9. #9
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Hi,

    Yes, table I am appending to have an ID field as Primary Key and set to auto number...

    Quote Originally Posted by mike7952 View Post
    Is the Table you are appending to have an ID field as Primary Key and set to auto number? Are the all fields the same from table 2 to the appending table?

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    That could be the issue. I would need a copy of the database to assist any further. Does your Table 2 have an Id field as the primary key?

  11. #11
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    Hi,

    It seems like I have an error code when i try to run this code. it says "type mismatch in expression". i believe its because office employee id's data type is text while employee id's data type is number.

    is there any way to change the data type to either number or text for both of them through vba or sql?

    thanks!

    Quote Originally Posted by mike7952 View Post
    Create a Make table query something like this will show only the values that are in table 1 from table 2

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    No. My table 2 has no primary key. I imported it from excel using this code below:

    Please Login or Register  to view this content.
    Is there any way to modify the code above to add a primary key field in the table?

    Thanks

    Quote Originally Posted by mike7952 View Post
    That could be the issue. I would need a copy of the database to assist any further. Does your Table 2 have an Id field as the primary key?

  13. #13
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    What's confusing is when i tried to change the data type manually in table 2, it says that:

    microsoft access encountered an error while converting the data and the contents of fields in 4 records were deleted. do you want to proceed anyway?

    i ran it one and i found out what those 4 records are.

    the only thing that got deleted were the employee id of those 4 records. employee name, account id, date, account period date weren't deleted..

    the employee id of those 4 records are like e.g. B00008, B00001, B00003... those 4 are the only records that start with a letter, instead of a number. im assuming thats why it got deleted..

    is there any way to get around this?

    thanks
    Last edited by ewong; 09-13-2012 at 04:39 PM.

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    @ewong,

    is there any way to change the data type to either number or text for both of them through vba or sql?
    Im not really sure. I do not use DoCmd.TransferSpreadsheet. I use ADODB to import data to or from Access.

  15. #15
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    is ADODB a VBA or SQL?

    I don't mind using SQL...

  16. #16
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Access VBA or SQL to create a query that has matching data from 2 tables

    another error: it says that the source and destination are the same

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1