Recieving Inventory Query

  • Thread starter Thread starter Ernst Guckel
  • Start date Start date
E

Ernst Guckel

Hello,

I'm pretty sure we are approaching this correctly but not sure how to do
this: We have a purchase order database where we enter all our inventory
purchases. The problem is that each line item in a purchase order is a
generic inventory item. I want to take these generic items and "Recieve into
Inventory" so that they become owned assets. A simple insert into query
should do the trick but there is one problem that I cannot work out...

If the purchase order says we bought 20 laptops then the insert query needs
to insert 20 new assets into the tblAssets.

EX:

PO 1001
1 Monitors
3 Keyboards
22 Laptops

The query needs to create 26 new records... Any help would be great...

Thanks,
Ernst.
 
You will need to do one line item at a time. Use a Cartesian effect append
query.
Creacte a table named CountNumber with number field name CountNUM containing
0 (zero) through your maximum.
Create the append query, add the CountNumber table, drag the CountNUM field
to the FIELD row, add criteria --
Between 1 AND [Forms]![YourFormName]![UnitsReceived]

Include the other necessary information for the individual item. Some
information will need to be added later such as bar code identification and
serial numbers.
 
This is wonderful. I understand that this will process only one line at a
time but is there a way to get it to do all lines? or do i need to look
through each line and do the same?

Ernst.

KARL DEWEY said:
You will need to do one line item at a time. Use a Cartesian effect append
query.
Creacte a table named CountNumber with number field name CountNUM containing
0 (zero) through your maximum.
Create the append query, add the CountNumber table, drag the CountNUM field
to the FIELD row, add criteria --
Between 1 AND [Forms]![YourFormName]![UnitsReceived]

Include the other necessary information for the individual item. Some
information will need to be added later such as bar code identification and
serial numbers.

--
Build a little, test a little.


Ernst Guckel said:
Hello,

I'm pretty sure we are approaching this correctly but not sure how to do
this: We have a purchase order database where we enter all our inventory
purchases. The problem is that each line item in a purchase order is a
generic inventory item. I want to take these generic items and "Recieve into
Inventory" so that they become owned assets. A simple insert into query
should do the trick but there is one problem that I cannot work out...

If the purchase order says we bought 20 laptops then the insert query needs
to insert 20 new assets into the tblAssets.

EX:

PO 1001
1 Monitors
3 Keyboards
22 Laptops

The query needs to create 26 new records... Any help would be great...

Thanks,
Ernst.
Click to expand...
Click to expand...
 
You will not know how many lines when you build the form.

An alternate method would be to use a table to load line items that would be
deleted following the run. These would be entered in the subform datasheet
view.

--
Build a little, test a little.


Ernst Guckel said:
This is wonderful. I understand that this will process only one line at a
time but is there a way to get it to do all lines? or do i need to look
through each line and do the same?

Ernst.

KARL DEWEY said:
You will need to do one line item at a time. Use a Cartesian effect append
query.
Creacte a table named CountNumber with number field name CountNUM containing
0 (zero) through your maximum.
Create the append query, add the CountNumber table, drag the CountNUM field
to the FIELD row, add criteria --
Between 1 AND [Forms]![YourFormName]![UnitsReceived]

Include the other necessary information for the individual item. Some
information will need to be added later such as bar code identification and
serial numbers.

--
Build a little, test a little.


Ernst Guckel said:
Hello,

I'm pretty sure we are approaching this correctly but not sure how to do
this: We have a purchase order database where we enter all our inventory
purchases. The problem is that each line item in a purchase order is a
generic inventory item. I want to take these generic items and "Recieve into
Inventory" so that they become owned assets. A simple insert into query
should do the trick but there is one problem that I cannot work out...

If the purchase order says we bought 20 laptops then the insert query needs
to insert 20 new assets into the tblAssets.

EX:

PO 1001
1 Monitors
3 Keyboards
22 Laptops

The query needs to create 26 new records... Any help would be great...

Thanks,
Ernst.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top