Insert/append multiple rows into a Ms access table from a access table that has 1 row

Sivasan
Sivasan used Ask the Experts™
on
Hi There,

I have two table in access table A, with fields  OrdNo  Qty   DueDate   Start Date  Idno
and table B with fields   OrdNo  Qty   DueDate  Idno.

Idno will be the primary key in both tables or I should say OrderNO and Idno combination should be unique.

I want the table B to get appended/inserted with the same order no on table A from the Start date till the due date  on the very first time you execute the insert/append query.

For example, assuming table A has the following value


OrdNo     Qty    DueDate     StartDate             Idno

12            10      4/7/2012      4/4/2012           7100  
34             20     4/8/2012      4/4/2012           7200

So I want to make an append or insert query so once I execute it table B will have the following

Table B

OrdNo     Qty    DueDate           Idno

12            10      4/4/2012             1    -----   Same order number has to be from start Date
12             10      4/5/2012            2
12             10     4/6/2012             3
12             10     4/7/2012             4  --------  Till end due date    
34             20     4/4/2012             5
34              20    4/5/2012             6
34              20    4/6/2012             7
34              20    4/7/2012             8
34              20    4/8/2012             9
 
Next time the append/ insert is executed same order should not come in again to make duplicate.

Please advise how I can do this.
thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
you can do this better using VBA codes and recordsets




dim rsA as dao.recordset, rsB as dao.recordset, j as integer, x As integer
set rsa=currentdb.openrecordset("tableA")



set rsB=currentdb.openrecordset("tableB")

rsa.movefirst
x=1
do until rsa.eof
 

        for j= 0 to datediff("d",rsa!StartDate,rsa!DueDate)
                with rsB
                      .addnew
                      !OrdNo=rsA!OrdNo
                      !Qty=rsa!Qty
                      !DueDate=rsA!StartDate + j
                      !IDNo=x
                      .update
                end with
               x=x+1
        next

rsa.movenext
loop

rsA.close
rsB.close
Top Expert 2014

Commented:
It looks like you can go a long way to you goal by making the Idno column in Table B an autonumber data type.

You should add a column to Table A that will note whether the row has been cloned.

Author

Commented:
Hi Capr,
Thanks a lot for your response, I'm going to try now, but I should have mentioned this. Table B is actually not a table but a query in access. So will this change?
thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Aiki,

"You should add a column to Table A that will note whether the row has been cloned."

How can I make table A note whether the row has been cloned." if I take your approach.
thanks
Top Expert 2014

Commented:
I have a TallyTable with numbers I can use for such occasions.  You can read about TallyTables and get the queries/code to create them in my Tally Table article:
http:/A_5410-Creating-and-Using-a-Tally-Table-in-Access.html

Append Query
INSERT INTO TableB ( OrdNo, Qty, DueDate )
SELECT TableA.OrdNo, TableA.Qty, [ID]+[StartDate]-1 AS IndivDueDate
FROM TableA, TallyTable
WHERE (((DateDiff("d",[startdate],[duedate])+1)>[TallyTable].[ID]-1))
ORDER BY TableA.OrdNo, [ID]+[StartDate]-1;

Open in new window


Update Query
UPDATE TableA INNER JOIN TableB ON TableA.OrdNo = TableB.OrdNo 
SET TableA.HasBeenCloned = True;

Open in new window


I know that the Update query isn't the most efficient, but it is the simplest.

Note: the HasBeenCloned column in TableA is a boolean (yes/No) data type.
Top Expert 2014

Commented:
You could create a macro that runs both of these queries or you could create a VBA routine to invoke them.
Top Expert 2016

Commented:
<Table B is actually not a table but a query in access. So will this change?>

TableB MUST be a table..
Top Expert 2014

Commented:
I just realized that I forgot to add a check for the HasBeenCloned column in the append query.

INSERT INTO TableB ( OrdNo, Qty, DueDate )
SELECT TableA.OrdNo, TableA.Qty, [ID]+[StartDate]-1 AS IndivDueDate
FROM TableA, TallyTable
WHERE (((DateDiff("d",[startdate],[duedate])+1)>[TallyTable].[ID]-1))
And HasBeenCloned = False
ORDER BY TableA.OrdNo, [ID]+[StartDate]-1;

Open in new window

Top Expert 2014

Commented:
I concur with cap1.  If TableB is actually a query, it needs to have some table source (by definition).

Author

Commented:
Hey,
Sorry I meant Table A is a query not table B. Table B which will get the update will be a table and table A will be a query whose result will go into table B.
Top Expert 2014

Commented:
It shouldn't matter, then.  Have you tried any of the solutions?

Author

Commented:
Hi Aiki,
I'm going to try both now.
Capri
where do I create the vba, sorry I'm new. Would I have to paste the code on modules?
thanks
Top Expert 2016

Commented:
Would I have to paste the code on modules?
1. Yes
2. you can call the sub   AppendROWS from the click event of a button in a form


Sub AppendROWS()

dim rsA as dao.recordset, rsB as dao.recordset, j as integer, x As integer
set rsa=currentdb.openrecordset("tableA")



set rsB=currentdb.openrecordset("tableB")

rsa.movefirst
x=1
do until rsa.eof
 

        for j= 0 to datediff("d",rsa!StartDate,rsa!DueDate)
                with rsB
                      .addnew
                      !OrdNo=rsA!OrdNo
                      !Qty=rsa!Qty
                      !DueDate=rsA!StartDate + j
                      !IDNo=x
                      .update
                end with
               x=x+1
        next

rsa.movenext
loop

rsA.close
rsB.close

end sub

Author

Commented:
Hi Capri
I created a form and perfomed the routine you posted on the click of a button event. It works great the problem I have though is first time it puts the data, the next time when I have more data in table A that's not available in tbl B and I click the button it doesn't update, throws an error will create duplicate.
It's look at the existing one and since it doesn't want to create duplicate( which is what I want shouldn't create duplicate) but fails to update the ones that's not already there in B, how can I make it update the ones that's missing in B by passing the error.

Also I had to add another field that needs to be moved from A to B. On B the field name is with a space in betweek, it's a pain to change that as lot of quries are already built.

 !Sc qty=rsA!Scheduleqty

doesn't like syntex, I tried

 ![Sc qty]=rsA!Scheduledqty
doesn't like it either  also tried {sc qty} didn't work what will be the correct way to put sc qty.

thanks a million for all your help.
Top Expert 2016

Commented:
better if you post a copy of the db...

Author

Commented:
Hi Capri,
I was able to figure out the Sc qty i just had 2 spaces in the field so when I did [sc  qty] it worked. Attached my db, you will see A and B has data after using form "Import" and clicking on Import btn.I already imported data once.
Now you try to hit import button again  or you add another order to A like 7891 or can remove order 1234 from B and try to import again and will throw an error.Since it has 3456 order in there it's not doing 1234 that's not in B.
Order.mdb
Top Expert 2016

Commented:
so what you want to happen, is to continue appending records

test this
OrderRev.mdb

Author

Commented:
Hi Capri,
It does update now, but it creates duplicate. Try to press import now as you have in the db ou sent me.
Top Expert 2016

Commented:
<but it creates duplicate>
be specific as to what you want to happen..

what is considered duplicate and should not be allowed...
Top Expert 2014

Commented:
I added the boolean field to table A and removed the unique index on the IDNO column in table B.

I added a couple of queries that give me the Tally Table number source and tweaked my prior SQL to refer to the Tally Table query.

I also created a macro that will invoke both of the queries.
Order.mdb

Author

Commented:
Hi Aiki,
You method work pretty good should work the way I want, how can I invoke the macro from a form button click event
thanks
Top Expert 2014

Commented:
docmd.runmacro "BozoTheClone"

Open in new window

Author

Commented:
Hi aiki,
My real db is pretty big with many more fields that I posted so I made a test and posted. Now when I copy QBTallyTableBase10 and QueryBasedTallyTable_AllQry to my real db.
It can't find the tbls on it is there anything other than these two queries that is needed for Tally tble
Top Expert 2014

Commented:
Those queries should be all that you need, unless you have a date difference greater than 1000 days.

Author

Commented:
Attached error when I check those two tall query
error.doc
Top Expert 2014

Commented:
what version of Access are you running?

Author

Commented:
2003. I can use 2007 if you want

Author

Commented:
I currently use access 2003
Top Expert 2014

Commented:
I tested this on an Access2003 database.  MSysObjects should be in your database.  Is this actually a 2003 format database?

Did you import the two tally table queries from the database I posted as well as the clone and update queries?

Author

Commented:
Hi Aiki,
I exported it now, and they are working now both those tally queries are working now. Now I have another problem, since in my case Table A is a query when it executes SetClonedIndicator it says Operation must use an updateable query
Top Expert 2014
Commented:
I moved the HasBeenCloned indicator into a new table.  That allows for your 'table A' to be a query.  Rather than set a boolean variable, I now append the OrdNo value into the new table.  The net effect is the same, just does things a bit differently.

Have you had a chance to read my Tally Table article?
http:/A_5410-Creating-and-Using-a-Tally-Table-in-Access.html

Note: the unique index on the new table prevents duplicates, so I don't have to worry about bloat or duplicate key errors.
Order.mdb
Top Expert 2014

Commented:
Also, I changed the data type of the OrdNo column in table A and the HasBeenCloned table to long integer.

Author

Commented:
Hi Aiki,
It worked like a charm. The only thing I changed back OrdNo back to text as when the second time it tries to update it said data mis match.
Everything works good now, my actual table has lot more field, It now moves all the fields without any problem.It works great.. THANKS A MILLION....
Top Expert 2014

Commented:
@Sivasan

Glad it works.  If your OrdNo column contains numeric data, you should make it numeric for best performance.  Also, as you've seen in your testing, the OrdNo column in the HasBeenCloned table.

Author

Commented:
The OrdNo in HABbEENCLONED, i changed it to text.My table B is used in lot of places and it has Ordno as text, so if change now, will affect lot of things. So I changed Hasbeencloned.Works good.
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial