Consolidation of tables in Msaccess and VBA

Asked

Viewed 437 times

1

I have the following access database:

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 intend 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 table mov_atm is not the same with the table mov_sc right?
What I want is that the n_mov of tabela mov_sc must inherit the n_mov table mov_atm and do not appear repeated data I want it to stay as shown in the result .

How can I get this result using Access.vba?

ms-access vba

  • You could use {Edit]ar and format your question better. It’s hard to understand this way.

  • 2

    Add to me how you want the output I help you to build SQL. It’s a bit confusing. And another thing, what is the link between the tables? account?

  • What fields do you really need in the result?

  • 2

    The result you added in your question makes no sense. The count and date fields are not equal, although in the query this is indicated. It is possible that you have added another result?

  • I do not know if I was clear now but if I was not weight to give a touch but in short that is what I intend to do.abs waiting.de Mocambique

  • 2

    Hi, Alex from Mozambique, I don’t know if you noticed, but the guys here has been having a lot of work to improve your posts. Please take advantage of your participation here to improve your writing: accents, points, uppercase, spelling, formatting... This will be useful in your professional life too. Good luck!

  • thanks bro thanks for the tip.

  • who has a tip for the problem in question thank you

Show 3 more comments

1 answer

1

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

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

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

After creating the table, 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 that, 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

Follow the link to SQL Fiddle I used to recreate your scenario:

http://sqlfiddle.com/#! 2/2717f/2/0

  • cannot create a function capable of doing this using access.vba or vb6?

Browser other questions tagged ms-access vba

You are not signed in. Login or sign up in order to post.