Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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 ?


0
MTCCOMP
Asked:
MTCCOMP
  • 3
2 Solutions
 
MTCCOMPAuthor Commented:
update
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.
 
0
 
hnasrCommented:
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
union
select * from tbl2
union
.....

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.
0
 
BALMUKUND KESHAVCommented:
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
0
 
MTCCOMPAuthor Commented:
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
0
 
MTCCOMPAuthor Commented:
got help on part of the question.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now