Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

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

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
Sivasan
Asked:
Sivasan
  • 15
  • 14
  • 6
1 Solution
 
Rey Obrero (Capricorn1)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
0
 
aikimarkCommented:
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
 
SivasanAuthor 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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
SivasanAuthor 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
0
 
aikimarkCommented:
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
 
aikimarkCommented:
You could create a macro that runs both of these queries or you could create a VBA routine to invoke them.
0
 
Rey Obrero (Capricorn1)Commented:
<Table B is actually not a table but a query in access. So will this change?>

TableB MUST be a table..
0
 
aikimarkCommented:
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
 
aikimarkCommented:
I concur with cap1.  If TableB is actually a query, it needs to have some table source (by definition).
0
 
SivasanAuthor 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.
0
 
aikimarkCommented:
It shouldn't matter, then.  Have you tried any of the solutions?
0
 
SivasanAuthor 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
0
 
Rey Obrero (Capricorn1)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
0
 
SivasanAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
better if you post a copy of the db...
0
 
SivasanAuthor 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
0
 
Rey Obrero (Capricorn1)Commented:
so what you want to happen, is to continue appending records

test this
OrderRev.mdb
0
 
SivasanAuthor 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.
0
 
Rey Obrero (Capricorn1)Commented:
<but it creates duplicate>
be specific as to what you want to happen..

what is considered duplicate and should not be allowed...
0
 
aikimarkCommented:
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
 
SivasanAuthor 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
0
 
aikimarkCommented:
docmd.runmacro "BozoTheClone"

Open in new window

0
 
SivasanAuthor 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
0
 
aikimarkCommented:
Those queries should be all that you need, unless you have a date difference greater than 1000 days.
0
 
SivasanAuthor Commented:
Attached error when I check those two tall query
error.doc
0
 
aikimarkCommented:
what version of Access are you running?
0
 
SivasanAuthor Commented:
2003. I can use 2007 if you want
0
 
SivasanAuthor Commented:
I currently use access 2003
0
 
aikimarkCommented:
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
 
SivasanAuthor 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
0
 
aikimarkCommented:
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
 
aikimarkCommented:
Also, I changed the data type of the OrdNo column in table A and the HasBeenCloned table to long integer.
0
 
SivasanAuthor 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....
0
 
aikimarkCommented:
@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
 
SivasanAuthor 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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 15
  • 14
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now