Solved

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

Posted on 2012-04-04
35
508 Views
Last Modified: 2012-04-09
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
0
Comment
Question by:Sivasan
  • 15
  • 14
  • 6
35 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37809055
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37809107
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.
0
 

Author Comment

by:Sivasan
ID: 37809116
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
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Sivasan
ID: 37809122
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37809138
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37809156
You could create a macro that runs both of these queries or you could create a VBA routine to invoke them.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37809163
<Table B is actually not a table but a query in access. So will this change?>

TableB MUST be a table..
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37809226
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

0
 
LVL 45

Expert Comment

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

Author Comment

by:Sivasan
ID: 37814573
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37814649
It shouldn't matter, then.  Have you tried any of the solutions?
0
 

Author Comment

by:Sivasan
ID: 37814687
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37815828
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
0
 

Author Comment

by:Sivasan
ID: 37817401
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817440
better if you post a copy of the db...
0
 

Author Comment

by:Sivasan
ID: 37817778
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817804
so what you want to happen, is to continue appending records

test this
OrderRev.mdb
0
 

Author Comment

by:Sivasan
ID: 37817835
Hi Capri,
It does update now, but it creates duplicate. Try to press import now as you have in the db ou sent me.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817838
<but it creates duplicate>
be specific as to what you want to happen..

what is considered duplicate and should not be allowed...
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37817884
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
0
 

Author Comment

by:Sivasan
ID: 37817938
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37817955
docmd.runmacro "BozoTheClone"

Open in new window

0
 

Author Comment

by:Sivasan
ID: 37818010
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37818050
Those queries should be all that you need, unless you have a date difference greater than 1000 days.
0
 

Author Comment

by:Sivasan
ID: 37818061
Attached error when I check those two tall query
error.doc
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37818070
what version of Access are you running?
0
 

Author Comment

by:Sivasan
ID: 37818072
2003. I can use 2007 if you want
0
 

Author Comment

by:Sivasan
ID: 37818073
I currently use access 2003
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37818086
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?
0
 

Author Comment

by:Sivasan
ID: 37818093
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
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 37818166
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37818204
Also, I changed the data type of the OrdNo column in table A and the HasBeenCloned table to long integer.
0
 

Author Comment

by:Sivasan
ID: 37825408
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....
0
 
LVL 45

Expert Comment

by:aikimark
ID: 37825439
@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.
0
 

Author Comment

by:Sivasan
ID: 37825457
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
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question