Solved

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

Posted on 2012-04-04
35
499 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

20 Experts available now in Live!

Get 1:1 Help Now