Please Help:table creation via vba

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

I am having a problem with a table in MS Access 2003:

I have a table tbl1 with the folowing
fields(id,personelid,name,startdate,enddate,ammount) and I want to
create a new table tbl2 with the folowing
fields(id,personelid,name,startdate,enddate,year,month,ammount).
For every id in tbl1 I want e record for every month and year from
startdate to enddate.

for example:
tbl1 (id,personelid,name,startdate,enddate,ammount)
1,d125,employee name,01/01/2006,01/04/2006,25$

tbl2 (id,personelid,name,startdate,enddate,year,month,ammount)
1,d125,employee name,01/01/2006,01/04/2006,1,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,2,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,3,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,4,2006,25$

thanks
 
Thomas said:
I am having a problem with a table in MS Access 2003:

I have a table tbl1 with the folowing
fields(id,personelid,name,startdate,enddate,ammount) and I want to
create a new table tbl2 with the folowing
fields(id,personelid,name,startdate,enddate,year,month,ammount).
For every id in tbl1 I want e record for every month and year from
startdate to enddate.

for example:
tbl1 (id,personelid,name,startdate,enddate,ammount)
1,d125,employee name,01/01/2006,01/04/2006,25$

tbl2 (id,personelid,name,startdate,enddate,year,month,ammount)
1,d125,employee name,01/01/2006,01/04/2006,1,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,2,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,3,2006,25$
1,d125,employee name,01/01/2006,01/04/2006,4,2006,25$
Click to expand...


You can use another table (named tblNumbers) with one field
(named Num) and populated with values 1,2,...,12 (or more)
for the months.

With those in place, use a query like:

SELECT id, personelid, name,
startdate, enddate,
Num, ammount
FROM tbl1, tblNumbers
WHERE Num Between Month(startdate) And Month(enddate)

I have no idea why you would want to create another table
for dummy data when a query is almost always sufficient.
 
Back
Top