MTCCOMP
asked on
Append or Join table in Access vba
hello
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
union
select * from tbl2
union
will creating union query will make data double,
i am new to sql, never use union query
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
union
select * from tbl2
union
will creating union query will make data double,
i am new to sql, never use union query
ASKER
got help on part of the question.
ASKER
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.