Insert Matching Records to different table

[Jack Leach]

Hi all, TIA for any help...

I'm trying to get away from some of my old-fashioned recordset looping
techniques and build a better understanding of how to use SQL (not a strong
point for me) to accomplish the same things. Wondering if someone can give
me a hand.

I've got two tables that hold Required Documentation information... one for
Parts that holds records based on the part, and one for Orders that holds
basically the same records, but allows for there to be order-specific changes
in the requirements for documentation.

As of now, I use two recordsets, rsP(arts) and rsO(rders). I select * from
tblPartRqdTrkDocs WHERE fldPart = SomePartID, loop that recordset and for
each record add a new recordset into rsO, copying the field information.

I know (think) this can be fairly easily done with an INSERT INTO SQL
execution... but I don't know how to set it up. Here's the fields...

Copy from:
tblPartRqdTrkDocs
fldID
fldPart
fldDocID
fldFirstRlsOnly

to:
tblOrdRqdTrkDocs
fldID (autonum)
fldOrderLine (get from form control)
fldDocID (copy from other table)
fldFirstRlsOnly (copy from other table)


I need to be able to copy the fields fldDocID and fldFirstRlsOnly... not
sure how to reference these inside an SQL string? And, how do I have it work
for any number of records for the given PartID?

Any help greatly appreciated... thanks

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Alex Dybenko]

Hi Jack ,
something like this (as I understand you):
currentdb.execute "Insert into tblOrdRqdTrkDocs(fldDocID , fldFirstRlsOnly ,
fldOrderLine ) Select fldDocID, fldFirstRlsOnly, " & me.txtOrderline & "
FRom tblPartRqdTrkDocs Where fldPart=" & SomePartID, dbfailonerror

you can also use query designer selecting what you need to copy, then
changing query type to append and set where to insert, very easy


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Daryl S]

Jack -

I seem to be missing the connection between the Order and Part. While you
specify the OrderLine in the form control, shouldnt' we restrict the parts to
those that are on the related order? (Otherwise you will get multiple
records for each partID, as I am sure there would be multiple orders with
this part ID) I don't know if you are only running this on new records, but
if not, you probably want to include criteria to make sure we don't add
duplicate records. For this, we need to know what (besides the autonum)
should make values unique (maybe fldOrderLine and fldDocID?)

That said, you can use SQL statements for batch inserts or updates very
efficiently, and can pull values from forms as well as tables or other
queries in the process. Here is a rough sample of what your query would look
like, though you would need to use the correct form names, and the
"SomePartID" needs to be defined.

Back up your database and see if you can adjust this to your needs. If you
get stuck, post the SQL you have come up with and let us know what is/isn't
working, and we can help more.

INSERT INTO tblOrdRqdTrkDocs (fldOrderLine, fldDocID, fldFirstRIsOnly)
SELECT forms!frmName!ctrlName, tblPartRqdTrkDocs.fldDocID,
tblPartRqdTrkDocs.fldFirstRIsOnly
FROM tblPartRqdTrkDocs
WHERE fldPart = SomePartID
AND (some connection between parts and orders)
AND Not Exists (SELECT "x" from tblOrdRqdTrkDocs WHERE fldDocID =
tblPartRqdTrkDocs.fldDocID AND fldOrderLine = forms!frmName!ctrlName)

 

[Jack Leach]

Thanks to both, this is what I was looking for. I thought it was somewhere
along these lines.


Daryl - the handling of duplicates and the entry points are taken care of.
Each Order Line will have one part... after the Part is updated, the system
checks for the existance of ReqdDocs and prompts a delete/replace, or, if
it's a new record it will automatically insert the relevant doc checks. And
yes, the OrderLine and DocID are the unique identifier. So each OrderLine
(part on an order) will have at most one of each type of required document
associated with it.

Thanks for the breakdown on the query... this is a big help.
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)