Complex Append Query

E

Edgar Thoemmes

I have a form which is used as a order entry form, it is based on 2 tempory
tables.

A user will input the invoice details ie inv number, supplier, currency,
delivery etc and then enter all the actual items which where purchased and
their quantities and prices on a seperate sub form.

I have two tables in my database, one for copies of each book which stores
location, condition and unit cost details and one table which holds book
details ie author, description, isbn etc.

What i want it to append the data which was entered into the temp tables
into the Copies and Book Details tables. The problem I am having is in the
temporary table I have added a field for quantity of books purchased (so
user does not have to enter each book individually) and I would like to
design a query which if the value of the quantity was say 5 it would append
5 entries into copies table and append 1 entry into book details table.

I would quite like to do this via VBA so that I can also perform someother
calculations(ie converting foreign currency etc)

I can figure out how to append to the Book Details table but I need some
assistance with appending the multiple copies to the copies table.

Can anyone suggest any hints on how this may be acheived?

Temporary Tables

Invoice detail table - tblTEMPInvoiceDetails(InvoiceNumber, Supplier,
Currency, BuyersPremium, Delivery, Tax, DiscountReceived)
Book detail table - tblTEMPBookDetails(InvoiceNumber, BookID, ISBN, Title,
Author, Quantity, UnitPrice)

Database Tables

Book Details Table - tblBookDetails(BookId, ISBN, Title, Author,)
Copies Table - tblCopies(CopyId, Location, Cost, Condition, Location)
 
S

Steve Schapel

Edgar,

Try it like this...

Dim i As Integer
For i = 1 To Me.QuantityOfBooks
< run your copies append query here >
Next i
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 1
Access Append Query 2
Append Query To Table 3
Select / Append Query 1
Improving append query speed 2
update 0
Append Query 1
Append Query in MS Access 1

Top