Solved

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

Posted on 2012-04-04
35
505 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 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

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

test this
OrderRev.mdb
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now