Append or Join table in Access vba

Posted on 2009-04-15
Last Modified: 2013-11-28

I have access program that input data from text file into the access table, (fixed sturcture table)
Every time input data routine is run (data from different stores), it create table with storename+date name (As wanted).  And when report is generated it has data from table.
Report take that table as record source.

What needs to done is that
1 - if input data is run for four store (could be more). Report should have data from all four store.
Problem = how to combine  or link all 4 table into one and set that as record source for repot?
Data is huge, so if there is a way to link all table, instead of appending it into one query will have database size grows twice (if possible)

2 - if possible to have option to display one store selcted or all store in report using combobox.

Any help ?

Question by:MTCCOMP

    Author Comment

    i have create SQL statement to run everytime input data is run.
    SQL statement basically append the data from current create table into precreated "Imported Data ALL" table.
    stSQL = "INSERT INTO [Imported Data ALL] (  Field1,  Field2,  Field3 )SELECT  [" & stTableName & "].Field1, [" & stTableName & "].Field2, [" & stTableName & "].Field3 FROM [" & stTableName & "];"

    DoCmd.RunSQL stSQL
    and use "Imported Data ALL" table as record source for report
    If i so this then i have double the database size. any way just to link it.
    LVL 30

    Accepted Solution

    What you need is a table for each store, with a structure like this:
    StoreID, FieldA, FieldB, .....

    You may create a union query for all data,

    Select * from tbl1
    select * from tbl2

    You can use this query in nreporting.
    You may use this query to create other queries with specific criteria like specific store or other criteria and use them as record source for other reports.
    LVL 6

    Assisted Solution

    Hnsr has suggest you is good but if you have duplicate records after merging all stores data, probably it should not be , then use UNION ALL in place of UNION word in query.

    Reg. specific stores report filter reports using storesid as a input variable in report .

    thanks and all the best.

    Bm Keshav

    Author Comment

    What you need is a table for each store, with a structure like this:

    I already have tables from each store, table name  = strore name
    Are you saying this for combine table/query like this
    StoreID, FieldA, FieldB, .....
    You may create a union query for all data,

    Select * from tbl1
    select * from tbl2
    will creating union query will make data double,
    i am new to sql, never use union query

    Author Closing Comment

    got help on part of the question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    Title # Comments Views Activity
    Help in Creating Access Webapp 7 38
    Access question - SELECT/UPDATE 11 25
    Day count current month 3 23
    Mysql not caching queries 4 30
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now