Inserting multiple entries into linked split tables

redbe4rd

Registered User.
Local time
Yesterday, 18:51
Joined
Sep 19, 2012
Messages
12
I apologize in advance if this has already been posted/solved. I wasn't quite sure what to search for when googling this and searching these forums.

Scenario:
I have an Access 2007 database for keeping track of quotes/projects/inventory. The user receives an RFQ (request for quote) and enters it into our quoting system, which uses the following tables:
Quote_Hdr
Quote_Items
Quote_Items_Sys (this is just a small linked table for the items that allows them to be grouped by systems/description headers)
The table that I'm currently working with is the Quote_Items table, which contains the following information/fields:
Line Item #
Part #
Quantity
Description, etc...
Once the quote is "awarded", it then needs to become an active "Project" which has its own set of tables. The information is copied from the quote tables (not all the information is used, some is entered upon "awarding" the quote), and everything is pretty straightforward, except for the items table.

The items have a need to be individualized (qty 1 per entry) for things such as Pallet/Box location, serial #, etc. at this point. The project tables are as such:
Project_Hdr (mostly the same as the quote..less info)
Project_Items
Project_Items_Info
Project_Items_Systems (same as quote)
This brings me to my multitude of hurdles. When copying the header information, I simply use an INSERT INTO w/ SELECT * FROM Quote_Hdr WHERE Quote_Hdr.ID = whatever ID. I'm at a complete standstill (again, from lack of search terminology) on my query for the items tables.

What's needed:

INSERT Line Item, Part #, Description from Quote_Items to Project_Items and assign a Project_ID based off of the Project_Hdr INSERT for EACH quantity of EACH item.

Ex:
Quote_Items Item #1 has a quantity of 2.
Project_Items should have two new records with matching information, except for Primary Keys.
INSERT all other item information (label notes, user notes, pricing, etc.) from Quote_Items to Project_Items_Info and assign an Item_ID based off of the Project_Items.ID that was just inserted above.

I understand that duplication of data is a pretty big DB no-no, but it's practically required in this scenario so that we can quote multiple quantities with ease, but still be able to assign the individual items serial #s and locations once they're received.

I'm trying to avoid pulling the tables and doing it programmatically through VBA. It feels like there should be a fairly simple SQL solution, I just can't find it. -.-

Any and all help is greatly appreciated.
 

Users who are viewing this thread

Back
Top Bottom