Consolidation of tables in MSAccess and VBA

Navigation
1

I have the following database in access: 

mov_atm
n_mov conta  data          valor         mov_sc
100   200    1/8/2014      500           n_mov   conta   data        valor
101   900    5/8/2014      800           200     900     5/8/2014    800 
102   100    1/8/2014      500           109     100     1/8/2014    500
103   100    1/8/2014      500

With the result I want to get the following: 

n_mov conta  data          valor         mov_sc
                                         n_mov   conta   data        valor
101   900    1/8/2014      800           102     900     5/8/2014    800 
102   100    5/8/2014      900           101     100     1/8/2014    500

Note that the number of records that exist in the mov_atm table is not the same as in the mov_sc table?
What I want is that n_mov of tabela mov_sc must inherit n_mov from table mov_atm and that no repeated data appear I want it to be as shown in the result.

How can I do to get this result using Access.vba ?

    
asked by anonymous 19.05.2014 / 18:03

1 answer

1

Your problem occurs because the n_mov_sc and n_mov_atm codes are not equal in the result.

To accomplish what you want, you can create a temporary table, without those code, as follows: 

CREATE TABLE mv_sc_atm
(conta INT,
 data  DATE,
 valor INT);

After the table is created, we insert the data: 

INSERT INTO mv_sc_atm SELECT mov_sc.conta, mov_sc.data, mov_sc.valor 
                      FROM   mov_sc, mov_atm WHERE mov_sc.conta = mov_atm.conta AND 
                             mov_sc.data = mov_atm.data

After this, we execute the query: 

SELECT DISTINCT * FROM mv_sc_atm;

At the end, destroy the table using the following command: 

DROP TABLE mv_sc_atm

Here's the link to the SQL Fiddle I used to recreate your scenario:

link

    
20.05.2014 / 18:53
Colorless IE table Entity with simple key referencing another with key composed