Append Query

[Kelsey]

I am wanting to use an Append Query to append records from one database to another. I am having problem with deciding on criteria to append only the records that haven't already been appended to the second database table.

Query:
IssueQuantity
InventoryID


Cost
PurchaseOrderDetailID
Job ID

Second table:
ReceivingID
InventoryID
ReceivingQuantity
Cost
ReceivingDate
PurchaseOrderDetailID

I will be opening the append query sometime in the form frmIssueNew. I only need to Append the records that have a JobID of 22, and haven't already been appended.

Any help on the criteria for this is appreciated! thanks!

[jzwp11]

Just to be clear you said this:

I am wanting to use an Append Query to append records from one database to another.

Did you really mean to say that you are appending data from one table in the database to a table in a second database? Or, did you really mean to say that you are appending data from 1 table to another table within the SAME database?

As to the query criteria, you will definitely need the jobID and then another field that distinguishes those records that have already been appended from those that can be appended.


If you are doing this between 2 tables in the same database, then I am guessing that you have not properly structured your tables since you should not have two tables with such similar data. That is a guess at this point. Could you please provide a little more info on your table structure and what business process your database is intended to model?

[Kelsey]

Yes, I did mean to say 2 different databases. My current database is an inventory database, with the location being our parts room. We also have a Van that carries inventory in it. I was hoping to be able to append records issued to the Van FROM our parts room (carrying over cost and all), and then having a totally seperate inventory system for the 2nd database inventory. The inventory in the parts room is used for things different than that of what the Van inventory is used for.
Hope this helps...

[jzwp11]

How are the two databases linked now? Are you using linked tables to link to the Van database? How do people in the van interact with the database as they use their inventory?

[Kelsey]

I'm just messing with it right now...but...
I was using tblInventory as a linked table, and put the Van (ID 22) as a Job in the first database. The van gets all of its inventory from the parts room, so when the van needs to be re-stocked, you'd issue parts from the first database to the second. This would take the parts out of the parts room and put them into the 2nd via an append query. This would keep the issues in my tblIssueDetail table in the first database, and also put them in my tblVanReceiving in my second database. The parts get out of the van inventory by invoices to specific customers (different entirely) from the first database.

[jzwp11]

Frankly, I would use only one database and track where the parts go based on location (Van, customer etc.). You can use a select query to get the inventory at any location at any time. Then you would not need to mess with append queries.

tblPartTransactions
-pkPartTransID primary key, autonumber
-fkPartID foreign key to tblParts
-quantity
-fkLocationID foreign key to a table that holds all possible locations (parts room, van, customer etc.)
-dteTrans (date of transaction)

For more on inventory control, you might want to take a look at Allen Browne's site.

[Kelsey]

I have already looked at Allen Brownes site. The first database has been up and running properly with it's own module. I was hoping to implement Allen's design on the 2nd database. Thanks for your help.

[jzwp11]

OK, if you are going to use the 2 database approach then you will either need to add a flag field (yes/no field perhaps) to your current database to mark those items as being moved to the other database. Then after you run the append query, run an update query to mark the flag field accordingly. Alternatively, you could have a table that holds the date/time when the appends occur and then when it is time for the next event, just select those records whose date is > the last event date.