crete new tables based moth

[sal21]

I have table named TAB1.
in this table have 21 fileds.

One of this filed in TAB1, contain a dates.

Field TAB1.DATE1, can have:
...
02/12/2014
03/01/2013
03/01/2013
02/12/2014
...

Now i need to create variuos table from TAB1. And recopy all data based a monh/year for each month/year.

Example:
for 12/2014, create (from TEMPLATE) a new table named 12_2014, append all data from TAB1 based month/year, delete from TAB1 based moth/year....

for 01/2013, create (from TEMPLATE) a new table named 01_2013, append all data from TAB1 based month/year, delete from TAB1 based moth/year....

ecc... dountil all records in TAB1

in effect i need to devid data from TAB1.

To the and of code TAB1 have a blank data.

Note:
i just have in databse a blanck table named TEMPLATE with the same structure of TAB1.
I use ADO and VBA for excel

I hope you understand me.

[Pat]

I don't know ADO so I will do it in DAO, you can modify it from there.
The code I would suggest is:

Code: Select all

    dim sSql as String
    ' First get a list of all the Month and Year in TAB1
    sSql = "SELECT Month(Date1) as Mth, Year(Date1) as Yrr FROM TAB1 GROUP BY Month(Date1), Year(Date1)"
    dim dbs as DAO.Database, rs as DAO.Recordset
    set dbs = Currentdb
    set rs = dbs.OpenRecordset(sSql)
    ' For each Month and Year in TAB1
    Do While Not rs.EOF
        ' Run a Make table query to create a new table for each Mth and Yrr in TAB1
        sSql = "SELECT * INTO [" & Format(rs.Mth,'00') & "_" & Format(rs.Yrr,'00') & "] FROM TAB1 WHERE Month(Date1) = " & rs.Mth & " AND Year(Date1) = " & rs.Yrr  
        dbs.Execute sSql
        rs.MoveNext
    Loop