2 Update Queries /variable?

[Guest]

HI there, is there a way to capture the primary key in one table to a variable and pass it to an update statement to create it in another table

What I have - On PO Form have cmd button which runs an click-event with 2 SQL DoCmd.RunSQL statements. One Creates a Receiptheader based on info from POHeader. ReceiptNumber is autonumber (I can change this if there's a better way i.e. increment a number field or something). The next statement Inserts ReceiptLines based on the Purchase order POLines. What I need to do is capture the ReceiptNumber created from the first INSERT and use it to insert into the ReceiptLines table. Can this be done

If more info is needed please let me know.

Thanks in advance for any help!

 

[Steve Schapel]

Joan,

It seems to me that this would be easier, as you suggested, using your
own incremental numbering, rather than an autonumber. You could assign
DMax("ReceiptNumber","Receipts")+1
to a variable in your code, and then you can use this value within both
of your SQL statements for the new ReceiptNumber.

 

[Guest]

Thank you for your response. I'm fairly new to vb and am getting by trying to use sql statements - as it is, I'm not sure how to implement this variable in my code?

The code below creates my records - my problem is how to get the receiptno variable defined (where do I do this to update the table with it) and into the SQLReceiptLines qry to update that table.

(More info. There can be more then one receipt for one PO. - so I am trying to get the code to update receipt lines based on the new receipt no - if I go by PO Number then it creates duplicate records because of the previous receipt with that PO)

Dim SQLReceipt As String
Dim SQLReceiptLine As String


SQLReceipt = "INSERT INTO tblReceipts ( SupplierID, PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber, [tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= '" & Me!PONumber & "') AND (([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = " INSERT INTO tblReceiptsLine ( ItemNo, PONumber, ItemDesc, ReceiptNo )" & _
"SELECT tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"FROM tblReceipts, tblPOLine INNER JOIN tblItemMaster ON tblPOLine.Description = tblItemMaster.ItemNo " & _
"GROUP BY tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"HAVING (((tblPOLine.PONumber)='" & Me!PONumber & "')) "

DoCmd.RunSQL SQLReceiptLine

(The above SQLReceiptLine doesn't work properly)

thx again in advance for help - I've been struggling with this for days!

 

[Steve Schapel]

Joan,

I have a few comments... I hope I am not going too far beyond the
original question

Is PONumber really a text field?

I assume that any given PO can only be associated with one Supplier?
OK, you shouldn't have a SupplierID field in the tblReceipts table. If
you know the PONumber, then you automatically know the SupplierID, and
as such, storing this information in two places is invalid.

I can't see why you are using a Totals Query. Doesn't really make sense
to me to have a GROUP BY clause and a HAVING clause in an SQL statement
which doesn't include any Aggregate functions.

I assume you are only creating one receipt at a time?... looks like it
from the SQL. So...

Dim SQLReceipt As String
Dim NextReceipt As Long
NextReceipt = DMax("[ReceiptNo]","tblReceipts) + 1

SQLReceipt = "INSERT INTO tblReceipts ( PONumber, ReceiptNo ) " & _
"SELECT tblPOHeader.PONumber " & NextReceipt _
"FROM tblPOHeader " & _
"WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0))"

I have similar comments about your tblReceiptsLine, but I am not really
sure about exactly what this data involves. But you almost certainly
shouldn't have, for example, the ItemDesc field in this table, for
similar reasons as I mentioned before. Also, is the join in the query
between tblPOLine.Description and tblItemMaster.ItemNo correct? Anyway,
what I would need to know is on what basis is which items from the PO
allocated to which Receipt? Unless I am missing something here, this
will be necessary. On that score, though, I am wondering about the
purpose of the tblReceiptsLine table. On the basis of what you have
told us so far, it seems to me that it is totally redundant and making
for unnecessary complication... maybe all you need is a ReceiptNo field
added to the tblPOLine table, and then your second existing query should
be scrapped in favour of an Update Query to allocate the new ReceiptNo
to those selected POLines items?

--
Steve Schapel, Microsoft Access MVP

Thank you for your response. I'm fairly new to vb and am getting by trying to use sql statements - as it is, I'm not sure how to implement this variable in my code?

The code below creates my records - my problem is how to get the receiptno variable defined (where do I do this to update the table with it) and into the SQLReceiptLines qry to update that table.

(More info. There can be more then one receipt for one PO. - so I am trying to get the code to update receipt lines based on the new receipt no - if I go by PO Number then it creates duplicate records because of the previous receipt with that PO)

Dim SQLReceipt As String
Dim SQLReceiptLine As String


SQLReceipt = "INSERT INTO tblReceipts ( SupplierID, PONumber ) " & _
"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber, [tblPOHeader]![Post] " & _
"HAVING (((tblPOHeader.PONumber)= '" & Me!PONumber & "') AND (([tblPOHeader]![Post])<>True))"

DoCmd.RunSQL SQLReceipt


SQLReceiptLine = " INSERT INTO tblReceiptsLine ( ItemNo, PONumber, ItemDesc, ReceiptNo )" & _
"SELECT tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"FROM tblReceipts, tblPOLine INNER JOIN tblItemMaster ON tblPOLine.Description = tblItemMaster.ItemNo " & _
"GROUP BY tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"HAVING (((tblPOLine.PONumber)='" & Me!PONumber & "')) "

DoCmd.RunSQL SQLReceiptLine

(The above SQLReceiptLine doesn't work properly)

thx again in advance for help - I've been struggling with this for days!

 

[Guest]

Any suggestions to resolve my issue is appreciated, including the tables def'n. My answers follow yours below.
THANK YOU!

----- Steve Schapel wrote: -----

Joan,

I have a few comments... I hope I am not going too far beyond the
original question

Is PONumber really a text field?

Originally it was a number field but I changed it to text when i couldn't get the Where clause to work because data type mismatch - ("WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0))") I was going to worry about that after I got receipt lines figured out.

I assume that any given PO can only be associated with one Supplier?
OK, you shouldn't have a SupplierID field in the tblReceipts table. If
you know the PONumber, then you automatically know the SupplierID, and
as such, storing this information in two places is invalid.

OK, I can take that out, I was just adding it there so on the receipt header I could put the supplier information on the form.

I can't see why you are using a Totals Query. Doesn't really make sense
to me to have a GROUP BY clause and a HAVING clause in an SQL statement
which doesn't include any Aggregate functions.

It seemed that I needed to do this so I didn't get a duplicate receipt created if it was the second receipt created against the PO. I'm sure the code below will eliminate the issue (I changed these statements so many times now....


I assume you are only creating one receipt at a time?... looks like it
from the SQL. So...
Yes.

Dim SQLReceipt As String
Dim NextReceipt As Long
NextReceipt = DMax("[ReceiptNo]","tblReceipts) + 1

SQLReceipt = "INSERT INTO tblReceipts ( PONumber, ReceiptNo ) " & _
"SELECT tblPOHeader.PONumber " & NextReceipt _
"FROM tblPOHeader " & _
"WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0))"

THANK YOU!!

I have similar comments about your tblReceiptsLine, but I am not really
sure about exactly what this data involves. But you almost certainly
shouldn't have, for example, the ItemDesc field in this table, for
similar reasons as I mentioned before. Also, is the join in the query
between tblPOLine.Description and tblItemMaster.ItemNo correct?

It took me a long time to figure it out, but in tblPOline uses a Lookup wizard to find the description for a combo box on the POLine form (the user knows the description not the item code). But it seems what is actually stored in the field is the item number even though the user sees the description. I don't know why this is, I just went with it once I found the join worked ok. Seems weird to me but....again I'm new so I assumed this was just how it worked.


Anyway, what I would need to know is on what basis is which items from the PO
allocated to which Receipt? Unless I am missing something here, this
will be necessary.

I would like the receiptlines to be updated with the POLines from the current PO, but I need the receipt number from above added to these lines to make the link for the form. (Once I get that part to work I will add a condition i.e. where OnOrderqty > 0 so if a second receipt is created from the PO only lines that haven't been fully received will be transferred to ReceiptLines - see below why I think I need ReceiptLines)

On that score, though, I am wondering about the
purpose of the tblReceiptsLine table. On the basis of what you have
told us so far, it seems to me that it is totally redundant and making
for unnecessary complication... maybe all you need is a ReceiptNo field
added to the tblPOLine table, and then your second existing query should
be scrapped in favour of an Update Query to allocate the new ReceiptNo
to those selected POLines items?

I use receipt lines to update QtyReceived, Date Received, HeatNo and LotNumber fields - which will inturn update the inventory transactions table - so they will know what is on hand, on order etc. If there is a better way.....PLEASE, the suggestions are muchly appreciated. Note, there might be times on the receipt line where the user will have to add an additional record with same item but different heatno and qtyreceived with that heatno.


--
Steve Schapel, Microsoft Access MVP

Thank you for your response. I'm fairly new to vb and am getting by trying to use sql statements - as it is, I'm not sure how to implement this variable in my code?

The code below creates my records - my problem is how to get the receiptno variable defined (where do I do this to update the table with it) and into the SQLReceiptLines qry to update that table.
(More info. There can be more then one receipt for one PO. - so I am trying to get the code to update receipt lines based on the new receipt no - if I go by PO Number then it creates duplicate records because of the previous receipt with that PO)
Dim SQLReceipt As String Dim SQLReceiptLine As String

"SELECT tblPOHeader.SupplierID, tblPOHeader.PONumber " & _
"FROM tblPOHeader " & _
"GROUP BY tblPOHeader.SupplierID, tblPOHeader.PONumber, [tblPOHeader]![Post] " & _

DoCmd.RunSQL SQLReceipt

SQLReceiptLine = " INSERT INTO tblReceiptsLine ( ItemNo, PONumber, ItemDesc, ReceiptNo )" & _

"SELECT tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"FROM tblReceipts, tblPOLine INNER JOIN tblItemMaster ON tblPOLine.Description = tblItemMaster.ItemNo " & _
"GROUP BY tblPOLine.Description, tblPOLine.PONumber, tblItemMaster.Description, tblReceipts.ReceiptNo " & _
"HAVING (((tblPOLine.PONumber)='" & Me!PONumber & "')) "

DoCmd.RunSQL SQLReceiptLine
(The above SQLReceiptLine doesn't work properly)
thx again in advance for help - I've been struggling with this for days!

 

[Guest]

Regarding the code you posted, I cannot get it to work! aaahhhhh! I'm getting a syntax error (missing operator) on
tblPOHeader.PONumber 46 From POHeader Where PONumber = '10' - Runtime error 3075.

46 would be the next receipt number and ponumber 10 is the po I'm on when I click the cmd button.

The only thing I changed was to add a & at the end of the line with
SELECT tblPOHeader.PONumber " & NextReceipt _ (changed to & NextReceipt & _, because it wouldn't compile without)

I have tried everything. If I remove everything to do with ReceiptNo, then of course it works fine. I tried adding brackets and + signs and such to see what operator it might look for - but can't figure out.

Any ideas? sorry to be a pain on this one, but I can't get it!

 

[Ken Snell]

I think it's a small typo in Steve's posted code. Try this:

SQLReceipt = "INSERT INTO tblReceipts ( PONumber, ReceiptNo ) " & _
"SELECT tblPOHeader.PONumber, " & NextReceipt & _
" FROM tblPOHeader " & _
"WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0))"

--
Ken Snell
<MS ACCESS MVP>

Regarding the code you posted, I cannot get it to work! aaahhhhh! I'm

getting a syntax error (missing operator) on

tblPOHeader.PONumber 46 From POHeader Where PONumber = '10' - Runtime error 3075.

46 would be the next receipt number and ponumber 10 is the po I'm on when I click the cmd button.

The only thing I changed was to add a & at the end of the line with
SELECT tblPOHeader.PONumber " & NextReceipt _ (changed to & NextReceipt

& _, because it wouldn't compile without)

I have tried everything. If I remove everything to do with ReceiptNo,

then of course it works fine. I tried adding brackets and + signs and such
to see what operator it might look for - but can't figure out.

 

[Steve Schapel]

Thanks, Ken. Darn, looks like foiled by a comma!

 

[Steve Schapel]

Very sorry, Joan. As Ken has pointed out, there should be a comma after
PONumber and also the missing & i.e.
SELECT tblPOHeader.PONumber, " & NextReceipt _

In too much of a hurry.

 

[Ken Snell]

You're not the first, and not the last~ ! < g >

BTDT......

 

[Steve Schapel]

Joan,

Some more comments inline...

Joan wrote:

....

Originally it was a number field but I changed it to text when i
couldn't get the Where clause to work because data type mismatch -
("WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0))") I was
going to worry about that after I got receipt lines figured out.

If it was a number field, the WHERE clause would omit the ''s, i.e.
"WHERE ((PONumber = " & Me!PONumber & ") AND (Post = 0))"

....
OK, I can take that out, I was just adding it there so on the receipt
header I could put the supplier information on the form.

Putting supplier info on the form is fine. This is done via basing the
form on a properly constructed query, not via the duplication of stored
data.

....
It seemed that I needed to do this so I didn't get a duplicate
receipt created if it was the second receipt created against the PO.
I'm sure the code below will eliminate the issue (I changed these
statements so many times now....

At times, where you have a query based on tables in a one-to-many
relationship with each other, you need to use a SELECT DISTINCT clause
to prevent duplicated records being returned

THANK YOU!!

.... and thank Ken.

It took me a long time to figure it out, but in tblPOline uses a
Lookup wizard to find the description for a combo box on the POLine

....

see http://www.mvps.org/access/lookupfields.htm

I would like the receiptlines to be updated with the POLines from the
current PO, but I need the receipt number from above added to these
lines to make the link for the form. (Once I get that part to work I
will add a condition i.e. where OnOrderqty > 0 so if a second receipt
is created from the PO only lines that haven't been fully received
will be transferred to ReceiptLines - see below why I think I need
ReceiptLines)

I use receipt lines to update QtyReceived, Date Received, HeatNo and
LotNumber fields - which will inturn update the inventory
transactions table - so they will know what is on hand, on order etc.
If there is a better way.....PLEASE, the suggestions are muchly
appreciated. Note, there might be times on the receipt line where
the user will have to add an additional record with same item but
different heatno and qtyreceived with that heatno.

OK, I agree, since it is possible for any given order line to be split
over more than one Receipt, you need another table. It looks to me that
it needs these fields:
ReceiptLineID (autonumber)
POLineID (do you have some such in the tblPOLine table?)
ReceiptNo
QtyReceived
Date Received
HeatNo
LotNumber

Do you mean that you want to build the skeleton of these records into
your table first, and then after that you data enter things like HeatNo
(whatever that means?) and LotNumber? I.e. this data is not meant to be
included in the query that puts the new ReceiptLines records in?

I am also bothered by the OnOrderQty > 0 idea. Where is this meant to
come from? Do you mean that you will enter a value into this field for
all items from POLines that should be included in the Receipt which you
are currently generating?

In any case, in the scenario you have now described, you now do not need
the tblReceipts table. It would serve no useful function. So the code
starts to look something like this...

Dim SQLReceipt As String
Dim NextReceipt As Long
NextReceipt = DMax("[ReceiptNo]","tblReceiptLines") + 1

SQLReceipt = "INSERT INTO tblReceiptLines ( POLineID, ReceiptNo ) " & _
"SELECT POLineID, " & NextReceipt & _
" FROM tblPOLine " & _
"WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0) AND
(OnOrderqty > 0))"

As for the 'Inventory Transactions' table... we'll leave that for
another day, but for now let's say that the existing tables that we have
been discussing probably means that a separate table is only necessary
for the recording of the outwards movement of goods?

Best wishes.

 

[Steve Schapel]

Thanks for the comforting words, Ken
Of course, on closer inspection I see there were actually 3 typos in
that short block of code! I think I should wait until I am properly
awake in the mornings before I start answering questions <g>

 

[Ken Snell]

I know that feeling, but it's night here and I can't use the excuse of
"morning sleepiness"..... but perhaps "night sleepiness"!?

 

[Guest]

Thank you so much for the update, appreciate it. Already tried it and it works wonderfully! Steve, I answered your other questions in a previous post

Joan

 

[Guest]

I'm not sure I understand you right? I don't need the receipts header tbl? Anyway, thank you very much for all of your comments I appreciate the help, I am now able to at least move forward! To briefly answer your question regarding receipt lines.....

----- Steve Schapel wrote: -----

Joan,

Some more comments inline...

Joan wrote:

....

THANK YOU!!

.... and thank Ken.

Yes definitley!!

It took me a long time to figure it out, but in tblPOline uses a
Lookup wizard to find the description for a combo box on the POLine

....

see http://www.mvps.org/access/lookupfields.htm
I'll look today. Thank you for the reference.

I would like the receiptlines to be updated with the POLines from the
current PO, but I need the receipt number from above added to these
lines to make the link for the form. (Once I get that part to work I
will add a condition i.e. where OnOrderqty > 0 so if a second receipt
is created from the PO only lines that haven't been fully received
will be transferred to ReceiptLines - see below why I think I need
ReceiptLines)

I use receipt lines to update QtyReceived, Date Received, HeatNo and
LotNumber fields - which will inturn update the inventory
transactions table - so they will know what is on hand, on order etc.
If there is a better way.....PLEASE, the suggestions are muchly
appreciated. Note, there might be times on the receipt line where
the user will have to add an additional record with same item but
different heatno and qtyreceived with that heatno.

OK, I agree, since it is possible for any given order line to be split
over more than one Receipt, you need another table. It looks to me that
it needs these fields:
ReceiptLineID (autonumber)
POLineID (do you have some such in the tblPOLine table?) - No but I can add this. obvious a link between the two tbls.
ReceiptNo
QtyReceived
Date Received
HeatNo
LotNumber

Do you mean that you want to build the skeleton of these records into
your table first, and then after that you data enter things like HeatNo
(whatever that means?) and LotNumber? I.e. this data is not meant to be
included in the query that puts the new ReceiptLines records in?

Yes, I'd like the skeleton, with the recno, item desc, and onorderqty, then the user will enter qty rec'd, heatno, etc...
I'd like to use a calculated field onorderqty - haven't really thought where to put this - which is Orderqty-QtyReceived on the receiptlines so the user knows what is left onorder (for reference as the received amt is of course however many they are recieving). Is this a bad way of thinking to do this?

I am also bothered by the OnOrderQty > 0 idea. Where is this meant to
come from? Do you mean that you will enter a value into this field for
all items from POLines that should be included in the Receipt which you
are currently generating?

OnOrderQty is a calculated field based on OrderedQty-QtyRecieved =I'm not sure how to handle this yet (as I'm trying to go one step at a time - which might bit me in the butt.....)?

In any case, in the scenario you have now described, you now do not need
the tblReceipts table. It would serve no useful function. So the code
starts to look something like this...
I wouldn't need it for the form to group the records together for viewing? The user also will run a report from this for tracking parts received.

Dim SQLReceipt As String
Dim NextReceipt As Long
NextReceipt = DMax("[ReceiptNo]","tblReceiptLines") + 1

SQLReceipt = "INSERT INTO tblReceiptLines ( POLineID, ReceiptNo ) " & _
"SELECT POLineID, " & NextReceipt & _
" FROM tblPOLine " & _
"WHERE ((PONumber = '" & Me!PONumber & "') AND (Post = 0) AND
(OnOrderqty > 0))"

As for the 'Inventory Transactions' table... we'll leave that for
another day, but for now let's say that the existing tables that we have
been discussing probably means that a separate table is only necessary
for the recording of the outwards movement of goods?

Yes, I the transactions table is meant to store movement i.e. Receipts, Adjustments so when a Sale is made I will allocate product accordingly. - Big dreams...this was obviously not the scope when I took on the project, it's just kind of grown.

Best wishes.

Thank you, very much for all your help, seriously needed and appreciated! Joan.

 

[Steve Schapel]

Joan,

Yes, I agree, it is not an entirely simple project.

Regarding your butt getting bitten, this sometimes happens when you move
too quickly to the next step before you have clarified the basic
design questions. The basic design questions involve having a table
structure that will allow all required data to be stored such that it
reflects the actual real-world relationships between the elements of
that data. The more you can leave user operational functions out of
your thinking at this stage, the better. "I wouldn't need it for the
form to group the records together for viewing? The user also will run
a report from this for tracking parts received."... these are not good
reasons for the existence of a table. If you have the tables as
discussed, tblPOHeader, tblPOLine, tblReceiptLine, then these tables
will contain all of the information you need. Draw out a diagram of
these tables, listing the fields and the relationships between them...
I'm sure you'll see what I mean. Forget forms and reports for now...
you should only think of them *after* you get the tables sorted. (As it
happens, though, the forms and reports you need will easily be done with
this table structure.) The table design is dictated by the actual data
elements you are working with. Thus, my idea of the plan changed after
you informed me that a PO Line item could be split over more than one
Receipt... a change to the actual data relationships (as far as I was
aware), therefore a change to the table structure. No, you do not need
a tblReceipts. Add it temporarily to your table diagram, and note that
it contains nothing that is not already there in the other three tables.

You are still also going to need to think through again how you will
designate which POLine items will be included in the Receipt. I don't
understand your "business rules" as yet, based on the way things
*really* happen as regards when goods are included, so I can't really be
specific. Maybe you need a Yes/No checkbox field for Received that you
can manually indicate each item for inclusion? But I don't think the
OnOrderQty thingie will work for this. That would mean that for the
first Receipt for a given PO, *all* POLine items will be tagged for
inclusion, since for all of them OrderedQty-QtyRecieved will be >0, and
then for the next Receipt for that PO, *all* of the POLine items where
OrderedQty-QtyRecieved>0 will be incuded, whether or not there were any
actually received. This is not what you want, is it? You want to
include those POLine items where at least some of them were actually
received.

As for your proposed Transactions table, as I tried to indicate before,
this idea is flawed as you have described it. The ReceiptLines table as
we have been discussing already contains the information about Receipts.
That's it. You've already got a storage spot for this info. You
don't need to put it anywhere else as well, and you should not do so.

It's not 100% related to your project, but you might also get some value
from having a look at Allen Browne's article about inventory tracking,
see http://members.iinet.net.au/~allenbrowne/AppInventory.html

 

[Guest]

I am very grateful for your input. I am definitly going to go back and look at the table structures from just the information point of view and forget the forms/reports for now. I should have done that from the beginning, I just was thinking along the lines of headers and lines tables - but there would be alot of redundant information there between the po, receipt and inventory.

Regarding the Receipts, as I see it - right now, (the user currently has a system in place - I am not trying to duplicate it but in some respects I think it would probably be fine to work similar) the user would create a po. with a number of items that they've ordered. Pressing a cmd button would create the Receipt against this po. in a perfect world all items on po would be recieved, but in real that's not how it is - so more then one receipt could be created against a PO. (the first receipt will transfer all items from polines) If however some of the items have already been fully received, then I wouldn't want to transfer that line info to the new receipt. Only items that still have an outstanding qty should transfer over to the new receipt - thats where my idea for calculated field onorderqty came in. based on poline qtyordered and receiptline qtyrecieved. But maybe I should have a flag on polines to set true when a line has fully been received as well so this line doesn't transfer based on the flag.

When the poline reaches the receiptline, there is a possibility that the receiptline might need to be 'split' into two/more lines to recieve against a specific heat number (it's like a serial number for pipe, i.e an order for 55 ft of pipe can be received like this:30ft can come in with heatnumber 456-0987I and another 25ft with heat number 456-77890O). Just thinking aloud here, so I need to record that 55 ft total was received in order to set my flag (mentioned above) so if another receipt is necessary for other items on this po, the poline described above wouldn't be transferred.

Working with an inventory system in the past(as a user), that is how po's/receipts worked with it and it seemed to make sense.

In any case, I have some database design work to look at here before I move along much further I think. Thank you so much again, for your input, suggestions and comments.

Joan